Mapping multiple parent tables: what is the correct design approach?

Question

What is the EF design pattern for this scenario?

 

Domain Model:

class Person
{

public Address Address {get;set;}
}

 

class Invoice
{

public Address Address {get;set;}
}

class Address
{

public string City {get;set;}
}

 

A Person class is not related to an Invoice class in any way, but they can both have an Address.

 

Store Model:

I can see 3 possible ways to implement this:

1.       There are 2 different tables PersonAddresses and InvoiceAdresses. Persons table is related to PersonAddresses, while Invoices table – to InvoiceAdresses.

 

Which brings up a question about mapping: can an Address entity be mapped to 2 different tables (PersonAddresses and InvoiceAddresses)?

Persons                                     PersonAddresses 
* PersonId -------------------- * Id

Invoices                                   InvoiceAddresses
* InvoiceId -------------------- * Id 

 

2.       There is single table Addresses (the preferred way in my situation), which requires an AddressType field (say, 0=Person, 1=Invoice), to identify its parent, in addition to ParentId field. But, in this scenario there are complications with a composite foreign key: ParentId + AddressType. And, from my experience, (I haven’t used the latest version of SQL Server) such constraint is not very effective, since Addresses doesn’t really know its parent table, since it depends on a type.

Persons   
* PersonId ------
                            |         
                            |       Addresses
                            |          * Id
                            |------ * ParentId
Invoices             |          * Type
* InvoiceId ----|

How can this be mapped to the domain model?

 

3.       From the DB schema modeling perspective, in my opinion this is the better way to represent the relationships. There is an additional table, called Entities, for instance, which in the essence is a primary key dispenser for Persons and Invoices. Entities has Id and Type fields.

 

Id – foreign key to Invoices and Persons

Type – identifies which is the parent.

 

Addresses table has a Type field (foreign key to Entities).

Any time an Address or an Invoice is added, it is Entities that is used to generate their respective primary keys, thus maintaining the relationship integrity using the DB engine.

Persons   
* PersonId ------
                            |         
                            |      Entities                          Addresses
                            |------ * Id -------------------* Id
                            |          * Type   
Invoices             |          
* InvoiceId ----| 

Can this be mapped? How?

 

Once again, thanks for any suggestions. I am fairly new to both EF and databases in general, so please let me know if the mentioned approaches are inherently flawed.

---

Regards, VRSki  

Answer 1

Hi VRSki,

 

Welcome to MSDN forums!

 

Based on my research, the design of your project depends on your requirements.

 

1)    If you want to have “many to many” association between Person and Address, Invoices and Address, then the first way is recommended.

Persons                        Invoices

 

PersonAddresses              InvoiceAddresses

(FK to Persons)                  (FK to Invoices)

(FK to Addresses)                (FK to Addresses)

 

                   Addresses

 

When you want to get the Addresses related to certain person, you can just use PersonInstance.Addresses.

 

 

 

2)    If you want to have “one to many” association between Person and Address, Invoices and Address, then the second way is recommended.

Persons                        Invoices

 (FK to Addresses)                (FK to Addresses)

 

                   Addresses

 

When you want to get the Addresses related to certain person, you can also use PersonInstance.Addresses. If the type field has no other usage, you can delete it.

 

 

There is an EF sample in All-In-One Code Framework. You can refer to the “one to many” and “many to many” sample,

http://cfx.codeplex.com/SourceControl/changeset/view/34279

(You can get the CSEFEntityDataModel project under Change Set 34279.  After going into Change Set 34279 you can choose to download. We’ll add EF projects in our next release on the home page.)

 

 

If you have any questions or concerns, please update the thread and we will have a further discussion.

 

 

Best Regards

Yichun Feng

MSDN Subscriber Support in Forum 

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

Yichun,

Thank you for your reply.

What if in the second scenario I need to enforce a unique constraint on the Person-Address or Invoice-Address relationship? Meaning, a given address can only belong to either a specific Person or a specific Invoice? To simplify this, let's say it's a one-to-one relationship. A person (or an invoice) can have one and only one address.

Can such constraint be expressed in the DB schema with your suggested approach? The way I saw it to accomplish it was to have the address contain a ParentId FK and a Type identifying the parent type (Person or Invoice). This way the UNIQUE constraint can be applied to FK+Type. Is there a way to do something similar with your suggested approach?

Thanks again for your help. 

 

 

 

 

 

In the first scenario

---

Regards, VRSki

Answer 3

A Table-Per-Type strategy is probably your best bet, and results in quite a normalized database:

1. Add a new "InvoiceAddress" table, give it the same key field as address, and add an "InvoiceId" field that is an FK to Invoice. The key field should not be identity/otherwise autogenerated, and should have an FK to the key column in address.
2. Add a new "CustomerAddress" table that has an FK to the Customer type, and also, as in the other table, a PK-to-PK FK to the Address table.
3. Add these tables to your model. Make the resulting entities derive from "Address".
4. Now you have a model that makes rational sense: CustomerAddress can only be referenced by Customer. InvoiceAddress can only be referenced by Invoice. Both are instances of the Address type. And the CustomerAddress type has a reference to an object of type Customer, and the InvoiceAddress has a reference to an object of type Address - giving you a very usable API surface.

This approach is also quite extensible in that as new objects come online that need addresses, you can add additional tables to your schema for them.

HTH,
 Noam

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 4

Noam,

Thanks for the post. So, if I understand correctly, EF doesn't allow for a type to be mapped to multiple tables, and that is the reason for having a CustomerAddress and InvoiceAddress types, both derived from Address, even though in the Domain it may be unnessary?

If so, is this EF limitation (or a design decision) -- single table per type -- is carried over to EF 4?

---

Regards, VRSki

Answer 5

Hi VRSki,

 

 

In EF, if you only have FK constraints in the table, add the same address to Person and Address is valid based on the rules.

 

If you want to enforce the 1:1 association as you posted, Noam’s suggestion is a possible way to achieve it.

There is also another way for this issue. You can refer to this blog:

http://blogs.msdn.com/dsimmons/archive/2009/02/18/d3-modeling-part-1-real-world-relationships.aspx

 

 

To feedback this issue to EF4 in a formal way, you can go to our Connect portal site and submit it?

 

    https://connect.microsoft.com/VisualStudio/

 

Choose VS2010 family and submit a suggestion. Our developers will take this into serious account when designing the future releases of the products.

 

If you have any questions or concerns, please update the thread and we will have a further discussion.

 

 

Best Regards

Yichun Feng

MSDN Subscriber Support in Forum 

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.