A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Adding records with referential integrity

Thread Tools Display Modes
Old May 4th, 2010, 08:25 AM posted to microsoft.public.access.tablesdbdesign
external usenet poster
Posts: 22
Default Adding records with referential integrity

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
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 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

On Mon, 3 May 2010 01:12:56 -0700 (PDT), atledreier

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]


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

All times are GMT +1. The time now is 10:56 PM.

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 OfficeFrustration.
The comments are property of their posters.