Extending classes in the database

Question

I am working on a php project that needs to store information about various buildings and will store different types of information depending on the type of the building:

Class Building {
    var $location
    var $name
}

The Building class will be extended by classes like, House and Office so the classes will look like this (just an example)

Class House Extends Building {
    var $numRooms;
    var $numBathrooms;
}

Class Office extends Building {
    var $offices;
    var $sqfoot;
}

The question is, how should this be represented in a database (im using MySQL, if it matters). Should I use the building table to store the location and name and then create a table for each of the other classes to hold 'extended' variables? Or do I create a table for each class? or should the building table have columns for all the variables that can be in the other classes?

Answer 1

If the idea is to not complicate things with a bunch of tables and fields I would simply create a single table for Building and then add two fields for the data, one would define the type of building (ie: house, office, etc...) the other would be a serialization of the extended class object data.

But I think the best way to do it will actually depend on the kind of queries you'll need to make to the database. For instance, if you need to query the database to select all "houses" with "$numRooms > 5", serialization may not be the best option...

Answer 2

It is as duplicate of (mine) http://stackoverflow.com/questions/836383/how-do-i-represent-object-classification-hierarchy-in-a-rdbms Check the answers there.

Answer 3

I would strongly recommend that you look at the Class Table Inheritance pattern as defined by Martin Fowler.

This design pattern creates a single table that holds the data that is common to all Buildings for example and then requires a separate table for any data that is related to a specific type of building. One thing that I find helpful is to store a 'type' field in the parent table so that you know what type of entity you have without having to search the child tables for corresponding records.

I would recommend that unless you have a very specific, well-defined reason to use an Entity-Attribute design, that you avoid it. For one thing it becomes impossible when using this type of design to take advantage of constraints on the database to control what inputs are required and what types of values are allowed. For another, it will drastically slow down any queries that need to pull data out of those types of fields because data stored in those fields cannot be indexed like you would typically.