Date: 11/30/05 (MySQL Communtiy)    Keywords: php, sql, web

I'm pretty new to db design. I had a class on MS Access/SQL back at PSU, but never really used it. So, I'm familiar, but I still don't know how to figure things out very well.


Anyway, here is my problem: I am attempting to make a db for the upcoming college bowl season. Every year, my family picks the bowl games and whoever wins gets a special prize. I want to make it into a db this year, though, so that you can view any pickset without having to see ALL of them (there are 28 bowls + at least 10 people, so you can see that is a ton).

I tried making a table with the bowls in it like this:

ID | Name | Winner
------------------------------
1 | Bowl 1 | Team 1
2 | Bowl 2 | Team 2

then tried making a table with the picks in it like this:

ID | Name | Pick
-------------------------
1 | Jim | Team 1
2 | Tony | Team 2

but as you can see that doesn't work out, because each person only gets one pick in the table.

So the next thing I tried was making a seperate table for each person's picks with just the bowl name and the pick in it. It seems like that will amount to a ton of tables, though, so I'd rather try something more compact.

The next thing I toyed around with was making a table like so:

Table: Pickset

ID | Name | Bowl 1 | Bowl 2 | Bowl 3
--------------------------------------------------------
1 | Jim | Pick 1 | Pick 2 | Pick 3
2 | Tony | Pick 1 | Pick 2 | Pick 3

But I can't figure out how to relate it to the other table. Maybe I don't need to? I will be using PHP to display it on the web, by the way.

Basically I want the page to say:

Display Picksets:
Jim [ ]
Tony [ ]
SUBMIT

So that whatever you check off will display in a nice table.

Can anyone help me out here..?

Source: http://www.livejournal.com/community/mysql/74483.html