CODEWITHSUNDEEP
databaseschema
Nick Wilkins
Nick Wilkins

Reputation: 301

Database schema - normalization or grouped

I've got a quick database schema that holds a group of players and my questions is how best to represent this. The database is for a golf prediction website. Each tournament has a list of groups of golfers that go out in triples, and those triples changes over a series of 3-4 rounds. Users can then predict which golfers will win their triple.

So for instance I've got Tournament A that has 3 rounds in it.

Round 1 has a group of:

Group 1: Round 1: Golfer A, Golfer B, and Golfer C
Group 2: Round 1: Golfer D, Golfer E, Golfer F
Group 3: Round 1: Golfer G, Golfer H, Golfer I
....

Then the next round these get changed around:


Group 1: Round 2: Golfer A, Golfer Y, and Golfer D
Group 2: Round 2: Golfer H, Golfer E, Golfer I
Group 3: Round 2: Golfer C, Golfer Z, Golfer R
...

Etc. The way I'm currently storing this is:

round_groups
------------
id:
competition_id:
round_number:
golfer_1_id:
golfer_2_id:
golfer 3_id:

But should I normalize this into something like:

round_groups
------------
id:
competition_id:
round_number:

round_group_golfers
-------------------
id:
round_group_id
golfer_id

To finish off the context brief, users can predict which goflers are going to win that group, so:

user_prediction
---------------
id:
user_id:
round_group_id:
golfer_id:

That obviously works for both schema choices. I know the advantages here of allowing me to expand to groups of 4 (or even 2) etc, and the normalisation aspect, but is it really necessary / is the first way really naughty? I cant really see a massive downside (other than some automatic joining mechanisms in frameworks) It seems contextually to make more sense - and my code can deal with the relationship joining / merging of golfers etc...

Just making sure I'm not missing something obvious here.

Thanks a lot

Nick

Upvotes: 0

Views: 28

Answers (1)

Ad Fortia
Ad Fortia

Reputation: 333

According to me, the first way is not naughty but less elegant and more rigid to changment. If I have to choose I will choose the second way that's more RDBMS "friendly". I guess you can also remove id from round_group_golfers because (round_group_id, golfer_id) can be a primary key.

Upvotes: 1

Related Questions