Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

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).

share|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
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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