Crazy MySQL conundrum (to me at least)

Databases
1

Hi there,

I have 16 participants. Each participant has it’s own “row” in the database, including the columns place1, place2, place3, …, all the way to place16. These “place” columns in the rows indiciate how many times a participant has been voted for that respective place. For example, if place10 column = “1337” for a row, that particular participate was voted 1337 times to win 10th place in this competition.

Now the hard part is syndicating all of these data into meaningful results.

What I am trying to do is: how can I display a list of results for all 16 people where the most votes for place1 is put in the first row of the result set, the most votes for place2 is put in the second row of the result set, the most votes for place3 is put in the third row of the result set, etc… is this possible? I don’t want to do this with 16 queries to find out…

Any help is mightily appreciated. Thank you in advanced. :cool:

2

it would be ~so~ much easier if you could split 16 place columns off into 16 rows of a related one-to-many table (this is called first normal form, by the way)

both participant_id and place_number would form the composite primary key for this second table

3

Since the system has already been built, it would be mighty difficult to change the database structure and code that is integrated with it.

Are there any ways around this?

4

yes, there is, but it’s far too ugly and complex for me, sorry

could you extract the data into separate throwaway-after-the-report-is-done tables?

5

If I had a dollar for every time I heard/read this I swear Bill Gates would be looking in his rear view mirror and I’d be coming along nicely!

6

yeah, it’s sad

not picking on this particular example, but how does a system for data storage get built ~before~ thought is given to how the data will be extracted?

7

The schema is an obvious deviation from proper design principles. Its not even about extracting data but lack of education/understanding.

8

Second “best” solution in this case : extract all data and elaborate it with PHP (or whatever language you’re using).

9

Yup, I haven’t had my database courses in school just yet…

10

doesn’t prevent you from looking up database normalization though does it? :slight_smile: