SQL Databases and table design/organization
- by John McMullen
(NOOB disclaimer)
I'm working on a system (a type of map), that is accessed mostly via 3 fields: ID (auto incremented), X coordinate, and Y coordinate.
As it is right now, i have all data on the map, stored in 1 table. Whenever the map display is loaded it simply queries the database for contents in x and y, and the DB gives the data (other fields in the same entry). If an item on the map is doing something, it has a flag saying its doing something, and then has an ID of the action in another table holding that type of 'actions'.
Essentially, for all map data, its stored in 1 table. All actions of a certain type are stored in their own table.
I'm a noob, and i'm wondering what the most effective/efficient structure for such a design? (a map that has items, and each item has stats/actions).
I'm using PHP atm, using standard SQL queries to get my data.
Should i split up the tables so that there are only x number of entries on a table? (coord range limits)? Should it just keep growing and growing?
There's a lot of queries to the table... so just tryin to see what is best :/
