2

I have a scenario where I have two tables, person_natural and person_legal.

Case 1:

I have employees, farmers, customers, and suppliers. Should I have an enum in each table (person_natural or person_legal) indicating the type of person (for example, in person_natural I would have a column called type to say if it is an employee, farmer, or customer) or should I create a table for each type of person and link it to the main table using a FK?

I ask this because employees and customers have additional columns, for example, an employee has a salary and a customer has a credit limit. If I put these columns in the main table, the column will always be NULL for a certain type of person.

Example:

person_natural
employee -> FK person_natural
farmer -> FK person_natural
customer -> FK person_natural

or

person_natural -> type_person (employee, farmer, customer)

Case 2:

I have a table for attachments and a table for addresses. If I have the tables person_natural and person_legal, I would normally have to create a join table for each one:

person_natural
person_natural_attachment
person_natural_address

person_legal
person_legal_attachment
person_legal_address

In this case, is it correct (especially following database normalization) to create a table called person and leave the FK in person_natural and person_legal only so as not to have to create additional join tables?

person
person_natural -> FK person
person_legal -> FK person
person_attachment
person_address

In this case, if I had a person_natural, I could easily access the attachments by joining the person and then the person_attachment tables.

What are the recommended practices for these two scenarios? I am open to any suggestions or recommendations. Thank you for your time.

0

1 Answer 1

3

In this case, is it correct (especially following database normalization) to create a table called person and leave the FK in person_natural and person_legal only so as not to have to create additional join tables?

Yes, it is correct and necessary for normalization. You will have a Subtype cluster as a result (The FK to person will also be the PK of person_natural and person_legal).

  • For modeling natural and legal entities, you would most probably need an exclusive subtype, where a Person is a PersonNatural XOR a PersonLegal, so you will need to implement some additional structures and constraints. Also take a look at this answer for more details.

  • Using a similar method you can model the employee, farmer, customer, and supplier subtypes. Maybe in this case a non-exclusive subtype is more appropriate, where a Person is at least one of PersonEmployee, PersonFarmer, PersonCustomer, and PersonSupplier.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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