Referential integrity

[Guest]

Hope you can help.

How do I use referential integrity with a table when it is joined to two
different tables. For example, table Photo is joined on the many side to both
the sample and isolate tables. Thus if i add data to sample and then photo i
get an error message saying that it breaks the integrity rules because
isolate is not filled out. Makes sence but how do i get around it??

When I try to add two different Photo tables to the relaitionship window,
Access re-sets the joins to just one of the two Photo tables.

Really lost here and will appreciate any help!!!!

 

[Guest]

An Isolate can have many photos. The relations should be one-to-many for
Isolate to photo. I see no need for two photo tables.

Are your Samples from the Isolate? If so, the you need a one-to-many for
Isolate to Sample.

 

[Guest]

Thanks for the try but I think I did not explain myself correctly.

I am using the table Photo to collect info and photos. thus I have it joined
to several tables, such as a laboratory sample, a bacterial isolate,
microscopy, etc. where i store photo data about these different things. for
example, a sample comes in and i take a photo, we culture it and i take a
photo of the culture, we isolate a bacterium from the culture and I take a
photo of the bacterium, etc. thus there is a chain of one-to-many and at each
table along the chain a photo is taken. This works well however, not with
referential integrity. In this case if I put anything into the photo table
joined to any of the tables, it expects a record for every table that photo
is joined with in the entire database!
It is like A-B-C-D where each letter is a table with a one to many join and
i want to store a photo for each Leter and in some cases each letter goes
vertical i.e. B-B1-B2-etc with photos on them.

Any ideas!

 

[Guest]

I was mistaken on your project thinking it was for Cultural specimens like
stone cherts, fossils, etc.

In a relational database you start with a table that is the root of the data
collection. In your case it seems to me it is the "laboratory sample." The
sample is given an ID.
Everything else would relate back to the sample either directly or
indirectly.
A sample may have many cultures and each culture may have many photos.
Your photo will not be related to many samples or many cultures - only one.

Your will have one-to-many from sample to culture. You will have
one-to-many from culture to bacterium. You will have one-to-many from
bacterium to photo.

The sample table will have a key field that is the ID.
The culture table will have the same field as a foreign key and a culture
ID. The combination of sampleID and cultureID wil be the key field for the
culture table.
The bacterium table will have sampleID, cultureID, and bacteriumID - these
together for the key field for the bacterium table.

To this point it is just referential integrity 101. But you want photos all
along the chain.

Your photo table must include all the other ID's plus another ID. A key
field will not have nulls. So in the form that you use the record photos of a
sample that is not part of a culture will need to make the field default to a
dash ( - ) for culture and bacterium. The same is true for photos of
cultures that are not isolated bacterium.

I hope I have made myself understood.

 

[Guest]

Karl,
Yes, you may have somthing here. I actually had things set up like this but
changed it because without referential integrity it was not necessary. I will
give it a try and see if it works.

thanks!

 

[Guest]

Karl,
What I did was creat different forign keys for photo. Gave it a try and it
does not work. This must be a very common problem. Again (in simple terms),
i have two tables sample and culture. I want to join the table photo to each
of them on the many side. If i set up a one to many relaitionship in the
relaitionship window and inact referential integrity, then each time i but
data in the photo table from sample, Access tells me that culture must also
have data. Aside from creating 20 different photo tables, there must be a way
to join the same photo table to multiple tables and enact referential
integrity (works great without RI!!).

The other thing that I assume is diagnostic of my mistake. If in the
relaitionship window I creat a second photo table, access automatically
lables it photo_1. I join one table to it and the other table to the original
photo table. If I close the relaitionship window and then reopen it, access
removes the joins to photo_1 and joins both tables on the one side to the
same photo table.

any ideas??

If your sugestion was not to create different forigen keys to join each
table, can you further explain any thoughts you have on how to fix this.

thanks

 

[Guest]

I would suggest you just "build a little" - "test a little" each time.
Sample - Culture relation - test.
Culture - Photo - test.
Sample - Photo with CulrureID as "-" - test.
etc. - test.