DB normalization vs data identificator

Question

I do have some idea about the database normalization and yet sometimes I think, that in some cases there is a more elegant solution than ONE DATA OBJECT = ONE TABLE pattern (I believe).

For example: storage of articles and static pages. The data are very similar and we could place them in one table using a simple identificator column and because of difference what data we are actually storing there would be some blank unused cols for every data type:

id | title(both) | author(article only) | content(both) | datetime(article only) | type(identificator - article/page)

Is this approach still correct or there should be table for each data no matter the similarities?

Answer 1

You're describing Single Table Inheritance. It's a legitimate pattern, see http://martinfowler.com/eaaCatalog/singleTableInheritance.html

Advantages include:

• One table contains all subtypes.

Disadvantages include:

• You can't define NOT NULL constraints on subtype-specific columns, since they must be nullable.
• UNIQUE or FOREIGN KEY constraints on subtype-specific columns may not be supported (depending on RDBMS implementation).
• If you have many subtypes, the table will have a lot of columns, most of which will be NULL. Some database have a limit on the number of columns for a given table. But in the case of MySQL, the maximum columns per table is 4096. InnoDB doesn't store NULLs, so this shouldn't be a problem.
• If you frequently add new subtypes, you'll have to ALTER the table every time you add more subtype-specific columns.

You might want to read about alternatives, such as Class Table Inheritance (http://martinfowler.com/eaaCatalog/classTableInheritance.html).