Prev: fone color in table
Next: Please help! Designing database
From: atledreier on 30 Apr 2010 09:07
Due to limitations regarding my client I can't change the structure of
the tables.

One tag_misc for every Tag is exactly what I want, really, but I guess
I can enforce this on form level and just tell my users this is what
you get.
But after reading this, I have no idea how this has worked
before! :-)

I guess I'll shuffle over to the Forms guys and see if they can help
me out with something.

Thanks for your help!

- Atle



On 30 apr, 13:35, "BruceM via AccessMonster.com" <u54429(a)uwe> wrote:
> If there could be more than one record for Tag_Misc, Tag_Failure_Mode, and
> Tag_Format, those tables need to one-to-many.  If they are one-to-one there
> can be only one Tag_Misc record for each Tag.  In the Relationships window,
> one-to-many will appear (in Access 2003 and earlier, anyhow) with a 0 on one
> end of the relationship line, and an infinity symbol on the other.
>
> The problem I see is that it seems you are attempting to link the primary
> keys of your tables.  Using Tag_Misc as an example, you should have:
>
>  tblTag:
>   * TagID
>     Description
>     Tag_Cat (category)
>     Function_code
>
> tblTag_misc
>   *Tag_miscID
>    TagID (linking field to tblTag)
>    manufacturer
>    misc fields....
>
> It may be possible under some specific circumstances to link the PK of one
> table to the PK of another for a one-to-one.  If so, it would work only if
> the PK field of the linked table is not autonumber.  I'm not sure if it will
> work even in that case, as on the rare occasions I have used one-to-one I
> have designed the table as if for one-to-many, but with a unique index on the
> linking field.
>
> You can set the Required property of the linking field to Yes, but that only
> means (unless I am missing something) that if there is a record it must have
> a value in that field.  If you want to require Tag_misc records for each Tag
> record I think you will need to enforce that at the form level, or at least
> in some way other than requiring a value in that field.
>
> Form/subform is the best way to set this up.  It would be possible to append
> values in other ways, but for day-to-day data entry it is unlikely it would
> make much sense to take that approach.  A form based on a query including
> several table may not be updatable.  For more:http://allenbrowne.com/ser-61.html
>
> On another note, I would not use Tag as a table or field name, as it is a
> property of forms, reports, and controls.  If you use it you would have to
> enclose it in square brackets, or you could get some unexpected results.  I
> have suggested tblTag as the table name, and TagID as the field name, but you
> can choose what you like.  For more on Reserved words:http://www.accessmvp.com/JConrad/accessjunkie/resources.html#Reserved...
> I have found Allen Browne's Problem names and reserved words in Access to be
> especially helpful.
>
>
>
>
>
> atledreier wrote:
> >Thank you guys. I'll give you more details.
>
> >I am in the design phase of a project, and my client wants me to
> >import all my data when all is as-built. My client has a database for
> >all his needed info, so I just used that database structure as a basis
> >for my design. I also added some new tables for design specific data,
> >and some other stuff that won't go to my client.
>
> >There is the master tag table. This holds the main list of records.
> >Then supporting tables to give more information about different kinds
> >of tags.
>
> >First, the tables: I've not listed all the fields, but the most
> >relevant ones.
>
> >Tag:
> >*Tag
> >Description
> >Tag_Cat (category)
> >Function_code
> >...
>
> >Tag_misc:
> >*tag
> >manufacturer
> >misc fields....
>
> >Tag_Failure_Mode:
> >*tag
> >Fail_code
> >Fail_mode
> >....
>
> >Tag_Format_in/_br_/te
> >*tag
> >cal_low
> >cal_high
> >IP-grade
> >....
>
> >The tag_misc table is 1 to 1. All tags should have information in the
> >tag_misc table. I know I then should have all that information in one
> >table, but since the original client structure needs to be maintained
> >I chose that design. And this has worked for a while, so I thought
> >it'd still work.
>
> >The tag_failure_mode table should contain data for most tags, but not
> >all. I chose to have a record in there for all tags regardless, as i
> >thought that may be easier to maintain. This is based on the tag!
> >function_code field.
>
> >The tag_format table should have data for tags of certain tag!tag_cat
> >values (br, in and te type tags). Once again, most of my tags are in
> >this category, so I chose to have all tags in this table too.
>
> >So I have used a query to gather up all the relevant fields for my
> >users (they are conservative and like their big excel-like datasheets)
> >in one big list.
> >My client made a change in his underlying database recently, so I
> >thought I'd take the opportunity to re-work my database as well.
> >trying to get rid of the big queries and relying more on forms, and
> >also finally linking the database (we've all been working on the same
> >file up until now).
> >So after these changes I get the message that a relevant record needs
> >to exist in the CHILD database, the Tag_failure_mode table in my
> >instance.
> >If it is like you say then I find it strange that this has worked
> >before, really. I see how it would work like you say, but then how did
> >it work this long? It was after I linked the database and added the
> >Tag_failure_mode table it stopped working. I also made many other
> >changes, so I can't tell what I did to break it.
>
> >Any tips on how to restructure or set stuff up to make this as smooth
> >as possible?
>
> >- Atle
>
> >On 29 apr, 18:43, John W. Vinson <jvinson(a)STOP_SPAM.WysardOfInfo.com>
> >wrote:
>
> >> >I have a few tables in my database, all 1 to 1 with the master table.
>
> >[quoted text clipped - 23 lines]
>
> >>              John W. Vinson [MVP]
>
> --
> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/20...

