Does mutliple selects need a second table?

[jeff5656]

If I have a drop down where a user can select multiple items (or a check box to check more than one) how do I store that in the database?  Do I *have* to create a second table and then JOIN the two tegether or is there a way of doing it with only one table?

[rhodesa]

depending on the situation, it can sometimes be acceptable to just join the data with a character (like a comma) then split on the data to read it back. instead of join, you can also use serialize()/unserialize() to turn an array into a string and back. this way uses more characters, but is safer

[flyhoney]

I agree with rhodesa.

 

However, the most extensible solution uses three tables.

 

table items

    id int(11) primary key auto_increment

    name varchar(32)

 

table users

    id int(11) primary key auto_increment

 

table users_items

    id int(11) primary key auto_increment

    item_id int(11)

    user_id int(11)

 

And then to get a user's choices:

SELECT
    items.*
FROM
    items 
    LEFT JOIN users_items
    ON users_items.item_id = items.id
WHERE
    users_items.user_id = 828

 

This would get the items associated for user id = 828

[premiso]

I agree with rhodesa.

 

However, the most extensible solution uses three tables.

 

table items

    id int(11) primary key auto_increment

    name varchar(32)

 

table users

    id int(11) primary key auto_increment

 

table users_items

    id int(11) primary key auto_increment

    item_id int(11)

    user_id int(11)

 

And then to get a user's choices:

SELECT
    items.*
FROM
    items 
    LEFT JOIN users_items
    ON users_items.item_id = items.id
WHERE
    users_items.user_id = 828

 

This would get the items associated for user id = 828

 

I love 3NF databases!