2

I wanted to ask you which could be the best approach creating my MySQL database structure having the following case.

I've got a table with items, which is not needed to describe as the only important field here is the ID.

Now, I'd like to be able to assign some attributes to each item - by its ID, of course. But I don't know exactly how to do it, as I'd like to keep it dynamic (so, I do not have to modify the table structure if I want to add a new attribute type).

What I think

I think - and, in fact, is the structure that I have right now - that I can make a table items_attributes with the following structure:

+----+---------+----------------+-----------------+
| id | item_id | attribute_name | attribute_value |
+----+---------+----------------+-----------------+
|  1 |       1 | place          | Barcelona       |
|  2 |       2 | author_name    | Matt            |
|  3 |       1 | author_name    | Kate            |
|  4 |       1 | pages          | 200             |
|  5 |       1 | author_name    | John            |
+----+---------+----------------+-----------------+

I put data as an example for you to see that those attributes can be repeated (it's not a relation 1 to 1).

The problem with this approach

I have the need to make some querys, some of them for statistic purpouses, and if I have a lot of attributes for a lot of items, this can be a bit slow.

Furthermore - maybe because I'm not an expert on MySQL - everytime I want to make a search and find "those items that have 'place' = 'Barcelona' AND 'author_name' = 'John'", I end up having to make multiple JOINs for every condition.

Repeating the example before, my query would end up like:

SELECT * 
FROM items its 
JOIN items_attributes attr 
   ON its.id = attr.item_id 
      AND attr.attribute_name = 'place' 
      AND attr.attribute_value = 'Barcelona' 
      AND attr.attribute_name = 'author_name' 
      AND attr.attribute_value = 'John';

As you can see, this will return nothing, as an attribute_name cannot have two values at once in the same row, and an OR condition would not be what I'm searching for as the items MUST have both attributes values as stated.

So the only possibility is to make a JOIN on the same repeated table for every condition to search, which I think it's very slow to perform when there are a lot of terms to search for.

What I'd like

As I said, I'd like to be able to keep the attributes types dynamical, so by adding a new input on 'attribute_name' would be enough, without having to add a new column to a table. Also, as they are 1-N relationship, they cannot be put in the 'items' table as new columns.

If the structure, in your opinion, is the only one that can acheive my interests, if you could light up some ideas so the search queries are not a ton of JOINs it would be great, too.

I don't know if it's quite hard to get it as I've been struggling my head until now and I haven't come up with a solution. Hope you guys can help me with that!

In any case, thank you for your time and attention!

Kind regards.

Improve this question

2 Answers 2

Reset to default
2

You're thinking in the right direction, the direction of normalization. The normal for you would like to have in your database is the fifth normal form (or sixth, even). Stackoverflow on this matter.

Table Attribute:

+----+----------------+
| id | attribute_name | 
+----+----------------+
|  1 |          place |
|  2 |    author name |
|  3 |          pages |
+----+----------------+

Table ItemAttribute

+--------+----------------+
| item_id|   attribute_id | 
+--------+----------------+
|      1 |              1 |
|      2 |              1 |
|      3 |              2 |
+--------+----------------+

So for each property of an object (item in this case) you create a new table and name it accordingly. It requires lots of joins, but your database will be highly flexible and organized. Good luck!

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

it doesnt help because in this case he has this records in table author_name | John and author_name | Kate and actually pages | 200 and you should add one more table for normalization this table
Yes, that's what I meant with the next to last sentence. For that instance he should add the ItemPages table and insert (1,200) as entry.
Thanks for your time and answer! I see that all solutions will be pointing to the same direction, as you said, the normalization. I'd like not to have as many tables as it seems I'll have to, but if it's the best approach then there's no excuse! I'll see if anyone comes with some other approach to review. Thanks for your time and the references!
1

In my Opinion it should be something like this, i know there are a lot of table, but actually it normilizes your DB enter image description here Maybe that is why because i cant understant where you get your att_value column, and what should contains this columns

Improve this answer

1 Comment

Thanks for your answer! The value columns are the item properties put dynamically by the user. So the user writes that the author is John and Kate, and that the place is Barcelona. But, the user should be able to write a custom field like, for example, 'weather' and write in it 'cloudy' (I'm writing a silly example, but for you to know where they come from). It's for this reason I need them to be dynamical.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.