[SOLVED] Best way to create this table (awards)

[xoligy]

Ok im going to try and code an awards page and im working on the mysql table for now but im unsure of the best way to do it. The way that im thinking of at the moment us as follows:

 

id - username - sup1 - sup2 - sup3 - fre1 - fre2 - fre3 - kil1 - kil2 - kil3

 

where 1-3 are ranks 1st to 3rd so if playerA win sup1 and kil1 he gets a 1 in them boxes if at a later date he wins sup1 and then kil3 sup 1 would be 2 and kil1&3 would be 1

 

Would you say thats the best way to do it? or can you think of another?

[Mchl]

Only if you know there will only be those 9 awards and no more awards will appear in the future

[xoligy]

I dont think there will be anymore anytime soon just depends on what "other" ideas i can think of lol if you have a suggestion please say as all feedback is always appreciated!

[Mchl]

If you expect there might be more awards i nfuture, then you should plan your database, so that adding new awards is easy.

 

You will need three tables

 

table users
userID, username

table awards
awardID,awardName

table users_awards
userID, awardID, count

Also create primary key in users_awards table on userID and awardID

 

So if you want award player 1 with award 1 you do

 

INSERT INTO users_awards VALUES (1,1,1) ON DUPLICATE KEY UPDATE count = count + 1

[xoligy]

When i get home i'll have a better look at everything i would only need the two tables if i did it your way as i already have the user details setup its just the awards that needs doing sorry i didnt mention that

[Mchl]

I didn't mean you need three new tables, but that you need three table layout for it to work

[xoligy]

hmm... going to have to think about this more tomorrow im tired now. but the way i was thinking was just one table with all the info in it for awards then i would query just the userid/name which would return the rewards for that user, the way your suggesting i would need to do a union or join yes? im not that smart lol ive done one of them so far and i only got that working by changing the query from "=" to ">"

 

like i said i'll think about it more when im not as tired and have a smarter head on me lol

[Mchl]

You'd have to use JOIN with the design I presented. It's not difficult.

[xoligy]

Yes i  see that now yesterday i was really tired lol. I was going to write back earlier but went the shop instead, and thinking about it i think my way would be better as its going to keep the db more compact say the player gets 6 awards in total over x time thats 6 inserts with 6 records my way its just 1 record for the whole thing this is my result:

 

id - username - award1 - award2 - award3 - award4 - award5 - award6

1 - xoligy - 3 -0 -1 - 0- 1 - 1

 

this would be yours

id - award -  count (id is username in this)

1 1 3

1 2 1

1 5 1

1 6 1

 

to me mine seems simpler and easier to understand lol

[Mchl]

Easier to understand? Sure

Simpler? Maybe

More compact? No. (though it may seem so at first)

Easier to add new awards in the future? Hell no!

 

It's up to you really. If you don't expect any new awards to be added later, then your approach will work fine. If on the other hand, there will be more awards added later, you'll have hell of a time doing it.

 

 

[xoligy]

It wouldnt be too hard to change in the future if i did decide to add more awards, but for now i think it will suite my needs thanks anyhow mchl