| Author |
Topic |
|
malachi151
Posting Yak Master
152 Posts |
 Posted - 2012-04-13 : 15:04:56
|
| I've run into this before and never really landed on a good way to handle it.I have a child table in which there are records have parents from one of two different tables. What's the best way to represent this?In other words, [CODE]A -< CB -< C[/CODE]So these are my options I would guess:1) I can include a column that indicates which table the record is related to, then a single key column that holds the key value.2) Put in two different key columns, one for each table. The value for that column will be NULL when the record doesn't join to that table.3) Only put a parent table indicator column in the child table, and create a third table that stores the keys along with the parent table identifier and then to join to the parent I'd join on key and parent table indicator through the 3rd table.To me option 3 sounds the best.Thoughts?--------------------------------------------------My blog: http://practicaltsql.net/Twitter: @RGPSoftware |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-13 : 15:10:09
|
| one more option is put a single column then using a trigger check for referential integrity to either of the parent tablesfor 2 you also need to put a check constraint in place to ensure either one of them having a valid value at any time. That was how we implemented it once------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
malachi151
Posting Yak Master
152 Posts |
Posted - 2012-04-13 : 15:24:41
|
I think I'll go with option 3, in which case I'll create the following type of table to join everything together:CREATE TABLE join_Child_Parents( parent_type char(1) NOT NULL, id_child bigint NOT NULL, id_parent bigint NOT NULL, PRIMARY KEY (parent_data, id_child, id_parent)) Then I can use a compound foreign key and won't have to use a trigger.--------------------------------------------------My blog: http://practicaltsql.net/Twitter: @RGPSoftware |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-13 : 15:42:36
|
| compound foreign key you will put on which all columns?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
malachi151
Posting Yak Master
152 Posts |
Posted - 2012-04-13 : 15:56:01
|
quote:
Originally posted by visakh16
compound foreign key you will put on which all columns?
Well from the child table its easy. FK on parent_type and id_child, both of which will exist in both the child and the joining table.As for the FK between the parent table and the joining table... hmm... Can you create a filtered foreign key? :p (Answer: no)Guess I'm stuck back in the same spot...Bah, who needs foreign keys?! ;)--------------------------------------------------My blog: http://practicaltsql.net/Twitter: @RGPSoftware |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-04-13 : 15:57:00
|
| Sounds like you are trying to implement a supertype-subtype relationship. ALthough, hard to tell for sure from the description. It looks like you are going down that path, you might want to so a search for supertype-subtype and see if that helps. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-13 : 16:07:37
|
quote:
Originally posted by malachi151
quote:
Originally posted by visakh16
compound foreign key you will put on which all columns?
Well from the child table its easy. FK on parent_type and id_child, both of which will exist in both the child and the joining table.As for the FK between the parent table and the joining table... hmm... Can you create a filtered foreign key? :p (Answer: no)Guess I'm stuck back in the same spot...Bah, who needs foreign keys?! ;)--------------------------------------------------My blog: http://practicaltsql.net/Twitter: @RGPSoftware
thats why i thought either use option2 with check constraint ot option1 with trigger would be better wayIn your case I cant see how you'll still ensure referential integrity to correct parent tables------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
malachi151
Posting Yak Master
152 Posts |
Posted - 2012-04-13 : 16:11:51
|
quote:
Originally posted by Lamprey
Sounds like you are trying to implement a supertype-subtype relationship. ALthough, hard to tell for sure from the description. It looks like you are going down that path, you might want to so a search for supertype-subtype and see if that helps.
No, its not a hierarchy, it's just that the records in the child table may be children of either table A or table B (not both, but one or the other).May help to give more context.This is a Location table, and the information in the location table may have come from either an Inspection or an insurance Policy.In other words, this is a list of homes with address and other info, and that info may have come either from a home inspection or a home owners insurance policy, hence the need to relate the records to either a "Policy" table an "Inspection" table...--------------------------------------------------My blog: http://practicaltsql.net/Twitter: @RGPSoftware |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2012-04-15 : 23:10:47
|
| Personally I'd go #2. Nullable FK constraint on each with a check constraint to ensure only one is ever set. It's not perfect but a good compromise. Even splitting it out into a "location source" kind of concept, you have to address the problems eventually and you'd still end up with problems trying to enforce 1-1 rather than 0-1. SQL Server does not make this easy (possible?) with its lack of deferred constraints.One comment I'd also like to make is that you cannot effectively enforce RI using triggers while maintaining high concurrency. Don't even try. You can get close, you can think you've got it but there is always a situation when it will fail. Remember - you cannot see other people's writes and they cannot see yours. Even if you can live with a solution with known holes (in which case why bother with RI?), the trigger solution offers the very real possibility of gumming up the whole works with locks and contention. The whole list of issues also changes if you move to snapshot isolation as well (which you should always use IMO). It's not worth it and you cannot do it effectively. Leave it to the DB. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-16 : 11:42:07
|
quote:
Originally posted by LoztInSpace
Personally I'd go #2. Nullable FK constraint on each with a check constraint to ensure only one is ever set. It's not perfect but a good compromise. Even splitting it out into a "location source" kind of concept, you have to address the problems eventually and you'd still end up with problems trying to enforce 1-1 rather than 0-1. SQL Server does not make this easy (possible?) with its lack of deferred constraints.One comment I'd also like to make is that you cannot effectively enforce RI using triggers while maintaining high concurrency. Don't even try. You can get close, you can think you've got it but there is always a situation when it will fail. Remember - you cannot see other people's writes and they cannot see yours. Even if you can live with a solution with known holes (in which case why bother with RI?), the trigger solution offers the very real possibility of gumming up the whole works with locks and contention. The whole list of issues also changes if you move to snapshot isolation as well (which you should always use IMO). It's not worth it and you cannot do it effectively. Leave it to the DB.
I agree to this.Thats how we've also implemented it on all similar occasions. I'm also not comfortable in handling this at trigger end owing to reasons you mentioned and also difficults in maintainability aspect------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-04-16 : 12:04:20
|
| An alternate perspective:You're saying that Locations can be the result/child of either an Inspection or Insurance policy. Does that mean Inspection/Insurance both require a Location? If that's the case, I don't see them being the "parents" as they are dependent on the Location. Therefore, the foreign key really should go in the other direction, with Location referenced by Inspection and Insurance tables.If they don't require a Location, Location could be a nullable column in those tables. The process that inserts into either table would be modified to insert to Location first, then Insurance/Inspection, to maintain the foreign key.If Insurance and Inspection truly are the "parents" of the location data, can you replace the Location table with a view that UNIONs the relevant columns from both tables? |
|
|
|
|
Foreign key to two different tables..