MySQL problems

Correcting bad db design?

[Cher]

example:
I have a db that is mainly made up of 3 tables; one table holds some id's and a name, table 2 holds data about comparisons, and table 3 holds all the data about a particular name. sort of like...

table 1:
1, 2, rock
3, 4, paper
5, 6, scissors

table 2:
1, 2, tie
1, 4, win
1, 6, loose
3, 2, loose
3, 4, tie
3, 6, win
(etc)

table 3:
tie, try again!
win, good job
loose, oops!

The page takes an instance out of the firstid and secondid in table1, compares it for a row match in table 2, then matches the 3rd column in table 2 to the one in table 3 and displays it (yaa you win - good job, etc.). It works fine for small numbers of entries in table 1, but now I'm coming across an instance where there will be 24 rows in table 1 and I dont even want to think about the 576 'comparison' rows need to be created into a table 2.

Is there another way of handling this than what I've set up? I can't imagine anyone with a good background with mysql would sit there and insert all the data into a massive 'table 2' that has 24 rows in table1.

[coopster]

Welcome to WebmasterWorld, Cher!

I look at this from a human logic or decision-making process. How would you ever develop anything that is intelligent enough to state that the next entry is win, lose or tie to the previous entries? Only you can make that decision, correct? Unless there is some form of top-down hierarchy, etc. Can you offer any more insight as to exactly what you are attempting to accomplish?

[Cher]

yes, the user would pick two entries from table one and submit for an answer. Then the database does a lookup on table 2 to determine the answer, then pulls the result from table 3.

So if someone was going to choose "rock" and "scissors" they would have id "1" (first choice) and id "6" (for second choice). The script would move to table 2 to look for a row match on 1 and 6, and discover the answer is "loose". Then table 3 would be queried to get the information for the "loose" and display it to the user.

So that's the jist of it basically :) The problem is I have 24 entries that need to be entered into table 1, which would end up with over 500 entries in table 2 (instead of 9 like the rock/paper/scissor example that only had 3 entries in table 1). I was hoping that perhaps someone could tell me there's an easier way and perhaps even eliminate table 2, but it looks like I might end up spending hours putting the data into table 2. bleh...

[DaScribbler]

Try a different approach and make a table this way...

.................... Rock.. Paper... Scissors 
Rock .......... tie....... lose....... win  
Paper ........ win..... tie.......... lose 
Scissors ... lose.... win........ tie 

Now first selection(index) and second selection(field) will give you 2 cross-reference points, and the value in that field will be the result.

[Cher]

.... I love you DaScribbler