| Author |
Topic |
|
stubflub
Starting Member
4 Posts |
 Posted - 2010-10-06 : 12:33:14
|
| The business rule is that one person can be married to 0 or 1 other person but not himself. I have a table PERSON with identity key person_id.What would be the best way to construct relationship of person to person? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-10-06 : 12:55:23
|
| Nullable foreign key and check constraints.--Gail ShawSQL Server MVP |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2010-10-06 : 12:58:54
|
| Just be yourself and treat the other person with respectBe One with the OptimizerTG |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2010-10-06 : 13:32:50
|
quote:
Originally posted by stubflub
The business rule is that one person can be married to 0 or 1 other person but not himself.
I wish business would stay the hell out of my personal life. What goes on behind my closed doors is nobody's business but my own.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2010-10-06 : 13:37:35
|
so that's how you went blind  Be One with the OptimizerTG |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
stubflub
Starting Member
4 Posts |
Posted - 2010-10-06 : 14:48:03
|
| Thank you Gail. |
|
|
|
stubflub
Starting Member
4 Posts |
Posted - 2010-10-06 : 14:50:14
|
| How would you implement the relationship table? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-10-06 : 15:40:11
|
quote:
I wish business would stay the hell out of my personal life. What goes on behind my closed doors in my soundproofed sub-basement is nobody's business but my own.
Fixed that for you.  quote:
How would you implement the relationship table?
Flowers, dinner, maybe a movie.You can add a Spouse column to your table and have a foreign reference the primary key:ALTER TABLE Person ADD Spouse int null FOREIGN KEY REFERENCES PERSON(PersonID) You'll need to add a check constraint:ALTER TABLE Person ADD Spouse int null FOREIGN KEY REFERENCES PERSON(PersonID), CHECK (Spouse<>PersonID) The problem with this is that you can't enforce that the spouses are married to each other unless you use a trigger or user-defined function to look for constraint violations. This could be a significant performance hit. You also couldn't insert a couple as 2 new rows and specify the spouse at the same time, you'd have to follow it with an UPDATE. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2010-10-06 : 16:08:57
|
| I'm sure there is a creative way to implement this constraint using an Indexed View.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
stubflub
Starting Member
4 Posts |
Posted - 2010-10-06 : 16:17:34
|
quote:
Originally posted by robvolk
You can add a Spouse column to your table and have a foreign reference the primary key
So I'd have to insert Person1 with NULL spouse. Then Insert Person2 with spouse referencing Person1. Then update Person1 spouse to reference Person2. Is that the most efficient way I can get it done? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-10-06 : 16:35:58
|
| Using that design, yes. You can encapsulate the logic in a stored procedure that does all the operations together, and have it include consistency checks.If you were using SQL 2008 you might be able to use composable DML and/or the MERGE statement and get it all in one operation, but even if it's possible it'll be kinda ugly. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-10-06 : 16:39:38
|
quote:
What about polygamy, like in Utah? Has anyone seen the show "Sister Wives"?
I watched Big Love the first season, don't know why I stopped. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2010-10-07 : 12:54:59
|
| I'd recommend creating a many-to-many table to store relationships, and then putting a unique constraint on both foreign key columns. The "union" of the two individuals should be considered a separate data entity, which could potentially have its own distinct attributes (anniversary date, for example).Preventing someone from "marrying themselves" could be done with either a constraint or a simple indexed view of the marriage table.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-07 : 13:27:00
|
quote:
Originally posted by blindman
I'd recommend creating a many-to-many table to store relationships, and then putting a unique constraint on both foreign key columns. The "union" of the two individuals should be considered a separate data entity, which could potentially have its own distinct attributes (anniversary date, for example).Preventing someone from "marrying themselves" could be done with either a constraint or a simple indexed view of the marriage table.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________
That's what I'd do too. Also a status column, as it is possible that people divorce and remarry -- which means the unique constraint needs to be changed too... |
|
|
|
|
Marriage problem
Tara Kizer