Unanswered: What's the best way to represent unknown number of properties ...
I'm trying to design a schema for my database, and I'm stumbled at this problem. I really would like to use a multi-dimensional database for this, but that's not an option - I have to use MySQL.
I have a bunch of objects that could have an X number of properties. So an object O can have property1, property 2, property3, etc.. There can potentially be about a 100 of those. So what would be the best way to represent it in a database?
Having a column for each of those property, and just putting a NULL when an object doesn't have this property doesn't seem like the best idea. 100+ columns in your table is never a good idea, imo.
Googling for it produced no results, maybe because I don't know what's the best way to describe it, so don't really know what search terms to put there.
Has anyone encountered this problem before? Is there an official name for it? What's the best and the most efficient way to solve this?
create a table of properties, and load the 100 rows
create a table of objects
then create a table of objectproperties, such that there is one row for each object/property combination
the next question is, should the objectproperties table have, besides its two foreign keys, one numeric column, one character column, one datetime column, etc. (basically, one column for each datatype), which will mean that all but one of them will be null
or should there be an "objectproperties" table for each datatype?
I was considering that. The only thing that makes me doubt this solution is wouldn't that table get really large fairly soon? If I have 10,000 objects, each of them having, on average, 50 properties, the ObjectProperties table would have 500,000 rows. Can MySQL handle that? Is performance going to be ok?