Simple database table structure

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Simple database table structure

Post 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.
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Simple database table structure

Post 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
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Simple database table structure

Post 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
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Simple database table structure

Post 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.
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Simple database table structure

Post 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
 
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Simple database table structure

Post 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
 
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Simple database table structure

Post by papa »

Ok nice, I'll try that!

gracias mucho!
Post Reply