Database/Table Questions [SOLVED]

[RogueTomboy]

*waves hello to everyone*

I have some questions on Databases/Tables.

Is it better to have more tables for things in your database or fewer tables with more rows?

Example:

I have multiple towns/cities for my game. Each city has their own vendors/trainers/inns/hospitals/etc. Is it better to make tables for each town/city with all their own stuff in them OR is it better have separate tables for each thing? ...or is that more confusing? *thinks*

Okay lets try this again.

Town 1 has a food vendor, drink vendor, and armor vendor.
Town 2 has a food vendor, drink vendor, and trinket vendor.

Is it better to do a table labeled "Town 1" and "Town 2" with all their vendors in them OR is it better to make a table called "Vendors" then in that table break it down by types of vendors followed by location?

I hope that makes sense...it does in my head lol.

[Torniquet]

http://indie-resource.com/forums/viewto ... =71&t=3293

your answer should lie there.

[RogueTomboy]

Okay so Jack's example was:

Master_Item table with 4 cols
1.inventory ID
2. item ID
3. Player ID
4. number of items

So for me, following that example, I should think about my vendors as in what they are selling so that I could break mine out like this?

Master_Item table -
1. inventory ID
2. item ID
3. vendor
4. town
5. Player ID
6. number of items

Or did I just make it more complicated?

[Xaleph]

Well regarding your question, the awnser is no. You should not create 2 tables for towns. Town 1 and 2 are still towns, so logically speaking, they should both become a row in town. However, every town may or may not have different types of stores. This depends.

Coming to the next thing: what are characteristics of a town? One might say the shops in that town, true, to some extend. However, the shops do not define a town. Characteristics of a town are IE. the name, the mayor, the dominant race living there, etc. Now, the shops. Shops can be a singular instance, or multiple instances of the same shop. Look at Pokemon, every town has a med center. Is a town unique because of the med center? If your awnser was no, you are right

So if you have different types of shops, you can safely say, a town does not care what shops are in there. On the other hand, a shop could care in what town they are in.

Ok, so in your database model it should look like this:

Tables:
Town
Shops

In your town rows there should be NO reference to a shop, whatsoever. Like we just figured out, it doesn`t care. However, in your SHOPS table, there should be a reference to the town. Because the shop needs a crowd right? So, you have a town_id for every town out there. If you store that same town_id in the shops rows for every shop in that town, you can now get all the shops in that town.

[RogueTomboy]

Thanks for the reply!

For the game (so far) I will have Cities and Towns. Cities will pretty much have the same things in them but with one or two differences (certain shops, certain trainers, certain etc). The towns will all be generic.

Most shops in the cities/towns will sell the same things except for those few rare vendors or 'limited' items.

I was wanting to do my database correctly so that query time wasn't 10mins long. I do want to be efficient

[Xaleph]

If you want to make it efficient, you will want to structure your database the good way. Look at relations. What do cities and towns have in common? What are the differences? Maybe follow a tutorial on good database design. It`s tough to get it right, the goal is to limit the tables, yet make it as flexible as possible.