How to design a set of database tables with dynamic attributes?


Problem :

Now I get a problem:

For some items in the same table(category), they may have different attributes.For example there is a table called Product with two items called A and B in it.

And the attributes that A has are id, name, attr01, attr03, attr04

And the attributes that B has are id, name, attr02, attr03, attr05, attr06

There may be some more items like this.

And for each attrXX attributes, its format is like this:

    [attrbute_name, 
    {column_id: 01, column_name: xxx, relationship: xxx, value: xxx, unit: xxx},
    {column_id: 02, column_name: xxx, relationship: xxx, value: xxx, unit: xxx},
    {column_id: 03, column_name: xxx, relationship: xxx, value: xxx, unit: xxx},
    ...]

The number of columns in a attr is not fixed.

I got a little bit confused because this is totally different with the database I designed before. Thus I want to ask you guys how to design a set of tables for storing data with the format mentioned above. The database I am gonna use is MySQL. So I want a solution in MySQL environment.

Also I heard that I can solve this question by using NoSQL database like MongoDB or Cassandra. Could you tell me how to use such a NoSQL database into solving my question? I am not sure my client will accept it, but I want to introduce it to him.



Solution :

This looks relational to me.

You would have tables like:

Product (id, name)
Attribute (id, name)
ProductHasAttribute (ProductId, AttributeId)
Column (id, AttributeId, name, relationship, value, unit)

The contents of ProductHasAttribute would be:

A, 1
A, 3
A, 4
B, 2
B, 3
B, 5
B, 6

To get all the attributes of a product P:

select A.name
from ProductHasAttribute PA
inner join Attribute A on A.id = PA.AttributeID
where PA.ProductID = P

Column may need to be changed depending on whether the values depend only on attribute, or they also depend on the product.


Mysql Tutorials

Mysql Howto..

How to get a user with most cars from mysql

How to do a MYSQL conditional select statement

how to sort and get row number in one MYSQL Query

How to do MySQL + Substring? + replace?

How Can I Combine a SELECT Statement that Returns Multiple Rows with an INSERT Statement?

PHP MySQL Get Specific Value When Result Show [duplicate]

Mysql result not show inside div (only table header)

Given checkboxes, how do I delete the corresponding database row?

How do I select one row from a MySQL table?

How to add relationships between tables in existing MySql database?

How to call mysql date_format function in JPQL

How do I solve an issue that’s related to inserting entries into mysql using php?

How to make a list of most ordered item on database?

MYSQL/Query: How to make table rows into column

How do I use MySQL Left Join to order by the column of another table?

How do I store and iterate over a result set in a MySQL Stored Proc?

How to select from two tables in MySQL even if not all rows in one table have corespondents in the other?

How to join these two tables in MySQL?

MySQL Workbench - How to get results from multiple where clauses?

In MySQL, how to build index to speed up this query?

How can I put rows of MySQL data under the appropriate titles using PHP?

How to use ebean and mysql in play framework?

How to connect to a mysql database from Django?

How to design and test for _lots_ of concurrent data in rails?

How to duplicate MySQL fields?

how to create a composite key in MySQL database

How do I compute a ranking with MySQL stored procedures?

How to install MySQL Connector/C on Mac OS X?

How to add link to media on website PHP MYSQL

How to Calculate Top Customer in each City With Maximum order