I am no MySQL expert … as I’m about to demonstrate
I have a DB with multiple tables. In one table, I have:
id (primary key) tag
1 monkey
2 cat
3 dog
etc
In another table I have a field ‘tags_used’ which contains any ids of tags used for a given person. I’m storing it as a string with underscore separators, e.g “1_3” and then breaking it apart in PHP and doing nested lookups for each id found.
This seems really inefficient. There must, surely, be a better way of storing the data and retrieving it again in PHP. I’ve got it working, sure, but it’s cumbersome.
I want people to enter a tag for an event. If that tag has not been used, it gets added to the first table tblEventTags with an associated id. At the same time, the id for that newly created tag in the tblEventTags table gets added to the tblEvents table which has a field called event_tags.
Rather than store all the event tags as comma separated values in a string in the event_tags field, I’m saving the id related to it, hence, the event_tags field may have 1_3_5_7. I explode that string, then loop through each item in the array to find the tag name in the tblEventTags table.
So, the question is really about whether this is the best way to store data like this in the tblEvents table > event_tags field.
It sounds like you have a many-to-many relationship between Events, and Event Tags?
So rather than the csv event_tags field string, I’d have a separate table to capture this relationship… e.g. maybe call it EventTagsUsed
and it’ll have 2 columns:
tag_id, event_id
perhaps i am in a small minority but when numeric surrogate keys (of dubious merit in the first place) are absent in an example, it allows the concept more easily to shine through
so, yes
ScallioXTX’s example is exactly the same, but you have to shift your eyes from the many-to-many table back to the referring tables in order to interpret the relationships (you’re performing a “virtual join” in your brain)
in instances where i need to do this, i avoid confusion by giving different keys a different range of values, like this --[indent]Events (not “tblEvents”)
id name
201 Pizza Night
209 Dance Night
237 Italian Dinner Gala
Tags (not “tblTags”)
id name
1 dance
2 food
EventTags (not “tblEventTags”)
event_id tag_id
201 2
209 1
237 1
237 2[/indent]
note: i don’t actually assign different ranges in my database, i meant for the purposes of giving an example)
in practice i would never use an event name as a primary/foreign key, but i definitely would use the tag itself
so it would end up like this --[indent]EventTags
event_id tag_id
201 dance
209 food
237 food
237 dance[/indent]
saves an extra join in the sql, too
Thanks folks. That all makes sense. I so rarely build DBs from ground up that this kind of thing is not always obvious to me. The more I work on this project, the more I’m reminded how much of a noob I am with MySQL