1

We have entities like Customer, Broker, Company. These have different attributes and must be on different tables. But they can have common things, in our case Contact Info (entity->contact is one to many relationship). Whats the best way to do that. If there isnt a perfect design, what is best if the most important thing is to write the least code possible.

case 1: all entities have a 'common' auto-inc entityId ( they share the primary key , so you cant have a Customer with id=1 and a Company with id=1) enter image description here

case 2: 'contact' stores information about what entity it refers to. (Customers, Companies, Brokers dont share primary key)

enter image description here

case 3: 3 contact tables, one for each of our entities.

There are more ways to connect those tables, and its a problem frequently occuring, i dont particularly like any solution, so help me decide.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
patrox
  • 408
  • 3
  • 11
  • 2
    Check out **super-type/subtype** http://stackoverflow.com/search?tab=votes&q=user%3a196713%20subtype and **Party** http://stackoverflow.com/questions/716549/what-are-the-principles-behind-and-benefits-of-the-party-model – Damir Sudarevic Nov 23 '12 at 12:28
  • Also check put class-table-inheritance. There is some overlap with party. They are both worth looking at – Walter Mitty Nov 24 '12 at 02:25

0 Answers0