1

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

Community
  • 1
ReynierPM
  • 1,764
  • 10
  • 30
  • 45
  • 1
    A pointer to http://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database that may be a helpful analysis. Slide here: http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back/32 – jynus Sep 02 '14 at 15:58
  • @jynus can you take a look to my edit and come back with some feedback around it? – ReynierPM Sep 02 '14 at 17:30
  • ReynierPM No, your edit is not on the right path. Use @user16484's suggestion or your original design. order_id has no place in the persons tables. Can an order be associated with many persons? Is a person associated with only one order? – ypercubeᵀᴹ Sep 02 '14 at 17:37
  • @ReynierPM, I do not fully understand your requirements, but if there a 1:N relationship and you want to follow the mentioned good patterns for having foreign key constraints (and not your original design), you would need an extra link table. – jynus Sep 02 '14 at 17:42
  • @ypercube no one order belongs only to one person but one person could have zero or many orders. I added a new approach, take a look – ReynierPM Sep 02 '14 at 18:09
  • @jynus I added the extra table as issued by user16484 is this one right? – ReynierPM Sep 02 '14 at 18:10
  • Your second approach looks ok, asuming that person_id are both PK and FK. (that's what both user18484 and Neil suggested, more or less). I'd prefer if you deleted this from the question and wrote it as an answer. (You can answer your own questions here!) – ypercubeᵀᴹ Sep 02 '14 at 18:33
  • My only other suggestion/rant is to use person_id, order_id as names everywhere (throw those id into the fire). – ypercubeᵀᴹ Sep 02 '14 at 18:35
  • @ypercube what you mean with use person_id and order_id as a names? you mean rename the field as person and order? – ReynierPM Sep 02 '14 at 18:38
  • I mean the column id in persons table to renamed as person_id. And the id in orders tables to be renamed order_id. It's a personal preference - although shared I think by many, not to have columns named just id. – ypercubeᵀᴹ Sep 02 '14 at 18:39

3 Answers3

2

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
JoseTeixeira
  • 1,123
  • 1
  • 8
  • 18
  • I can't do that since both holds different values see the new model, I made a typo in the first one, my bad – ReynierPM Sep 02 '14 at 17:31
1

No, that is not right.

There should not be an order_id in a person table. Generally speaking, is an order id an attribute of a person? No.

I'd start with Single Table Inheritance, as it's simpler:

create table parties (
  party_id int primary key,
  type smallint not null references party_types(party_type_id), --1=individual,2=organization
  name text not null,
  ...
);

Also, multiple parties play multiple roles in a Sales Order, but I guess you're talking about the Customer. In that case:

create table sales_orders (
  order_id int primary key,
  customer_id int not null references parties(party_id),
  ...
);

There is a customer_id attribute on the sales order, with a foreign key pointing to the parties table.

You should probably read one of the books listed here:

Ready-to-Use Database models example

Community
  • 1
Neil McGuigan
  • 8,423
  • 4
  • 39
  • 56
  • Thanks I'll read those recommended books in order to improve my knowledge and learn new things, I've the model – ReynierPM Sep 02 '14 at 18:11
0

Following Neil and user16484 suggestions also ypercube and jynus and good practices at DB modeling, I come with this model

enter image description here

This is what they talked about

ReynierPM
  • 1,764
  • 10
  • 30
  • 45