One Row VS. Other Tables

[BASHERS33]

I've really always been unsure how to best determine whether it's best to have one row where some fields have "1,5,34,77" etc... in a field vs. making a new table and having one row for each of those numbers.

I've seen it done both ways really. It sure looks sloppier having to have numbers separated by commas, but it creates more quesries or joins the other way.

I made a sports betting program based on points. Well for each matchup row there are three or four fields which have numbers separated by commas. I have no limit on how many teams could be in a matchup... Well the first number in each of those fields goes alonw with the first in all the others, the second with the second, etc... So as it is now I have to take those out and pull them apart and get them all arranged properly.

So say for some odd reason a matchup may need 100 teams and their team ids may be 11000, 13423, 74632, etc... Well obviously the fields have a limited amount of space to fit what could be long strings.

As it is now I just have low numbers and a few teams in matchups, but who knows what someone else may use. So instead of the three fields i could make three new tables and do a separate row for each team id. Then instead of using PHP to pull strings apart I would be adding three more queries, however.

I am not experienced, so I'm just guessing... but I would guess for normal matchups with 2 teams it would be much more ideal to do it as I have now, but I just think ahead and realize there could be space constraints later on. Adding three queries would surely take a lot longer than PHP I'm using though.

I thought of just keeping it as it is and releasing it and then later on changing it in a new version, but then of course I would have to have a tool change past matchups to the new format and so I'm considering doing this where I don't have that annoyance later on.

[Arctic Fire]

I believe that the separate tables approach is the better approach, especially if you need it that way. Just make sure that your queries are using indexes properly and it can be just as efficient as using PHP to seperate the comma delimited string.

It'll also help if you need to take statistics, such as the most popular team or something.

[BASHERS33]

As far as most popular team and such, I already do have a separate table for the teams. But in the matchups table each row has a column with the team ids anyway. Basically I did that because I also have other columns related to them so I had to try to keep the team ids where it would be easy to put the other comma sepearated strings with the proper team id.

So if I do it this other way I have to make 2 or 3 more tables and then I could just get rid of the teams column totally. It's just all tricky. Because I also made another column which sin't necessary in the matchups table, but it does make it where on SOME pages i didn't have to query the teams table.

I really do think more tables is the best answer. I jst hate that I have to redo it and possibly introduce more bugs, but oh well.

Another concern that has me not satisfied with this program is when setting up a matchup you pick the team from dropdowns which could potentially have thousands of choices. Not good. And I don't know JS, ajax, etc... so I'm stuck with it being sloppy and annoying if I release it now. But on the flip side I may release it anyway and make it better as time goes on. It's for forum sites basically and I keep taking so long to decide it's finished that all these sports tournaments go by and would have been a good time to sell it. Blah.

[Arctic Fire]

As for your ajax/javascript problem, try jquery. And here's a plugin that makes it easy to make an autosuggest feature.

Plugins | jQuery Plugins

And here's a tutorial I found right quick.

Jetlogs.org jQuery: Auto-suggest with keyup() event

[BASHERS33]

ok thanks. I'll check it out. Hopefully auto suggest can be set up to ONLY allow ones in the DB and not a custom one though. Eh or I guess if it allowed custom it would be cool because then it wouldn't be a pain adding new teams. As it is now you have to add teams on a team form before even adding matchups or bracket contests. I thought of adding a feature where a tool will auto add all the college and pro teams from various sports where you don't have to type in all the names though.

This is for Invision's forum software by the way and I couldn't even get their BUILT IN auto suggest feature working properly. lol Thanks for the links though because if I can get something like that done it will be so much nicer.

I don't know if you've done bracket contests before for the NCAA tournament, but those are cool where you click a name and it moved the teams down the bracket wheras me being limited by only knowing PHP made me have to use dropdowns for that too. Guess I better start learning some things soon as I could greatly improve my modifications/apps!

edit: this is release for forum sites though and requiring them to download the jquery files would probably cut down on people buying it.