Creating a one-to-one relationship

Question

I have two objects, a Press and a Stitching:

Press
------------------
PressID
Name

Stitching
------------------
StitchingID
Name
Cost

So on my site, when a user creates a press, they have the option to create a Stitching for that press. A press doesn't have to have a stitching.

I'm using Entity Framework 4, and if I get a Press object from the database, I'd like to be able to say:

Press p = getPressFromDB(pressId);
if (p.Stitching != null)
{
    float cost = p.Stitching.Cost;
}

And I'd also like to be able to do:

Stitching s = getStitchingFromDB(stitchingId);
Press p = s.Press;

How can I create a one-to-one relationship between these two tables?

EDIT:

I followed the advice of a user on another forum and ran this script to create a relationship between the two and prevent a Stitching from associating with multiple Presses:

CREATE TABLE PressStitching (PressID int NOT NULL PRIMARY KEY, StitchingID int NOT NULL,
CONSTRAINT FK_PressStitching_Press FOREIGN KEY(PressID) REFERENCES Press(PressID),
CONSTRAINT FK_PressStitching_Stitching FOREIGN KEY(StitchingID) REFERENCES Stitching(StitchingID),
CONSTRAINT UNQ_Stitching UNIQUE(StitchingID))

But when I update my .edmx file, I still get a one-to-many relationship. A Press has one Stitching, but a Stitching has multiple Press entities. I updated the relationship in the .edmx designer so that an Press had Zero-or-One of Stitching, and a Stitching had One of Equipment. But I don't know if that's enough. Any advice here?

Not exactly a duplicate but [this post](http://stackoverflow.com/questions/1761362/entity-framework-one-to-one-mapping-issues) should give you a good idea. — Bala R, Apr 20 '11 at 00:56
@Bala - yeah, I saw that, and the top answer said "For one-to-one relationships, EF expects that the tables are using the same primary key." But I don't know how to do that. One primary key for two tables? — Steven, Apr 20 '11 at 01:00
Does this help?: http://stackoverflow.com/questions/2523528/one-to-one-relationship-using-jpa — ypercubeᵀᴹ, Apr 20 '11 at 06:10
"when a user creates a press, they have the option to create a Stitching for that press" -- that would be, then, a 1:0..1 ("one to zero or one") relationship, rather than a true 1:1 relationship. — onedaywhen, Apr 20 '11 at 08:55
@onedaywhen - you're right, my mistake. Do you know how to do that? :) — Steven, Apr 20 '11 at 19:47

score 0 · Answer 1 · answered Apr 20 '11 at 01:05

0

If the relationship between Press and Stitching is one-to-one, and there no reason to have Stitching in a separate table(i.e. Stitching is only associated to Press and no other class) then it would be better to move the Stitching attributes into Press

PressID
Name 
StitchingName
StitchingCost

This makes your model design much simpler and your database correctly normalised.

In .net I would make the StitchingCost and nullable type. ie. float? StitchingCost;

This would then allow you to check if a Stitching details had been added.

Example:

Press p = getPressFromDB(pressId);
if (p.StitchingCost.HasValue)
{
    float cost = p.StitchingCost.Value
}

answered Apr 20 '11 at 01:05

SyntaxGoonoo

900
8
10

1

Well in reality, there's about 30 other tables I need to create relationships with: Binding, Folding, etc. I just used one as an example. So I want one-to-one relationships so I can avoid having my Press table with 200 fields, and more than half of then NULL – Steven Apr 20 '11 at 01:14
Actually, Steven's tables are normalized in a higher level than your suggestion. – ypercubeᵀᴹ Apr 20 '11 at 06:02

score 0 · Answer 2 · answered Apr 20 '11 at 06:19

0

I think that StitchingID should be a Primary Key and also a Foreign Key to PressID

answered Apr 20 '11 at 06:19

ypercubeᵀᴹ

113,259
19
174
235

score 0 · Answer 3 · answered May 13 '11 at 19:28

I don't see why you want a StichingID at all unless Stichings can be associated with things other than presses. If it is what you are saying and there can be one or zero stichings for each press then the stiching table should act as an addon table to Presses. Instead of using StichingID have the primary key in the Stiching table be PressID. Thus the 1:0-1 relationship is enforced directly. I believe Entity Framework should have no trouble recognizing this.

Creating a one-to-one relationship

3 Answers3