Database Normalization question

[mattyt81]

Hi

 

Just want some advice on how to do the below:

 

I already have one table called 'venopt' which has two fields one is 'id' and 'venues'

 

I also already have a table for members details called 'ptdata' and has 'id' 'firstname' and 'surname' etc

 

Both ids in each table are primary keys and autoincremented.

 

The user will be selecting options from the 'venues' table and submitting them via POST.

 

How should i then handle the data, is it best to setup another table which will store the selections and if so how would i go about that?

 

Or is it best to store the details in an array say in the 'ptdata' table.

 

Either way I would also want to retrieve the contents of the array and display it in another drop down list?

 

Any ideas

[Psycho]

You had it right the first time. You have a one-to-many relationship between users and venues. You need a third table that will make those associations. You only need two columns: one for the ptdata.id and one for the venopt.id.

 

You don't give much specifics over how you want the SELECT this data to put in a drop down - there could be several scenarios. But, let's say you wanted to get a list of the venues selected by a particular user. You would at least need their user ID. Then it is a simple matter of joining the tables. So, let's assume the new table is called pt_ven_assoc and the fields are called ptdata_id and venopt_id (By the way I tend to give my "ids" unique names instead of just "id" and use the exact same name in the table where it is a primary key and where it is a foreign key. It makes things much easier to keep track of and you can also use USING() to easily JOIN your tables). The query might look something like this:

 

SELECT id, venue
FROM venopt
JOIN pt_ven_assoc ON venopt.id = pt_ven_assoc.venopt_id
WHERE pt_ven_assoc.ptdata_id = $userID

[xyph]

Generally, you'd want a row for each selection, specifying who made the selection and what the selection was.

If you had a small set amount of options that never/rarely changed, it would probably be smart to use a bitwise method.

 

Have another table with an id, ptdata_id and venopt_id.

 

The data held is so small that an index on each column doesn't bloat and allows extremely quick queries to find what users have selected a given venue, or what venues a given user has.