From: BruceM via AccessMonster.com on 30 Apr 2010 10:06
Don't try to link the PK fields! If you have just one Tag_Misc for every
client, is there a reason it cannot appear in the main client record?

The "forms guys" cannot overcome an unworkable design. It's hard to know how
it worked before, but you are **severely** limited by the design if you have
no freedom to change the table structure. Good luck.

atledreier wrote:
>Due to limitations regarding my client I can't change the structure of
>the tables.
>
>One tag_misc for every Tag is exactly what I want, really, but I guess
>I can enforce this on form level and just tell my users this is what
>you get.
>But after reading this, I have no idea how this has worked
>before! :-)
>
>I guess I'll shuffle over to the Forms guys and see if they can help
>me out with something.
>
>Thanks for your help!
>
>- Atle
>
>> If there could be more than one record for Tag_Misc, Tag_Failure_Mode, and
>> Tag_Format, those tables need to one-to-many.  If they are one-to-one there
>[quoted text clipped - 131 lines]
>> --
>> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/20...

--
Message posted via http://www.accessmonster.com

From: atledreier on 3 May 2010 04:12
Thank you for the good luck! :-)

I'm on a little thin ice here.
My client's system import tables one by one, so no links are provided
or taken into account. I need to make sure of the integrity of the
data in the tables, hence I use referential integity. So the actual
links is pretty much up to me. So how can I make sure the data makes
sense and keep the data integrity while keeping the table structure? I
should think the structure as is should work, since the tables are
structured the way they are. Basic logic don't change from system to
system.

So, here's a link to a screenshot of the main tables in the database.
If anyone could suggest how to best structure this database while
keeping the table structure I'd be thrilled.

http://picasaweb.google.no/111389954736894900969/DiverseBilder#5466951736013107746

Short explanation to the different tables and what I want:

Tag. This is the main table with all the tag numbers and some related
information like category and function code
Tag_Diverse: This is misc information about tags. all tags should have
at least some information in this table, hence the 1:1 relationship
Cable: All tags with [Tag_cat]="C" should have a record in this table.
Tag_format: All tags with [tag_cat]="BR", "IN" or "TE" should have a
record in this table.
The Doc_ref thing works fine.

Hope someone can make sense of this, I really haven't fully wrapped my
head around these basic issues yet.

- Atle


On 30 apr, 16:06, "BruceM via AccessMonster.com" <u54429(a)uwe> wrote:
> Don't try to link the PK fields!  If you have just one Tag_Misc for every
> client, is there a reason it cannot appear in the main client record?
>
> The "forms guys" cannot overcome an unworkable design.  It's hard to know how
> it worked before, but you are **severely** limited by the design if you have
> no freedom to change the table structure.  Good luck.
>
>
>
>
>
> atledreier wrote:
> >Due to limitations regarding my client I can't change the structure of
> >the tables.
>
> >One tag_misc for every Tag is exactly what I want, really, but I guess
> >I can enforce this on form level and just tell my users this is what
> >you get.
> >But after reading this, I have no idea how this has worked
> >before!  :-)
>
> >I guess I'll shuffle over to the Forms guys and see if they can help
> >me out with something.
>
> >Thanks for your help!
>
> >- Atle
>
> >> If there could be more than one record for Tag_Misc, Tag_Failure_Mode, and
> >> Tag_Format, those tables need to one-to-many.  If they are one-to-one there
> >[quoted text clipped - 131 lines]
> >> --
> >> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/20...
>
> --
> Message posted viahttp://www.accessmonster.com

