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

Lookup "generalization specialization relational modeling". You'll find some good articles on the subject. Most of the examples follow the same pattern as the Class Table Inheritance link that Bill gave you.

There's just one more little detail. The specialized tables (for customer and staff in your case) do not autonumber their id fieid. Instead, when you populate them, the id field should get a copy of the id field in the generalized table (person in your case).

This makes the specialized ids do double duty. They are both a pk and an fk reference to the corresponsding row in the genralized table. This makes joins easier and faster.

It can be convenient to create views that have each specialized table joined with the generalized table. Or you can make one large view that generates the same data you would see in a single table inheritance pattern suggested by another response. It's basically a union of a bunch of joins.

Answer 2

Both Pranay Rana and Ben Lee are correct, and the ultimate answer is: "it depends".

You have to weigh up the number of sub-class specific columns against the number of common columns to decide what's right for you. Single Table inheritance doesn't scale well: what happens when you have to introduce a third type of sub-class, such as suppliers?

For that matter how are you going to treat staff that are also customers?

Answer 3

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.

Answer 4

You're describing a pattern call Class Table Inheritance. It's a valid design, but like any other design, it must be used with good judgment. Read Martin Fowler's "Patterns of Enterprise Application Architecture" for more details on its advantages and disadvantages.

Some people caution against the use of joins, but you need a join only when you need the subclass-specific columns. When a given query only needs the common columns, you can avoid the extra join.

Answer 5

Well I say its good design because you are not repeating data and that's y the data normalization is there.

just one thing is that as much as you normalize your no of join will increase.