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!
