How to design a database for unkown amount of 'meta'-data

Question

I want to store certain items in the database with variable amount of properties.

For example:

An item can have 'url' and 'pdf' property both others do not en instead have 'image' and 'location' properties.

So the problem is an some items can have some properties and others a lot.

How would you design this database. How to make it searchable and performant?

What would the schema look like?

Thanks!

Answer 1

Create a properties table with the following fields:

item_id int(or whatever the ID type is in the item table) property_name varchar(500) property_value varchar(500)

Set a foreign key between item_id and the item's id field, and you're done.

That's how you do a many-to-one relationship in SQL.

Answer 2

I have designed tables like this in the past to have the following fields:

1. id
2. type
3. subtype
4. value

And then I would have another table that would define the type and subtypes used, and possibly give the datatype for that type and subtype combination so that you could programatically enforce it.

Its not pretty, and you don't want to do it unless you have to. But its the best way I have found when you do.

update: even if you leave subtype blank, I find its a good thing to have, because its too often that you want to subcategorize something that already exists. Example you create type: address, now you need mailing address and billing address and physical address.

Answer 3

Looks like an "items" table with primary key "item_id", a "properties" table with primary key "property_id" and a foreign key "item_id" with the "items" table. "properties" will have columns "name" and "value", both of type varchar.

Performant? Don't know.

Answer 4

Start with your object (item) and create a table for items. Your item can have 1 or many attributes or none at all right? So set up a table of attributes with unique ids. Now set up a table that holds many items (some can duplicate) and many attributes (can duplicate as well)

Item

ItemID

ItemDescription ...

Attributes

AttributeID

AttributeDescription ...

ItemAttributes

rowID

ItemID

AttributeID

Now when you want to query you can simply join the tables and filter however you desire...

Answer 5

What you are after has a name - Entity Attribute Value (EAV). It is "a data model that is used in circumstances where the number of attributes (properties, parameters) that can be used to describe a thing (an "entity" or "object") is potentially very vast, but the number that will actually apply to a given entity is relatively modest."

Answer 6

That sounds like a perfect job for a document database.

Answer 7

If you are not necessarily tied to SQL, a triple store is designed for precisely this task. Most are designed to be queried with the SPARQL query language.

Answer 8

For this kind of scenario's I use the XML-type column in MS SQL 2005... you'll have all the advantages of XML + SQL. That is use an XPath expression as part of an SQL-statement.

It's a feature of MS SQL 2005, I am not sure which other RDBMS support this. I am not sure what the implications are performance wise.

Answer 9

The Entity Attribute Value (EAV) model is very flexible. The semantic web and its query language sparql are based on EAV too. But some people don't like it because there is a performance penalty with this model.

Start with doing some high load performance tests on your database. Don't do them when you are done coding, because then it is too late.

edit: Focus on the speed of you select statements. Users expect quick results when they search.