It it bad practice to use inheritance in database to make it less painful to change schema latter?

I'm thinking on new design for database. And I want to use Table-Per-Type inheritance inside database. I want to separate audit data (like create|modify dates, user_id , permissions,etc) from actual data, and use some abstract objects(tables) but I don't plan to have deeper than 3 inheritance (to make things simple and fast). The reasons for this I want my database design to be flexible, and easy to modify, without lots of impact on my code. And using inheritance and extending existing functionality that way looks like a good choice to me.

开发者_如何学C

I read some posts that using inheritance in database is bad practice, but I never seen explanation of that and what exact problems can occur.

---

I want my database design to be flexible, and easy to modify, without lots of impact on my code.

First, you're just describing Object-Relational Mapping (ORM). You need to update your question to specify a language, and a database and an ORM.

Second, this kind of flexibility is often an attractive nuisance.

You don't specify what database you're using, but there can be a fundamental contradiction in your desires.

A SQL database has a relatively fixed, less-than-perfectly-flexible schema so that it can be fast.

Asking for flexibility means you must now make certain kinds of relatively painful and complex changes to the database in the name of "flexibility". Table design changes that involve moving (and possibly normalizing) data can be painful and difficult because the database isn't generally designed for flexibility.

More importantly, however, there's no simple mapping from inherited attributes to database tables.

There are times when superclass attributes should be copied into multiple tables.

However, there are also times when the superclass attributes should be a separate table with an explicit join to the subclass tables.

As you read more about ORM's, you'll see that this is a problem of non-trivial complexity. It requires thinking, and each design decision has important consequences.

There is no "blanket" solution. Your vague "And I want to use Table-Per-Type inheritance inside database" isn't a good idea or a bad idea. It's a vague idea. Each individual table and each individual inheritance decision must be made individually based on performance requirements and the expected need for flexibility.

---

As an answer to my own question: I have reread Martin Fowler's book patterns of enterprise application architecture about database inheritance. And it helped me to clear my thought and choose my inheritance strategy. Nothing bad in using inheritance in database, and as S.Lott said, this requires thorough thinking and testing.