2

I am trying to design database. I have one problem related to addresses. The will be different types which will have addresses, for example Companies, Persons. I am not sure how to design my database. I have three options in my mind.

  1. Option one: Create one Address table and create AddressRelation tables for each of above tables like below:

1) Addresses table:

  • ID
  • Country
  • City
  • District
  • Street
  • Building
  • Apartment

2) Companies table:

  • ID
  • CompanyName
  • ..........

3) Company address relation table:

  • ID
  • CompanyID (Has foreign key to Companies table)
  • AddressID
  • AddressType (Legal, Factual) Status

4) Persons

  • ID
  • Surname
  • FirstName
  • Patronymic
  • ..........

5) Persons address relation table

  • ID
  • PersonId (Has foreign key to Persons table)
  • AddressID
  • AddressType (Legal, Factual, Birth)
  • Status
  1. Option two: Create only one AddressRelationTable and add trigger instead of foreign keys:

1) Addresses, Persons, Companies table as above.

2) Address relation table:

  • ID
  • OwnerID (OwnerID can be PersonID or ComanyID)
  • AddressID
  • AddressType (Legal, Factual, Birth)
  • Status
  1. Option three: Add foreign keys from Persons and Comapnies table to Address table:

1) Address table is as above.

2) Companies table:

  • ID
    • CompanyName
    • ..........
    • LegalAddressID (Has foreign key to Addresses table)
    • FactualAddressID (Has foreign key to Addresses table)

3) Persons table:

  • ID
  • Surname
  • FirstName
  • Patronymic
  • ..........
  • BirhtAddressID (Has foreign key to Addresses table)
  • LegalAddressID (Has foreign key to Addresses table)
  • FactualAddressID (Has foreign key to Addresses table)

Third option is my last option, because it is less flexible one and I don't want to add address details directly to my main tables.

Option two is good because using this I need to create less tables, but I don't want to use trigger instead of foreign key, and OwnerID can be from two tables.

First option is straightforward I think, but this time number of tables will increase significantly.

Please consider that there are actually three tables which will have address details, same scenario must be applied for contact details and this number can increase in the feature. I tried to keep question as simple as possible so I don't provide details for third table and for contact details.

My question is which option should I use and why? May be there are other great options which I don't imagine?

closed as primarily opinion-based by Colin 't Hart, Philᵀᴹ, dezso, Max Vernon, RolandoMySQLDBA Apr 28 '15 at 15:05

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    You may also want to check Universal data models, namely Party-Role-Relationship. Short article about the topic, tdan.com/view-articles/5271 – a1ex07 Apr 28 '15 at 8:09
  • @a1ex07 Thanks for article. I read it till the end and it can be useful and aplicable to my situation – Adil Mammadov Apr 28 '15 at 11:27
1

This is a spin-off from your option two. It uses two design patterns, namely class table inheritance and shared primary key.

In addition to having two tables, one for companies and one for persons, have a third table, called contacts, that is a generalization of companies and persons, regardless of type.

Contacts.ID will be the usual kind of primary key, probably some kind of autonumber. Data that pertains to companies and persons alike can be stored in this table.

In the Companies and Persons table, don't include an ID field to use as a primary key. Instead, declare Companies.ContactID and Persons.ContactID to be primary keys in their own table, and also references to Contacts.ID. This has the nice effect that it enforces the one-to-one nature of the IS-A relationships, namely a company is a contact and a person is a contact. It also means that you can use ContactID in any other table and refer to the contacts and also to the companies table or the persons table, as the case may be.

Data that pertains to person, and only to persons, goes in the persons table.

Data that pertains to companies, and only to companies, goes in the companies table.

You can have a ContactsAddresses table that contains ContactID and AddressID so as to provide a many to many link between contacts (whether persons or companies) and addresses.

  • thanks for answer. @a1ex07 provided a link to useful website and it also offered similar solution. I will examine your answer and information at that site and I will give you feedback about result – Adil Mammadov Apr 28 '15 at 11:31

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