CODEWITHSUNDEEP
sqldatabase-designentity-framework-5relationship
Tom
Tom

Reputation: 179

SQL Table Relationship (Entity Framework Code First)

I was just having a play around with Entity Framework Code First and came to a stand still on the best way to relate a group of tables. I may be looking at this in completely the wrong way, in which case it would great if someone could point me in the right direction.

Basically I have 3 tables, 2 main tables and 1 which holds common fields for both tables.

Now, where I'm stuck is on what type of relationship I should setup between the 2 main tables and the 3rd table. The two main tables will always have 1-1 relationships with the 3rd table, however the 3rd table will either be related to table 1 OR table 2 not both.

Am I possibly not looking at this in the right way? and should I just get rid of the third table and just have the common fields replicated in both tables?

Here is what the structure of my tables look like at the momment:

Table 1:
-- Id
-- RatePricingId
-- Name
-- CreatedOn
-- ModifiedOn

Table 2:
-- Id
-- RatePricingId
-- Name
-- StartOn
-- EndOn
-- CreatedOn
-- ModifiedOn

Table 3 (Common Fields)
-- Id
-- Monday
-- Tuesday
-- Wednesday
-- Thursday
-- Friday
-- Saturday
-- Sunday
-- MinNights
-- CheckInOutDays
-- RateType
-- CreatedOn
-- ModifiedOn

Cheers,

Tom

EDIT:

I originally had it setup so table 1 & 2 had a foreign key to table 3, however the issue came when using this in entity framework, this is because the table 3 POCO object would now have an ICollection of both table 1 and table 2 when it would only really be related to one of the table objects.

Upvotes: 1

Views: 480

Answers (2)

Tom
Tom

Reputation: 179

I've just come across a blog post by Morteza Manavi which I think has answered my question:

http://weblogs.asp.net/manavi/archive/2011/04/14/associations-in-ef-4-1-code-first-part-3-shared-primary-key-associations.aspx

Cheers,

Tom

Upvotes: 0

Pleun
Pleun

Reputation: 8920

Well, your question has nothing to do with entity framework, asp.net etc but more with database design. You should retag

The only difference seems to be

  • StartOn
  • EndOn

So without any more background, I would probably use only one table.... Perhaps not way you expected but it seems more simple too me.

In case you want to keep the three tables, create a foreign key from table1 to table3 and also one from table2 to table3 and you will be fine.

Upvotes: 0

Related Questions