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
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 "13_" 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.
How would you approach something like this?
1) what is the structure of the second table?
2) it's not clear to me what exactly you are trying to do. Are you trying to make sure a user doesn't use a tag more than once?
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 13_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.
Hope this helps explain a bit more
Sounds like a problem asking for a many-to-many table.
As an example:
id | name
1 | Pizza Night
2 | Dance Night
3 | Italian Dinner Gala
id | name
1 | dance
2 | food
tblEventTags (<-- The many-to-many table)
event_id | tag_id
1 | 2
2 | 1
3 | 1
3 | 2
So event 1 has tag 2, event 2 has tag 1, and event 3 has tags 1 and 2
Does that make sense?
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:
hope it helps..
here's another suggestion which can make you data easier to follow as FKs.
It makes the assumption that the data in each row of the first two tables, is to be unique i.e., each row will appear just once.
| Pizza Night |
| Dance Night |
| Italian Dinner Gala|
EventTags (<-- The many-to-many table)
|event_name | tag_name |
| Pizza Night | food |
| Dance Night | dance |
| Italian Dinner Gala| food |
| Italian Dinner Gala| dance |
Does that make sense?
oh, yes, yes, yes
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
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")
201 Pizza Night
209 Dance Night
237 Italian Dinner Gala
Tags (not "tblTags")
EventTags (not "tblEventTags")
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 --
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