Indie-Resource.com

In mysql i have a table....ok in this table it stores the players buildings that he/she has per city...so i wanna know how many fields is too much?

like right now i have so far:

id (AI)
cityid
playerid
tag1
1_level
tag2
2_level
tag3
3_level
tag4
4_level
tag5
5_level
.........


and that keeps going on to fill all the city spots that player can build per city.....so in the end is going to be aprox 34-40 fields...

so im wondering if this is too much?


or should i make seperate tables...like one called villagepositions:

id
userid
cityid
tag1
tag2
tag3
tag4
....and so on

and then have a table for the building levels:

id
userid
cityid
buildingid
tagid


?????????????????????????????????????????

MySQL will be able to handle it no problem until you start to get into at least the tens or hundreds of thousands of records. The real question is: Is this database design flexible enough? Unless you are working towards high-availability (i.e. you know you are going to be handling huge amounts of requests and/or huge amounts of records, meaning hundreds of thousands or more), that is what you always need to think about first when you find you are making a table with more than 10 fields or so. Think about all the possible questions you may need to ask about your data. How many players have a building in a certain area? How about a certain region? If you find any questions that you can't answer with a query, your table likely is too large and you need to break it down into several smaller tables.

Oh ok. I guess the second way is a bit better. Thanks rly appreciate it.

Sorry I could not give an A or B answer, but I could be doing more harm than good without knowing how the rest of your game is set up. If you keep the names of your fields constant between both forms, than you can move forward with development and change it later during testing with only minor code changes if you run into problems with the current schema.