maintaining referential integrity with many-to-one relation

[SunWuKung]

Time and again I come across the problem that I need to store identical type of information on multiple tables (the alternative that I am trying to avoid would be to create a child table with identical structure for each of my parent table).

What I would like to achieve is
1. To have an identifier which is unique across different tables of the current db
2. Whenever a record is deleted from any of a number of tables its related records should be deleted from the child table.

How do you usually do this?

Thanks for the help.
SWK

 

[79ssecca]

Create a child table that not only include the unique id of the parent but an identifier from what parent table it came from. them when it becomes time to delete you delete the parent record and the child record where the parent table identifier is equal to the parent table AND the unique id is equal as well.

Saying this is only a little easier than doing it however. But that is the premise.

Good Luck

 

[The_Doc_Man]

If I understand what you said, your problem is what you chose to call a child and what you chose to call a parent. Let's see if I can clear this up.

With regard to your business model, you have many types of things (represented by different types of table) but you have some single item (as represented by your child table) that the variant tables all have in common. You want an ID that is unique across all of the different tables. You want to be able to delete items from the various parent tables according to referential integrity rules of "cascading" deletes.

If the above is true, then ...

Make the child table the relational "parent" in each relationship. Let the child table hold the unique identifier as an autonumber. Define your relationships so that the many tables that differ link to the unique entry that is the child as though it were the parent. Define the relationships to include ALL records from the child and only matching records from the various parent tables.

Then if you delete a child entry with its unique identifier, only the matching parent table(s) will lose entries to a cascaded delete.

This requires you to conceptually turn your model upside down, but I think (if I understand it correctly) that it might work.

This might, of course, complicate data entry a little, since the relational integrity rules would require your logical child to exist before its logical parents. This is true because it is the only way you can create an identifier that is unique across all other tables.

 

[SunWuKung]

Thank your very much for your help.
This is how I think I will solve this - its not my brainchild I found this when searching the web. (Comments welcome).

I have ParentTable1 and ParentTable2 and the ChildTable

I will create a junction table with the following structure:
JunctionTableID - AutoNumber
ParentTable1ID
ParentTable2ID

I will join both parent tables to the junction table and maintain referential integrity.

I will than add JunctionTableID as a foreign key to the ChildTable, and maintain referential itegrity, like this:
ChildTableID
JunctionTableID

This way the JunctionTableID identifies uniquely a record in either of the parent tables and deleting a record in either of the parent table will cause the deletion of the related record in the Child table.

 

[SunWuKung]

I have a table storing Instruments and an other storing Secondary scales. These are quite different animals, so they need to sit in different tables, but they both could have multiple Norms attached to them.

So I thought I either create a Norm table with identical structure for each of them separately, or store their norms in the same table - which seemed a better solution.

Instruments and Secondary scales both have their unique IDs, but they are Autonumbers so they are unique only within their tables. To be able to attach a norm to any of them I wanted to create an ID which is unique across both tables and attach the norm to that ID.

Now I realise that you are right again - the junction table is not the solution - thanks for pointing that out.
Maybe I will just create the two Normtables separetely if you don't have a better idea.

Thanks for the help.
SWK

 

[SunWuKung]

Doc_man,
I understood now what is it that you are suggesting - what you are suggesting would certainly solve the problem. But as you say it seems a bit too complicated on the data entry side.

I am hoping that somehow I could solve this perhaps by
- appending a table specific prefix to the autonumber, creating a textual identifyer that would be unique across both tables, or
- a custom function that returns with the highest value +1 from the child table (I know - multiuser issues, but maybe somebody has a function like that).

as both of these methods seem to produce an identifier that is unique across both tables. The prefix seems the simplest but I couldn't find out how to do that - format only gives display cosmetics.

Any suggestions?
Thanks for the help.

SWK

 

[SunWuKung]

Thanks Pat for the help,
if I understand it correctly it is the same thing than what The_Doc_Man suggested.

Just a few theoretical thoughts:
Instruments and Secondary Scales are truely different, but their norms are just norms. I can see the technical difficulties implementing this, but I see no theoretical problem in the fact that very different type of things could have the same type of information stored on them.
In my case creating different norm tables would mean to duplicate all subtables, queries and (to a certain extent) interface as well. It would also hide the fact that the information in the two tables are in fact of identical types.

Many thanks again.
SWK