Skip to main content
edited tags
Link
Rick James
  • 79.4k
  • 5
  • 51
  • 117
Source Link

MySQL - Store date as a value with an ID?

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!