MySQL – Store Date as a Value with an ID

database-designdateeavMySQLnormalization

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!

Best Answer

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.