Page 1 of 1
Simple database table structure
Posted: Tue Mar 10, 2009 3:18 pm
by papa
Hi,
I'm doing a small website for me and a couple of friends where we can bet on UFC events (
http://www.ufc.com)
Trying to keep it very simple and not go overboard as usual.
So. You first add an event, selecting date. Then you add fights to that event selecting players already put in the database. Structure looking like this so far:
Code: Select all
event
id | name | event_date | created_by | create_date
fighter
id | name | url | wins | losses | noc | created_by | create_date
fight
event_id | fighter1_id | fighter2_id | result | time |
w = win
l = loss
tko = technical knock out
noc = no contest
s = submission
I need some input regarding the fight table. What would be a smart solution? The table must store the result so I then can see if the players have done a correct bet or not. And if there is a knock out or a submission I need to store the time when the fight ended.
So should result be 0 for win, 1 for loss, 2 to for tko and 3 for submission and how would it determine which fighter won when you retrieve the data?
Please help me Obi Wan.
Re: Simple database table structure
Posted: Wed Mar 11, 2009 2:45 am
by jaoudestudios
Why not use a enum for the fight result?
So it could read whatever you wanted. i.e. knock out, win etc...
Hmm if it is a win how to determine who won, good question, because in that theory you would never need a loss option in the results column because you would only store a win and who it was - if that makes sense!?!?
Actually thinking about it I would slightly make it more complicated (I have reasons why). Because in the future if you wanted fighters statistics then you could only bring out their wins easily, but to bring out their losses would be more difficult as you would see where they dont appear as a win - or something to that effect.
Why not create a result table that joins onto the fight table (foreign key -> event-id). That way in the results table you would have 2 entries for each event, 1 entry for each fighter. This way you can bring out loads of statistics on each fighter easily in the future.
i.e.
fight
event_id | fighter1_id | fighter2_id | time
result
id | event_id | fighter_id | result
Re: Simple database table structure
Posted: Wed Mar 11, 2009 3:17 am
by papa
Thanks J!
I'll see what I can come up with!
Edit:
Say that I have a table called result. Would it be wise to store it as follows:
result
even_id | fighter_id | result | time | round
1 | 10 | w |
1 | 15 | l |
10 | 56 | s | 3:33 | 2
10 | 32 | l |
w,s,ko = win
l = loss
So if the win is by s or ko it also stores the time when the fight ended?
Also remove the stats from the fighter table like so:
fighter
id | name url | created_by | create_date
Re: Simple database table structure
Posted: Wed Mar 11, 2009 3:34 am
by jaoudestudios
The only downside to storing the time and round in the result table, is the information will be stored twice - which is a bad design. Where as if you stored it in the fight (event) table then it is only stored once - but not a major issue if duplicated as it will never be updated, just a bit messy.
Re: Simple database table structure
Posted: Wed Mar 11, 2009 3:42 am
by papa
True, I was thinking of changing the fight table like so:
fight
event_id fighter1_id fighter2_id
Code: Select all
event
id | name | url | event_date | created_by | create_date
fighter
id | name | url | created_by | create_date
fight
event_id | fighter1_id | fighter2_id
result
event_id | fighter_id | result | time | round
Re: Simple database table structure
Posted: Wed Mar 11, 2009 3:47 am
by jaoudestudios
Looks good. If I was you I would still move [time|round] into the fight table from the result table.
[time|round] belong to the fight where the [result] belows to the fighter.
i.e.
Code: Select all
fight
event_id | fighter1_id | fighter2_id | time | round
result
event_id | fighter_id | result
Re: Simple database table structure
Posted: Wed Mar 11, 2009 3:56 am
by papa
Ok nice, I'll try that!
gracias mucho!