MySQL data modeling.

Question

I have this current problem with data i have to represent in a MySQL database:

I have to support different kinds of products with different data each one, for example, cars, hotel rooms, books, etc. For my application there is some data which is common for each product, like name, description, price, etc.

So I think of having a product table, with common data, with cars, hotel_rooms, books and whatever tables referencing this product table in a 1:1 relationship.

The problem is having the product table as a master table, so I can query the product table and get the extra data "automagically" with those queries, so I can make the application more "generic".

Any ideas of how can this be accomplished? I have a few ideas, but I don't think they are the best way of doing it and I don't want to go for dirty hacks when this problem may be more common than I think.

Using: PHP + Zend Framework + MySQL.

Answer 1

Check out http://stackoverflow.com/questions/45621/how-do-you-deal-with-polymorphism-in-a-database

Answer 2

Have a base object, then use polymorphism to subclass that base object. The base object stores all the shared data (in 1 table).. Then each subclass has it's unique properties and sources them from a separate table key'd into the base table. Make the subclasses capable of loading both the common data from their parent object, aswell as loading their unique/distinct data.