How to store data with dynamic number of attributes in a database

user_154910 0
Viewed 24332

I have a number of different objects with a varying number of attributes. Until now I have saved the data in XML files which easily allow for an ever changing number of attributes. But I am trying to move it to a database.

What would be your preferred way to store this data?

A few strategies I have identified so far:

  • Having one single field named "attributes" in the object's table and store the data serialized or json'ed in there.
  • Storing the data in two tables (objects, attributes) and using a third to save the relations, making it a true n:m relation. Very clean solution, but possibly very expensive to fetch an entire object and all its attributes
  • Identifying attributes all objects have in common and creating fields for these to the object's table. Store the remaining attributes as serialized data in another field. This has an advantage over the first strategy, making searches easier.

Any ideas?

rdbms mysql database
8 Answers

I am assuming you do not have digital attribute soup, but that there is some order to your data.

Otherwise, an RDBMS might not be the best fit. Something along NO SQL might work better.

If your objects are of different types, you should generally have one table per type.

Especially if you want to connect them using primary keys. It also helps to bring order and sanity if you have Products, Orders, Customers, etc tables, instead of just an Object and Attribute table.

Then look at your attributes. Anything that exists more than, say for 50% of the objects in that type category, make it a column in the object's table and use null when it's not being used.

Anything that is mandatory, should, of course, be defined as a NOT NULL column.

The rest, you can either have one or several "extra attributes" tables for.

You could put the attribute names into the table with the values, or normalize them out in a separate table and only use the primary key in the value table.

You may also find that you have combinations of data. For instance, a variant of an object type always has a certain set of attributes while another variant of the same object type has another set of attributes.

In that case, you might want to do something like:

MainObjectTable:
  mainObjectId: PRIMARY KEY
  columns...
MainObjectVariant1Table:
  mainObjectId: FOREIGN KEY TO MainObjectTable
  variant1Columns...
MainObjectVariant2Table:
  mainObjectId: FOREIGN KEY TO MainObjectTable
  variant2Columns...

I think the hard work, that will pay off, in the long run, is to analyze the data, find the objects and the commonly used attributes and make it into a good "object/ERD/DB" model.

user_3865870
Related