SQL Databases, Entity Data Models and 1 to 1 entities

Question

I am quite new to EDMs, having written quite a lot of ADO.Net stuff in the past. I have three tables:

**Product:**
Prod_ID - PK

**Product_MaxLoan**
Prod_ID - PK

**Product_MinLoan**
Prod_ID - PK

These tables, hosted in MS SQL 2005, have no FKs or constraints configured as yet, they are to have a notional 1 to 1 relationship. For example, every row of a Product with ID of 1, there will be a row in Product_MaxLoan and Product_MinLoan each with an ID of 1.

In Visual Studio 2010, I want to set the EDM up correctly so that the cardinality is set to 1 to 1. I previously had FK constraints on the tables and the following set up, however, this would only allow a 0..1 cardinality (to cater, I suppose, for the fact a Product may not have a Product_MaxLoan or Product_MinLoan).

**Product:**
Prod_ID - PK

**Product_MaxLoan**
ID - PK
Prod_ID - FK

**Product_MinLoan**
ID - PK
Prod_ID - FK

Questions:

• What advice would you give for setting these tables up in SQL 2005? For a 1 to 1 relationship in an EDM would you set up FKs?
• Can you set up a PK relationship in SQL 2005 that the EDM will read when importing from a database?
• A product contains some 300 properties, so containing all of this data in a single table would be poor database normalization (hence the many 1 - 1 tables). Would best practice be to put all of these properties into a single EDM class? My gut reaction is to break it down much as it is structured in the DB (this is my ADO heritage coming to the fore), having a class for each logical part of the product.

Your advice would be appreciated.

Best regards,

Mark

Answer 1

In database use this configuration:

**Product:**
Prod_ID - PK

**Product_MaxLoan**
Prod_ID - PK, FK (to Product)

**Product_MinLoan**
Prod_ID - PK, FK (to Product)

This will force one-to-one relation on database level as well as in EF. The relation itself will be 1 - 0..1 (Product can exists without MaxLoan and MinLoan) because real 1 : 1 cannot exist in database. Real 1 : 1 demands that both entities always exists = you cannot insert the first if the second doesn't exist and you cannot insert the second if the first doesn't exists. How do you insert them without turning off referential integrity?