Database how to model 1:1 relationship

(VS2008, SqlCE 3.5)

I try to model a 1:1 relationship. So I put the foreign key in the parent table, holding the PK of the child table. Then I set the foreign key to UNIQUE. Still when I create my entity classes (With SqlMetal), the child class has a reference to an EntitySet of the parent, not just a single entity. This seems like a m:1 relation? So what I need to do to make it 1:1 ?

EDIT1:

I'm confused.. Trying to make a set, like this:

StrategySet(ID, EntryStrategyID{Unique}, 开发者_开发问答ExitStrategyID{Unique})   
EntryStrategy(ID) 
ExitStrategy(ID) 

Is this m:1 isn't it? Though it looks like FK's are in the parent, or wouldn't we name StrategySet the parent? And how would I now change this too 1:1 ?

First of all, the parent is table which is referenced by FK from child. So you can't say that your parent table references the child: it's not correct.

Secondly, 1:1 relations can be made through:

• Primary Keys in both tables
• Primary Key in parent and Unique Foreign Key in child

So in your case, the architecture is correct. I suppose you should check the structure again, and look through this article.

If all columns in EntryStrategy and ExitStrategy are the same, then all you need is simply this (add all other columns too).

If EntryStrategy and ExitStrategy have some different columns, then use this. Keep all common columns in the Strategy table. EntryStrategy and ExitStrategy have only columns specific to each one.

Here is also a generic example of 1:1 due to vertical partitioning of a table.

Before:

After:

Let me understand the situation you are describing.

You have set of fields which make up a "Strategy". A subset of the fields are conceptually the "EntryStrategy" and a non-intersecting subset of the fields are the "ExitStrategy".

In your case a given set of values making up an "EntryStrategy" can be joined with one and only one set of values making up an "ExitStrategy". This is what you mean when you say there is a 1:1 correspondence.

As smirkingman said earlier, in classic relational database modeling, all of these fields belong in a single table because no subset of the fields appear in more than one record.

If you could have multiple ExitStrategies for a single EntryStrategy then you would have two tables with the EntryStrategy being the parent and the ExitStrategies being the children and the ExitStrategy records would have a Foreign Key pointing to the EntryStrategy parent record.

If you could have multiple EntryStrategies for a single ExitStrategy then you would have two tables with the ExitStrategy being the parent and the EntryStrategies being the children and the EntryStrategy records would have a Foreign Key pointing to the ExitStrategy parent record.

If you could have multiple EntryStrategies associated with multiple ExitStrategies then you would have a many-to-many relationship which requires a third table to maintain the correspondences.

The principles of classic database modeling would put all your fields in one table.

As St Woland wrote, you can enforce the 1:1 relationship by having two tables where the foreign key in the child table is a Unique index. But two tables are normally used for 1-to-many relationships.

As Damir wrote, you can enforce the 1:1 relationship by having three tables where the third table has a foreign key to each of the other two tables and both foreign key fields are marked as Unique indices. However, normally you only use three tables in this fashion when you have a many-to-many relationship.

I think you are expecting way too much from the automated data modeling tools to expect them to construct entities that represent your very unconventional approach.

The answer to your main question is simple. How do I represent a 1:1 relationship? You put them in the same record in a single table!