0

I have 2 tables: person and company.
I want to create a table address knowing that:

  • a person can have zero, one or several address
  • a company can have zero, one or several address
  • the basic rows for address are the same for person and company

Solution 1

  • Create a table address (no foreign key) with all address rows (street, city...)
  • Create a person_address with foreign keys address_id and person_id
  • Create a company_address with foreign keys address_id and company_id

=> A person and company can share the same address (good) and no duplication (good)
=> A bit cumbersome: I frist have to create adress then person/company_address

Solution 2

  • Create a table person_adress with foreign key person_id and all address rows
  • Create a table company_adress with foreign key company_id and all address rows again

=> Rows for address info are defined twice

Solution 3?

Is there a way to create an unique table adress which could refer to either a person OR a company?

nico7et8
  • 217
  • 1
  • 8
  • Yes, first thing that pops to mind, you can have your person and company tables as they are, then have a third table with a PK that consists of entity_id, a column entity_type (PERSON/COMPANY/ETC), and a col for the address. You can enforce the relationship programmatically with a combo of id/type. Other options as well. – Jacob Barnes Oct 23 '17 at 23:36
  • Thank you. But in this case, can the entity_id be a foreign key? If it refers to a PERSON and PERSON_ID changes, is there a way to cascade the change to ADRESS? – nico7et8 Oct 23 '17 at 23:40
  • This guys goes into other options. #4 is the best. https://stackoverflow.com/questions/7844460/foreign-key-to-multiple-tables – Jacob Barnes Oct 23 '17 at 23:43
  • I would be tempted to force all people to belong to (at least) one organisation - even if that organisation was rather artificial, e.g. 'Bob's house' – Strawberry Oct 23 '17 at 23:44
  • @JacobBarnes Thanks, I'll look into that! – nico7et8 Oct 23 '17 at 23:47
  • @Strawberry Yes, tempted too, at first. OR consider that organisations have at least one employee who works at an address. But I want to be able to list all companies and not have Bob's House in them. – nico7et8 Oct 23 '17 at 23:49
  • @JacobBarnes Looks exactly like my issue. Thanks for the link. – nico7et8 Oct 23 '17 at 23:52
  • You could have a flag that identified real and fictitious organisations – Strawberry Oct 23 '17 at 23:56
  • Yes, that would work. But the idea of a parent table works better for me. – nico7et8 Oct 24 '17 at 00:01

1 Answers1

0

Thanks to Jacob Barnes for the link:
Foreign Key to multiple tables

Create a "parent table" entity which can either be a person or a company. adress then refers to entity.

nico7et8
  • 217
  • 1
  • 8