0

I've got a database with the following structure:

Elements table
+------------+---------------+---------------+-----+
| element_id |    user_id    | element_data2 | ... |
+------------+---------------+---------------+-----+

Tag categories table
+-------------+---------------+
| category_id | category_text |
+-------------+---------------+

Tag values table
+----------+------------+
| value_id | value_text |
+----------+------------+

Tag-element relationship table
+----+------------+-----------------+--------------+
| id | element_id | tag_category_id | tag_value_id |
+----+------------+-----------------+--------------+

An element can have multiple tags associated with it, even multiple values on the same category.

What the app does is to let the user put new tag_values as unique elements (categories right now are not user-editable, but may be in a future). As they are not associated to the user, if two users write the same value_text, then the same ID is assigned to it.

So, to have a data sample, the tag-element table - the most important here - would look something like:

+----+------------+-----------------+--------------+
| id | element_id | tag_category_id | tag_value_id |
+----+------------+-----------------+--------------+
|  1 |          1 |               3 |            9 |
|  2 |          1 |               3 |            5 |
|  3 |          1 |               6 |           13 |
|  4 |          2 |               6 |            2 |
|  5 |          2 |               5 |           15 |
+----+------------+-----------------+--------------+

The question

As such, there is a category called game_date, which stands for the date where the element (that is an event) is celebrated.

Should the game_date values be stored as the other values, so two equal values for a date have a unique ID, or its uniqueness makes its performance poor?

I mean, it can be possible for multiple events to be on the same date and hour, but I don't know if it would be better another kind of structure where that date is stored as a Date field on any of the tables above. Just as the game_date can be also other categories involving date (such as game_recorded, etc.).

Thank you!

2

It sounds like you have reinvented "Entity-Attribute-Value" schema. This will be painful in the long run. Meanwhile...

Do not "normalize" dates or other 'continuous' values (such as FLOAT).

The DATE datatype is only 3 bytes, and may as well be put in whatever table it best fits in, not normalized into an attribute table.

Relationship tables usually do not need an AUTO_INCREMENT id PRIMARY KEY since a combination of columns makes a better PK. More tips here.

  • Thank you! I will wait to see if someone comes with another approach, but I will mark this as the answer by tomorrow! Thank you again! – Unapedra Dec 6 '16 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.