Schema to support dynamic properties
- by Johan Fredrik Varen
Hi people.
I'm working on an editor that enables its users to create "object" definitions in real-time. A definition can contain zero or more properties. A property has a name a type. Once a definition is created, a user can create an object of that definition and set the property values of that object.
So by the click of a mouse-button, the user should ie. be able to create a new definition called "Bicycle", and add the property "Size" of type "Numeric". Then another property called "Name" of type "Text", and then another property called "Price" of type "Numeric". Once that is done, the user should be able to create a couple of "Bicycle" objects and fill in the "Name" and "Price" property values of each bike.
Now, I've seen this feature in several software products, so it must be a well-known concept. My problem started when I sat down and tried to come up with a DB schema to support this data structure, because I want the property values to be stored using the appropriate column types. Ie. a numeric property value is stored as, say, an INT in the database, and a textual property value is stored as VARCHAR.
First, I need a table that will hold all my object definitions:
Table obj_defs
id | name |
----------------
1 | "Bicycle" |
2 | "Book" |
Then I need a table for holding what sort of properties each object definition should have:
Table prop_defs
id | obj_def_id | name | type |
------------------------------------
1 | 1 | "Size" | ? |
2 | 1 | "Name" | ? |
3 | 1 | "Price" | ? |
4 | 2 | "Title" | ? |
5 | 2 | "Author" | ? |
6 | 2 | "ISBN" | ? |
I would also need a table that holds each object:
Table objects
id | created | updated |
------------------------------
1 | 2011-05-14 | 2011-06-15 |
2 | 2011-05-14 | 2011-06-15 |
3 | 2011-05-14 | 2011-06-15 |
Finally, I need a table that will hold the actual property values of each object, and one solution is for this table to have one column for each possible value type, such as this:
Table prop_vals
id | prop_def_id | object_id | numeric | textual | boolean |
------------------------------------------------------------
1 | 1 | 1 | 27 | | |
2 | 2 | 1 | | "Trek" | |
3 | 3 | 1 | 1249 | | |
4 | 1 | 2 | 26 | | |
5 | 2 | 2 | | "GT" | |
6 | 3 | 2 | 159 | | |
7 | 4 | 3 | | "It" | |
8 | 5 | 3 | | "King" | |
9 | 6 | 4 | 9 | | |
If I implemented this schema, what would the "type" column of the prop_defs table hold? Integers that each map to a column name, varchars that simply hold the column name? Any other possibilities? Would a stored procedure help me out here in some way? And what would the SQL for fetching the "name" property of object 2 look like?
