Database Design Mysql

[DazZzL]

Im working on my database model atm, and have a question about db design in Mysql;

I will be using lots and lots and lots of stats in my game. The core model for example a hero in game, will consist of relations to race, class, equipedarmor, spells, etc. etc. etc. Check below to get the idea (kind normal for a game I guess... =).

hero
heroId
raceId
classId
heroField2
heroField3
heroField4
...

race
raceId
raceField2
raceField3
raceField4
...

class
classId
classField2
classField3
classField4
...

armor
armorId
slotId
armorstat1
armorstat2
...

equipedarmor
heroId
armorId

etc. etc. etc.

What Im wondering about is...

If im gonna show the stats page for my hero, im gonna be querying like 15 tables. Im wondering what would be smarter to do, my feeling says (and thats how Im used to do it), make the data itself less redundant instead of adding more field to some tables.

For example. I could add the race info to my Hero itself, or assign 8 fields to armorslots, and reduce the number of tables used. But by doing so Ill be adding much more data into the database itself.

Seems like a no brainer..., but Im a little worries about the complex queries (not making them, but performance wise).

Should I assign foreign keys? So far I just made an Id field for every table and been using foreign keys, but not assigned them (just added the Id fields to other tables).

Or shouldn't I worry as long as Im using the primary keys in my queries? Getting data out of 15 tables in 1 query, would that be a smart thing to do? Spread it over more queries?

Could somebody give me a few tips? Im trying to get the best performance, and be as less redundant as I can be.


Hope this makes some sense=) English is not my native language, so excuse me if it doesn't=)

[ConceptDestiny]

You can have the class, race, armour, equipped armour in one table. i.e.

tables:
Character
Items

In the items table, as you equip/unequip items, it can update the stats in the character table, however when in combat or such, you only need to query one table (character) for the character stats.

[DazZzL]

Thanks for the reply.

The point is. Race will have a table also with like 15 fields.

For example

Race Id
Race Name
Race Description
Race XP Modifier
Race Sta Bonus
Race Cha Bonus
Race Dex Bonus
Race Int Bonus
Race Str Bonus
Race Skill 1
Race Skill 2
Race Spell 1
Race Spell 2

Where Race Skill (and Spell) will for example have a table wil dozens of skills also and Ill just store the skill Id in the race table. (Same for lots of other fields in Hero).

Thats why I thought just storing the race Id in the hero table, and define 15 races in the race table. Instead of having 500 heros which all those 15 fields filled (or might even be 200 fields for race, class, spell, armor, etc.) , Id have 500 heros with 1 id for each table.

Mmm any more info would be much appreciated =)

[ConceptDestiny]

I don't see a problem keeping your race data in a different table than your main user/character one.

[Jackolantern]

Really it should not be in one table. "Joins" are nothing to MySQL, and they are what you will use to take the player ID and look up all relevant stats in however many tables you want. You can do it all in one query. It could end up looking something like this if you needed data from 4 tables named "players", "stats", "equip", and "items":

Code: Select all

SELECT players.ID, stats.health, stats.mana, stats.str, stats.dex, stats.agi, stats.end, equip.shoulders, equip.legs, equip.arms, equip.chest, items.item
FROM players, stats, equip, items
WHERE players.playerID = '$playerID'
AND players.playerID = stats.playerID
AND stats.playerID = equip.playerID
AND equip.playerID = items.playerID

Keep in mind it has been a while since I have written one of these, but I think that should have it. Someone correct if my brain has fogged out, as I have not slept much lately If I had a nicely setup database to test it on, I would.

Anyway, using this kind of query, you can have a very simple Players table in your db that really just houses the player ID. Then each major block of data about the player can be housed in its own database table, such as stats, equipment, items, etc. This way you can have just one entry for things that are supposed to only have one, such as stats (although this would have to be enforced in your code), and you could have pretty much an unlimited amount of entries for other types, such as equipment and items. That makes your database much more flexible and gives it tons of room to grow. Putting everything in one table will eventually break your database, leading you to have to throw it out because you simply cannot add what you need to. And some single-table designs are basically impossible, or lead to massive amounts of blank space in the table (such as having "one-to-one" relationships, such as Stats, in the same table as "one-to-many" relationships, such as items). Hope this helps!

[Torniquet]

check http://youtu.be/dnM8mcqpfNE and http://youtu.be/NOiCr-LYWmY out.

The first is basics of mysql (i am sure that you are familiar with it) but the second is a continuation and looks at joins and how to use them.

Like jack said, Joins will be the way to go with what you want to do .

[DazZzL]

Awesome, thanks for your suggestions.

Ill stay on this track then =)

[Xaleph]

Well, database model looks good. It`s not even a question of JOINs. It has more to do with the design itself. When I started out, I had the same ambitions, but for stats I used this:

statistics

id INT,
name VARCHAR,
strength INT,
defense INT,
magic INT,
et cetera

Now, what I did is, I created a "template" for the each race, a template for each profession et cetera, and each individual hero also has it`s own upgradeable entity. If you want to have specifics for a profession, like when you reach level xx, you can load a new template for that level et cetera.

Same goes for armor, each different armor type had it`s own template, that way you solidify stats in that table. It does, however, create a lot of overhead, since not every profession uses magic or what have you.

Anyway, hope it helps. Oh and like ID`s. race Skill 1 should be RaceSkill1_ID, but you knew that already.

[Jackolantern]

Yes, IDs are a must for everything, even for basic many-to-many tables that are only keeping track of matches between two different tables. That keeps strings out of everything except "master tables" (what I tend to call them, as I don't know of any specific term) which serve to define what each ID represents.

And when in doubt, make it a new table! Everything that gets bunched up on the same table is more chances you won't be able to match the data you need in a query. Matching data within the same table but not on the same row is error-prone, CPU intensive, and overly complex. The MySQL engine has to make "iterative Cartesian tables" to do that (a fancy shmancy way of saying it has to make a virtual table for every possible combination of elements). Of course there is nothing wrong with keeping all of the base stats in the same table, but if you have secondary stats that are figured from a combination of base stats, they should go in another table. Remember that MySQL favors many smaller, less complex, less whitespace tables rather than large, complex, whitespace-laden tables.

[hallsofvallhalla]

I use join in the cavern crawl videos when pulling maps. Check those out as well.