Hi there. I am currently in the midst of a MySQL database project, but I am wondering now, two weeks into the project, if I have a good database design planned or a poor one planned. My project involves four AJAX drop down lists; the first one is independent of the other three, but the following three drop down lists are dependent of the ones in frond of it. By dependent I mean that each option in dorp down lists (1), (2) or (3) will pull different data from the MySQL database and build the next drop down list in the line of four with that data. If that isn't clear enough for anybody, please let me know so I can give you the information you need to help me. Moving on though, my current MySQL database is one long, long table with six columns: pindex, sport, league, team, players, stats. Pindex is the unique column-each row is assigned a different integer. Depending on what sport is chosen, different information from the league column in the table will be chosen. For example, if soccer was choosen, then all of the soccer leagues would be chosen in the column league, or if basketball was chosen in the first drop down list then the second drop down list will be populated with all of the world's basketball leagues. Again, if anything is confusing let me know and I will do my pest to clarify anything up. What I want to know is whether my one-table database design is the best option when I will be updating the database on a daily routine. Or perhaps have I choosen the wrong database design, and instead I should have choosen a relational design. If I should choose a relational database design, how would I set it up? A big thanks for anybody who reads this from top to bottom and gives me some help. Thanks


P.S.
By the way, if the database is a one-table design it will be around 8,000 to 4,000 rows long.

one table design is fine

"better" designs exist but for the volume of rows you're talking about, the extra work may not be worth it

"better" in quotes because it is very subjective

by the way, what you describe is pretty common -- they are called "related selects" and i've seen canned javascripts, coldfusion components, and i'm sure other technologies have invented this same wheel too

google: related selects +ajax

So if you have to change one of the major categories you might have to update thousands of rows. (Updating redundant data is a major reason one would want to normalize a database design.)

If:

a. you're only doing this once or twice a day

and b. it doesn't cause any appreciable downtime

and c. you can guarantee that the update code (which is going to be more complicated) isn't buggy

and d. you can be sure a failed update won't leave the database in an inconsistent state

you should be good to go. To work around c and d, a script could put the DBMS in single-user mode, run a backup, run the updates and then run consistency checks before bringing it online, but this might be overkill for what you're doing.

Distributor,

Are you working on a project that requires you to keep the details of the work confidential? I'm trying to assemble a list of all basketball leagues around the world. Are you in a position were you can share any of the information regarding world basketball leagues currently in your database?

Thanks

g