Adding records with referential integrity
in [Tables]
|
From: atledreier on 4 May 2010 03:25 Ok, I've made an empty database and experimented a bit with relationships and integrity, and i think i just had my Eureka! moment. :-) I see why it worked before, and that is an acceptable way to make it work again, to me. I made a simple database, two tables with referential integrity enforced. Made a simple query, tag.* and tag_diverse.* and started entering data. as soon as I started entering data in any of the tag_diverse fields a record was created in that table, with the correct tagnumber. So all I need to do is make sure the users enter all the relevant data (in all tables) before saving the record. This may be very basic stuff for you guys, and laugh if you want, but I think I get it now. :-) Thank you guys! On 4 Mai, 08:46, atledreier <atledre...(a)gmail.com> wrote: > 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] |