store objects in database

Question

I have lot of objects, each can have many different kind of attributes. a lot of the attributes overlap, for example, many objects have the attribute "name". Since there are many type of objects, if there is a table constructed for each set of objects with same set of attributes, it will take lot of tables.

Currently I'm using a mysql, and it is stored this way.

object_id|attribute_id|data

There are a few tables like this, different table have different type of data. For example, in a Integer table, it only store integers

So I can store all kind of object in the system and it is still possible to do filtering and sorting according to the data of the attributes. It make queries a bit more complex and I don't think it is efficient.

Is this is only way to do it in mysql or other relational databases? Should I try to find other database models for this problem?

EDIT I did some research, it seems what I need is a Document-oriented database with the power of SQL.

Answer 1

I think what you want to do is research the area called "object relational mapping". It is a big area with many solutions. Some are used in frameworks, such as ""ActiveRecord used in Rails.

Answer 2

Using one table is possible, and probably preferable. Have a table column for each of attributes from all of the object types. Then add one more column to indicate the 'object type'. You can get all the objects of one type out of the table with a simple query for rows with a particular object type name.

Answer 3

An alternative to using an ORM (which is a very good idea), is to instead use the language's in-built serialization functionality (if it has one, otherwise a library instead) to transform the object into a byte buffer, and store that in a database, with a suitable key to look it up. This works pretty well with key-value databases such as memcached.

Answer 4

Your design is called Object-Attribute-Value (OAV). It's one way to map objects to a relational database. It's generally very slow, but very flexible. If your class definitions change constantly (or change dynamically) this may be your only choice.

You can use Object-Relational-Mapping (ORM). It's another way to make objects to a relational database. It's much faster, and somewhat less flexible. If your class definitions don't change very often (and don't change dynamically) this is a far, far better choice.

Answer 5

If you want to go with a RDBMS-like document-oriented database, I think you should look into mongoDB:

A key goal of MongoDB is to bridge the gap between key/value stores (which are fast and highly scalable) and traditional RDBMS systems (which are deep in functionality).