CODEWITHSUNDEEP
databasedatabase-designschemaentity-attribute-value
Sander Versluys
Sander Versluys

Reputation: 74427

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

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!

Upvotes: 2

Views: 1004

Answers (9)

tuinstoel
tuinstoel

Reputation: 7306

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.

Upvotes: 0

Cohen
Cohen

Reputation: 2720

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.

Upvotes: -1

allclaws
allclaws

Reputation: 5705

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.

Upvotes: 2

Jörg W Mittag
Jörg W Mittag

Reputation: 369428

That sounds like a perfect job for a document database.

Upvotes: 1

Paul Dixon
Paul Dixon

Reputation: 300825

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."

Upvotes: 8

Eppz
Eppz

Reputation: 3206

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...

Upvotes: 0

duffymo
duffymo

Reputation: 308743

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.

Upvotes: -2

Ryan Guill
Ryan Guill

Reputation: 13886

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.

Upvotes: -1

user54650
user54650

Reputation: 4426

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.

Upvotes: -2

Related Questions