HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Two relationships to same table

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
tworelationshipstablesame

Problem

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:

Solution

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

Context

StackExchange Database Administrators Q#6519, answer score: 6

Revisions (0)

No revisions yet.