Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 and possibly a default value that corresponds to the type. Once a definition is created, a user can create an instance of that definition and set the property values of that instance.

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" and possibly a (default) value of 27. 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 ten instances of "Bicycle" and fill in the "Name" and "Price" property values of each bicycle.

This example uses only primitive property types (numeric and textual), but I want to support the following types:

  • Numeric (primitive)
  • Textual (primitive)
  • Boolean (primitive)
  • Object (compound, such as a "Bicycle" instance)
  • Enumeration (a set of one of the above)

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. Does anyone know this concept well enough to give me a few pointers, or even point me in the direction of a design pattern for this? I'm planning to implement this in MySQL.

I hope I've managed to explain the concept well enough.

Update: This question has been edited and posted at stackoverflow.com instead: http://stackoverflow.com/questions/4705860/schema-to-support-dynamic-properties

share|improve this question
    
This question would probably be a better fit for Stack Overflow, btw –  Gaius Jan 16 '11 at 12:35
    
    
I concur this is a better fit on the SO and to boot it's got better answers on SO –  jcolebrand Jan 17 '11 at 20:13
add comment

2 Answers

For those following along at home, he got an answer that he accepted on SO and hasn't updated this question to acknowledge that.

http://stackoverflow.com/questions/4705860/schema-to-support-dynamic-properties

Just FYI

share|improve this answer
add comment

Sounds like an application of a many-many relation (an M-N relation these were called at college). Something like (in the simplest possible terms):

create table templates (template_id number, template_name varchar(255));
create table objects (object_id number, template_id number);
create table properties (property_id number, property_name varchar(255));
create table template_properties (property_id number, template_id number);
create table object_properties (object_id number, property_id number, property_value varchar(255));

So to create a bicycle type:

insert into templates values (1, 'Bicycle');

To add name and size to bicycle:

insert into properties values (1, 'Size');
insert into properties values (2, 'Name');
insert into template_properties values (1, 1);
insert into template_properties values (2, 1);

To create an actual bicycle:

insert into objects values (1, 1);

To associate the properties with your bicycle:

insert into object_properties values (1, 1, 'Large');
insert into object_properties values (1, 2, 'My bicycle');

In practice you would obviously use indexes for performance and foreign keys to enforce consistency, and I have assumed that all your datatypes can be represented as varchars. You can, in your interface, populate menus just by selecting from templates, objects and properties.

share|improve this answer
    
Well, storing all values as varchars is what I would like to avoid, which is what makes this a little harder. That little tidbit wasn't very clear in my question, so I'll add that. Thanks, though. –  Johan Fredrik Varen Jan 16 '11 at 13:01
    
I'm not sure there's a way in an RDBMS to have different types per-row, it's all done in terms of columns –  Gaius Jan 16 '11 at 13:46
    
Care should be taken that the scope of this solution remain limited so that this platform not be used when some designed, ordinary tables will suffice. –  Leigh Riffel Jan 17 '11 at 19:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.