[SOLVED] database structuring question....

[Derleek]

I'll give you guys the background of the website I'm attempting to make:

 

Essentially i am creating a game for a friend that is a motocross fanatic.  I am creating a script that takes 10 riders from a user and compares it to the actual race data.  The database structure needs to be set up to take 10 picks from probably quite a bit of users weekly (so space is an issue).  This is the same for the race results database.

 

I have the user table (name, password, email, etc.) set up already.

 

I see this being able to go in a couple different directions, i'm just wondering what would be the easiest most efficient way to do this.

 

First:

Should i create the individual user's input table to house all of the picks in one slot?

 

    ex:

User 1 |	pick1/pick2/pick3
User 2 |	Pick1/Pick2/Pick3 etc...

 

or should i create it so each pick has its individual slot in the table?

 

Second:

Should i create an individual table for every race week or just append the table every week.  I'm not positive how the second option could be done, but i'm assuming table's can be edited (i have too look into this obviously)

 

Here is the general layout of the database i'm seeing

 

User Database:

    Name

    User name

    password

    ID#  etc...

 

User input Database:

    ID#

    Picks

    Race#? (this is the part i'm unsure about)

 

Race Results Database:

    Top 10

    Over all Results

 

So any suggestions are definitely encouraged and welcome 

[rhodesa]

I would have a RACE table. With a Race ID, and then info about that race. 1 Entry = 1 Race

Then, a RACERS table, with 2 columns, Race ID and User ID. There would be an entry for every racer for each race. So 10 rows per race.

 

This will allow the greatest flexibility (in case you want to change the number of racers/race in the future)

[Derleek]

I'm not quite grasping your suggestion...

 

are you saying to create a table for every user?

[rhodesa]

No...

 

Table for races. So every race that happens, add a row in here.

Tables for racers. This table simply maps which users are in which race.

 

--USERS--

user_id user_name    ...and whatever other columns

------- -------------

1      John Smith

2      Jane Doe

3      Bill Gates

4      Big Bird

5      Cookie Monster

 

--RACES--

race_id race_type race_date

------- --------- ----------

1      weekly    2008-05-13

2      weekly    2008-05-20

3      other    2008-05-22

 

--RACERS--

race_id user_id

------- -------

1      1

1      3

1      4

2      2

2      3

2      4

2      5

3      1

3      5

 

 

[Derleek]

ahhhh yes.

 

now, would i store the 10 selections of each user in the 'racers' table?

 

i think i was a little vague on that part.

 

there are 40 actual racers that the users will be choosing from, they select what they think the top 10 racers of each race will be.  this is compared to the actual results of the race, which would probably be another table.

[rhodesa]

oh...ok...i understand now. i thought the users were in the races. these would be my tables:

 

USERS => We've covered this

RACES => We've covered this

RACERS => (Completely different from before) Holds racer info (racer_id, racer_name, etc)

RACE_RACER => Map Racers to Races. Two columns (race_id, racer_id)

USER_PICKS => Map picks to races/racers. Three columns (user_id, race_id, racer_id)

[Derleek]

that makes complete sense.

 

So just to be clear you think the best way to store the picks would be in one column(racer_id: in USER_PICKS)? probably sepparated by a filler character like '/'?

 

because each user will be picking 10 racers each week...

[Barand]

So just to be clear you think the best way to store the picks would be in one column(racer_id: in USER_PICKS)? probably sepparated by a filler character like '/'?

 

That would be a terrible way to do it.

Have a separate row for each racer picked by the user for the race.

[Derleek]

i'm having trouble visualizing how that would work using rhodesa's model for a database?

[Derleek]

OOOH, the user id/race id would stay the same in each entry, its just the racer_id that would change... GOOOT IT

[Barand]

Suppose

 

User ID : 1

Race ID : 10

 

User picks racers 2,4,6,8 and 10

 

USER_PICKS table

[pre]

pickID    userID      raceID      racerID

  1          1          10          2

  2          1          10          4

  3          1          10          6

  4          1          10          8

  5          1          10          10

[/pre] 

[rhodesa]

Suppose

 

User ID : 1

Race ID : 10

 

User picks racers 2,4,6,8 and 10

 

USER_PICKS table

[pre]

pickID    userID      raceID      racerID

  1          1          10          2

  2          1          10          4

  3          1          10          6

  4          1          10          8

  5          1          10          10

[/pre] 

 

Yup, that's it. Personally, I wouldn't have a pickID column, others will disagree, but that is my preference. I just do a primary key over all three columns.