SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Foreign key to two different tables..
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

malachi151
Posting Yak Master

148 Posts

Posted - 04/13/2012 :  15:04:56  Show Profile  Visit malachi151's Homepage  Reply with Quote
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,


A -< C
B -< C


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

India
52317 Posts

Posted - 04/13/2012 :  15:10:09  Show Profile  Reply with Quote
one more option is put a single column then using a trigger check for referential integrity to either of the parent tables
for 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

malachi151
Posting Yak Master

148 Posts

Posted - 04/13/2012 :  15:24:41  Show Profile  Visit malachi151's Homepage  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/13/2012 :  15:42:36  Show Profile  Reply with Quote
compound foreign key you will put on which all columns?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

malachi151
Posting Yak Master

148 Posts

Posted - 04/13/2012 :  15:56:01  Show Profile  Visit malachi151's Homepage  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/13/2012 :  15:57:00  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/13/2012 :  16:07:37  Show Profile  Reply with Quote
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 way

In your case I cant see how you'll still ensure referential integrity to correct parent tables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

malachi151
Posting Yak Master

148 Posts

Posted - 04/13/2012 :  16:11:51  Show Profile  Visit malachi151's Homepage  Reply with Quote
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
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 04/15/2012 :  23:10:47  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/16/2012 :  11:42:07  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

robvolk
Most Valuable Yak

USA
15675 Posts

Posted - 04/16/2012 :  12:04:20  Show Profile  Visit robvolk's Homepage  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.76 seconds. Powered By: Snitz Forums 2000