Mysql – Multiple Similar Tables vs One Master Table

best practicesMySQL

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?

Best Answer

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.

Related Question