MySQL Database Design – Best Approach for Legal/Natural Entities with Many Orders

database-designMySQLreferential-integrityrelational-theory

I'm working on this model:

DB Model

where as you may see one NaturalPerson can have many Order and the same for LegalPerson the only difference between NaturalPerson and LegalPerson are a few so in that case will be better to repeat all the Order fields in NaturalPerson and in LegalPerson or will be better to leave as I have right now? My only concern around my model is if is not this a serious inconsistency to leave an empty column at Order table (legal_person or natural_person depends on which the Order belongs to) as the Orders belongs to a Natural or a Legal but not both of them?

What will yours do in this case?

1st approach

For all the pros mentioned at this post I tough the best here is go with Class Table Inheritance so if I understood the explanation my model now is this one:

enter image description here

It's right?

2nd approach

See my own answer below …

Best Answer

This is an alternative to your solution. I suppose your id columns are just for mysql internal use, so you could have:

  • person table with id, name, and type which would be a bit or 1 character to say if it is a natural or legal person. This table will connect to order table where you will only need one column - person_id
  • On natural_person table you remove the name column and connect it to the person table
  • On legal_person table you remove the name column and connect it to the person table
Related Question