From: John W. Vinson on 3 May 2010 12:31
On Mon, 3 May 2010 01:12:56 -0700 (PDT), atledreier <atledreier(a)gmail.com>
wrote:

>Short explanation to the different tables and what I want:
>
>Tag. This is the main table with all the tag numbers and some related
>information like category and function code
>Tag_Diverse: This is misc information about tags. all tags should have
>at least some information in this table, hence the 1:1 relationship
>Cable: All tags with [Tag_cat]="C" should have a record in this table.
>Tag_format: All tags with [tag_cat]="BR", "IN" or "TE" should have a
>record in this table.
>The Doc_ref thing works fine.

I think you may be misunderstanding how one to one relationships work.

A relationship will PREVENT adding a record to Tag_Diverse unless its linking
field exists in Tag. The relationship will not *create* a record in
Tag_Diverse. The relationship is one way; the Tag table is still the "master"
table, and strictly speaking the relationship should be called a "One to (zero
or one)" relationship. It's a chicken or egg problem; before a Tag record has
been created and saved to disk, you *cannot* have a Tag_Diverse record because
referential integrity would prevent its existance. There will always be a
moment when you have a "chicken which has not yet laid an egg"!

Another issue is the 1 to 1 relationship. Is it in fact the case that the
Tag_Diverse table will contain one, and only one, NEVER ANY MORE, records of
"misc information"? If so, why not just add the fields in Tag_Diverse into Tag
and enforce that at least some of them are non-null?

Similar questions about the Cable and Tag_Format tables, which may be more
legitimate one to one "subclassing" tables.

--

John W. Vinson [MVP]
From: atledreier on 4 May 2010 02:46
The Tag_diverse table is a table of data that is design specific, and
not meant for the client database at all. Like I stated before, the
structure of most of the tables is untouchable.

Any thoughts on how I can solve the other tables?


On 3 Mai, 18:31, John W. Vinson <jvinson(a)STOP_SPAM.WysardOfInfo.com>
wrote:
> On Mon, 3 May 2010 01:12:56 -0700 (PDT), atledreier <atledre...(a)gmail.com>
> wrote:
>
> >Short explanation to the different tables and what I want:
>
> >Tag. This is the main table with all the tag numbers and some related
> >information like category and function code
> >Tag_Diverse: This is misc information about tags. all tags should have
> >at least some information in this table, hence the 1:1 relationship
> >Cable: All tags with [Tag_cat]="C" should have a record in this table.
> >Tag_format: All tags with [tag_cat]="BR", "IN" or "TE" should have a
> >record in this table.
> >The Doc_ref thing works fine.
>
> I think you may be misunderstanding how one to one relationships work.
>
> A relationship will PREVENT adding a record to Tag_Diverse unless its linking
> field exists in Tag. The relationship will not *create* a record in
> Tag_Diverse. The relationship is one way; the Tag table is still the "master"
> table, and strictly speaking the relationship should be called a "One to (zero
> or one)" relationship. It's a chicken or egg problem; before a Tag record has
> been created and saved to disk, you *cannot* have a Tag_Diverse record because
> referential integrity would prevent its existance. There will always be a
> moment when you have  a "chicken which has not yet laid an egg"!
>
> Another issue is the 1 to 1 relationship. Is it in fact the case that the
> Tag_Diverse table will contain one, and only one, NEVER ANY MORE, records of
> "misc information"? If so, why not just add the fields in Tag_Diverse into Tag
> and enforce that at least some of them are non-null?
>
> Similar questions about the Cable and Tag_Format tables, which may be more
> legitimate one to one "subclassing" tables.
>
> --
>
>              John W. Vinson [MVP]

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: fone color in table
Next: Please help! Designing database