I suspect there are *MUCH* better ways of doing this ...

[lloydi]

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.

How would you approach something like this?

[Kalon]

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?

[lloydi]

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 _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.

Hope this helps explain a bit more

[ScallioXTX]

Sounds like a problem asking for a many-to-many table.

As an example:

tblEvents

id | name
------------------------
 1 | Pizza Night
 2 | Dance Night
 3 | Italian Dinner Gala

tblTags

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?

[jurn]

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.

Hope this helps explain a bit more

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

hope it helps..
Jurn

[IBazz]

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.


Events

event_name
------------------------
| Pizza Night        |
| Dance Night        |
| Italian Dinner Gala|

Tags

event_tag
----------------
|dance |
|food  |

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?

bazz

[r937]

Does that make sense?

yes!!

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

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 --

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

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 --

EventTags
event_id tag_id
201 dance
209 food
237 food
237 dance

saves an extra join in the sql, too

[lloydi]

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