SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Posts rarely lloydi's Avatar
    Join Date
    Jan 2002
    Location
    Swindon UK
    Posts
    620
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?
    Build Your Own Web Site the Right Way!
    A beginners' HTML/CSS book with web standards at its heart
    The Ultimate HTML Reference
    A complete reference, in glorious hardback

  2. #2
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  3. #3
    Posts rarely lloydi's Avatar
    Join Date
    Jan 2002
    Location
    Swindon UK
    Posts
    620
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Kalon View Post
    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
    Build Your Own Web Site the Right Way!
    A beginners' HTML/CSS book with web standards at its heart
    The Ultimate HTML Reference
    A complete reference, in glorious hardback

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Sounds like a problem asking for a many-to-many table.

    As an example:

    tblEvents
    Code:
    id | name
    ------------------------
     1 | Pizza Night
     2 | Dance Night
     3 | Italian Dinner Gala
    tblTags
    Code:
    id | name
    ----------------
     1 | dance
     2 | food
    tblEventTags (<-- The many-to-many table)
    Code:
    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?
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lloydi View Post
    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

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Code:
    event_name
    ------------------------
    | Pizza Night        |
    | Dance Night        |
    | Italian Dinner Gala|
    Tags
    Code:
    event_tag
    ----------------
    |dance |
    |food  |
    EventTags (<-- The many-to-many table)
    Code:
    |event_name          | tag_name |
    -----------------
    | Pizza Night        | food     |
    | Dance Night        | dance    |
    | Italian Dinner Gala| food     |
    | Italian Dinner Gala| dance    |
    Does that make sense?

    bazz

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Posts rarely lloydi's Avatar
    Join Date
    Jan 2002
    Location
    Swindon UK
    Posts
    620
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Build Your Own Web Site the Right Way!
    A beginners' HTML/CSS book with web standards at its heart
    The Ultimate HTML Reference
    A complete reference, in glorious hardback


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •