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?

Thank you!

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?

that degree of generalization is up to you

Thanks!

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?

yes, mysql can handle that

500,000 is a small table

500,000,000 is a large table

500,000,000,000 is a very large table

performance will depend on proper indexing

I like the simplicity of the forum.
I would like to see the green in that header image caried out throughout the rest of the layout though, then it woulc fit in a bit more.

could you maybe explain which forum you're talking about?