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:
Best Answer
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:. Once you fix the database design, you can tackle the entity-framework problem (which will probably disappear).