(MS-SQL) 2 tables, one-to-one, but nullable?

Question

Ok this is what i need to model...

I have 2 objects, that can exist seperately - lets say man and woman, neither needs to know about the other. But once they get get "married" it can only be married to each other.

In other words, I'm trying to create 2 tables, 1 table has a FK to the other, that is NULLABLE. When i try to set the relationship to 1-to-1 via the dmbl, it wont let me - which makes sense.

Ultimate i want to

1. create a women (no knowledge of a man) Woman = new Woman{ Name = "Jane", Id = 1 };
2. create a man (no knowledge of woman) Man = new Man{ Name = "John", Id = 1, WomenId = null};

But when i hook up the Man to Women...id like to be able to do something like this

Man.WomanId = 1;
SubmitChanges();
Man.Woman.Name = "Jane"

Is this even possible? If i do a one-to-many - how can i make it act this way? At the business layer? create a partial class and override stuff?

Hope this makes sense :P

Answer 1

You have to make a many-to-many link table.

Depending on your model, this can be a plain table:

(man, woman)

with unique constraints on both fields, or a more complex table

(id, man, woman, effective)

with a partial unique index on man and woman with additional clause WHERE effective = 1 (this is only possible in SQL Server 2008)

Effective = 1 means that the marriage is effective now. Any person can have the relationship history of arbitrary depth but can be married to only one other person of opposite sex at once.

The latter table can track divorces and even remarriages.

Answer 2

Can you mess with your database structure at all? It seems like this might be easier to do if you can structure it like this:

Person
------
PersonId
Name
Gender

RelationShip
------------
MalePersonId - Unique
FemalePersonId - Unique