Is it a good idea to design database with inheritance?

Question

For example, I have 2 tables : 'customer' and 'staff'. They are almost the same, only 2 attributes are different. So should I create another table named 'person' contains all of the same attributes of 'customer' and 'staff' then create fk keys point to this 'person'? Something like inheritance in class design.

Is there any drawback to this method ?

Answer 1

Yes, there is a drawback to that method. Joins increase query complexity (immensely so in some cases) and can increase query time if you're not careful.

Instead, the standard way to do this (i.e. simulate object inheritance when only a few attributes differ between the subclasses) is to do something called Single Table Inheritance. This method prevents database joins at the cost of a little bit of unused database space.

It works like this: You create one table that contains all the attributes, including the ones that only apply to one or the other, as well as a type attribute to specify the object type. For example, if customer has attributes:

id, name, email, password, order_date

AND staff has attributes:

id, name, email, password, hire_date

Then you create one table with columns for all the attributes and a type:

id, type, name, email, password, order_date, hire_date

The type column will always contain either "customer" or "staff". If type is "customer", then hire_date is always NULL, and is meaningless. If type is "staff" then order_date is always NULL, and is meaningless.