i have one transaction table, each transaction has 5-6 contacts involved. each contact has the same fields like name, address, cell, email etc.

So the best thing I guess is to use the same contact table and relate it 5 times to the transaction table.. So I don't need to make the same kind of table 5 times.

The problem starts when I connect with the entity-framework this only takes one relation.

My question is should I make 6 copies of contact for each contact type or I would be better with 6 join tables (A table with just the 2 id's so I could do a join)?

I think its a very common problem but I couldn't find clear information what the best way is.

EDIT: Sample of Tables


The best thing is to normalize the tables. Create a contact_type table with a record for each contact type. Then create a contact_type_xref junction table that contains the identifier from the contact table and the identifier from the contact_type table. Then load the contact types associated to each contact into the contact_type_xref, and remove the duplicate contact records from contact. Here's an example:Schema Example. Once you fix the database design, you can tackle the entity-framework problem (which will probably disappear).

| improve this answer | |
  • 1
    I'm creating the database now... so there's no data to merge/move. I try to do it right the 1'st time so I shouldn't have to ask how to fix it later... – Ezi Oct 5 '11 at 2:02
  • So you say I should use 1 contact table and then make 6 jump tables with just the ids? – Ezi Oct 5 '11 at 2:03
  • @Ezi, why six jump tables? One contact_type table, one contact table, one contact_type_xref table to relate the contacts to the types. One record per contact in the contact table, 6 records per contact in the contact_type_xref table, one record per contact in the contact type table. It probably wouldn't hurt to read up on database normalization. – Wil Oct 5 '11 at 2:06
  • To what table would I join the Transaction table? – Ezi Oct 5 '11 at 2:20
  • It depends on the rules. If a transaction can only have one customer, stick the customer id in the transaction table. If transactions can have multiple customers, create a customer_transaction table with the customer id and the transaction id. The customer type should not be a part of the junction between transactions and customers...it shouldn't matter to the transaction at all if the customer has only one type, or six. – Wil Oct 5 '11 at 2:25

First off, it's a bit of a rubbish framework that doesn't acknowledge multiple relationships between entity types!

If you have this many foreign keys, chances are you'll have more (or fewer) in the future. The solution @Wil details will allow you achieve this without schema changes.

One work-around which may fool your framework would be to define views in the database for each of your contact types and define framework relationship from transaction to the view. For example

create view Manufacture as
    ContactID as ManufactureID,
from Contacts

May or may not work, depending on how your framework interacts with the database.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.