How to design a properties database

[Adam Dougherty]

I'm developing a site that allows users to design their own vehicle.

It is a fluid "drag and drop" system that stores design information in a database. The system is centered around "objects" (or "layers") where users can add 1 or more object/layer types (e.g. a car door) when designing their vehicle.

Initially the db design seemed relatively simple, and I planned on having an OBJECTS table, with an "object type" column.

Things became complicated when object "properties" came into play. All objects would have global properties like X position, Y position, etc, and the logical approach would be to just have all those properties as separate columns in the OBJECTS table. However, the complications arise when I considered type specific properties - For example: a car door object would have a color property, a handle property, and a trim property. Whereas a window object might only have a tint property.

I've been thinking about it for hours, and I couldn't come up with a logically efficient way to store type specific property data. As it is now, I have a different table for every different type of OBJECT type (e.g. OBJECT_door), but when I have to select and retrieve information via PHP I basically have to run a separate query on each object table, which to me, seems terribly inefficient.