I'm a web app developer and less experienced in database design, so would appreciate anyone's input.

When I first built this web app, the app managed one type of object (table) with a handful of properties (columns). As the app grew, there were additional types of very similar objects, so I created additional tables for each object type. (You could say that all the object types inherit from the same class/prototype.) I'm finding that my tables for each object type are all nearly identical because each object type shares ~ 90% of the same property types. For example, the tables may have nearly the exact same column names (& data types), but one table may have 1-2 extra columns to refer to properties specific to that object type.

Since all the object types are so similar (in the same way that Dog, Cat, and Mouse all inherit from the Animal class), would it be wise to put these all in the same table and simply allow some columns to contain NULL values when the columns are not relevant to that type? Or is there some other alternative I'm not considering?

For reference, here are some similar posts I've already reviewed:

1) What are the design alternatives when having multiple "type" tables

2) https://stackoverflow.com/questions/9021838/database-design-multiple-tables-vs-a-single-table

3) Database design: Dividing multiple identical tables, good or bad?

2 Answers 2


Create the Animal table which has information that all animals share. Add another column to the Animal table called type or something similar. In this, specify whether the animal is a cat, dog, mouse, or whatever.

Then, create Cat, Dog, and Mouse tables that have the Animal ID (The primary key) as a foreign key. Each of those tables has columns specific for Cats, Dogs, and Mice specifically. The Mouse table might have a column Favorite_cheese or some such, which the normal animals table would not. To get the full mouse record, you can join the two tables together.

This allows you to have a relatively lean Animal table while still supporting subclassing functionality.

  • Nice clear explanation of why you like this approach, that made my very similar issue more clear to me, thanks.
    – Lizardx
    Feb 2, 2016 at 21:48

The idea of single common master table instead of multiple is good but the integrity of Foreign Key constraint fails.

  • 2
    What do you mean by "The integrity of Foreign Key constraint fails"? Please explain what you mean and/or show us (an) example(s). Jan 22, 2015 at 8:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.