Generalization vs Specialization of DB table [closed]

Question

When I want to design database tables that model a finite parent-child relationship, for e.g., computer as parent, and the components inside as the child; or a simple organizational hierarchy, I am usually torn at which approach to use -

1. A 'specialized table' approach, in which I create a table for each of the possible entity. In the computer component example, I would have a Computer table and a Component table, with the ComputerID as FK in the Component table referencing back to Computer.ComputerID. or
2. A 'generalized table approach, in which I have one table table called Component, with ComponentID as PK, and a ParentComponentID as FK referencing to its parent's ComponentID.

Answer 1

The reason I choose option 1 over 2 most of the time is because it is easier to pull my parent rows out of the database. Otherwise, you have to write the sql statement such that you get the rows from the table that have children rows, but only those. It gets messy quick.

If you're using any of the common relational databases, then use them the way they're designed to be used.

Answer 2

There's a great danger in relational design when you try to create generalisations that aren't actually true, but look elegant. If you have a general table with a type field, that introduces a form of indirection that has to be resolved in your code instead, which can hurt both performance and the clarity of the code.

On the other hand, such indirection can have its uses, and for certain purposes I use it quite a lot. The question you have to ask is whether Computer and Component really are aspects of the same thing, or whether you're just fudging them together because it sounds good.