CODEWITHSUNDEEP
mysqldatabasedatabase-designnormalizationtable-structure
ccdavies
ccdavies

Reputation: 1606

Is it best to use multiple rows, or arrays to structure data

I am building a competition website and need to store multiple rounds of judges scoring for each entry.

The 1st and 2nd rounds will have a score of either 0 or 1. If half of the judges give 0, it doesn't go through to the next round.

Those that make it through to round 3 will have a score of 1 - 10. The top x amount (combined judges scores) will move through to the final round. The same approach will go for round 4, but show the winner etc.

I want to make sure I set up the table structure as best as possible to help with sorting the above data.

Is it better to have:

A single row per entry, with the judges scores for the entry stored in a single column, within an array

array(
    array ( judge_id => '1', round_1 => '', round_2 => '', round_3 => '', round_4 => '' ),
    array ( judge_id => '2', round_1 => '', round_2 => '', round_3 => '', round_4 => '' ),
    array ( judge_id => '3', round_1 => '', round_2 => '', round_3 => '', round_4 => '' ),
    array ( judge_id => '4', round_1 => '', round_2 => '', round_3 => '', round_4 => '' )
)

OR

A row per entry, per judge, with columns for 'round_1', 'round_2', 'round_3' and 'round_4'

Which would make the most sense to store, then calculate scoring later?

Upvotes: 2

Views: 113

Answers (1)

Somnath Muluk
Somnath Muluk

Reputation: 57656

You should maintain row per entry for each round.

id| judge_id | round_id | score | created_at | updated_at

Advantages:

  1. You can have any number of rounds 4 or 10. You will not need to change table structure.
  2. You can place additional information of each round in row tuple.
  3. You are making more normalised data so it will be helpful for adding more feature related to rounds.

Upvotes: 2

Related Questions