Datbase Design

[BlackScorp]

Hi all,

iam actually Making some database design. Since i didnt planed much stuffs ingame, i have to plan it flexible to change stuffs fast.

here is my ERD
http://img193.imageshack.us/img193/2295/erdp.png

now there are some questions.
1) What would be better for Character Attributes? Store them to character table? or make an attribute table and connect it over relations?
2) What about item attributes? they could change depend on item . for example, an Armor dont have damage or an potion dont have attack power..
3) i want to make a logic for equipment. so what item can be inserted in which slot, how to realize it?

maybe you have some improvements or tipps for me?

Best regards BlackScorp

[Chris]

Argh!! My eyes, my eyes! If you were trying to make a proper relational database schema to get a good grade in school, you scored full points. If you tried to kill your server and invent the most complex database schama possible you also scored full points

I don't want to dvell into this too much, since everyone that will post below will say you did it right and I'm wrong. So I will just say that I don't agree with those people that will post below

Download any free script and check how they did it. It's always much simplier that what you posted.

[Copy112]

The database structure seems quite well designed, the ER-diagram could parhaps be cleaned up, you usually don't display cr_role_users (I think).

Can the characters be on multiple maps at the same time? The ERD says so but it seems quite weird .

1. If you plan on having a few attributes, such as str, agi I would store them in the character table. If you plan on having tons, another table

2. Items modifying your stats is a quite complex problem, the solution I probably would go with is making item classes, such as potions, armor, weapons and handle them differently in php. Possible jsonify or serialize the data, for example potions, { hp: 100 } or { mana: 10 }.

3. Well, look at 2, and for weapons you could have something like { type: "sword", dmg: 10 }.

There are many more ways to do it, but it should give you some ideas on how to do it

[BlackScorp]

Download any free script and check how they did it. It's always much simplier that what you posted.

i didnt told that i want to get a simple result. i want to have a good result. others makes everythink over PHP and with much hardcoded if else switch case structure.. its simpler.. but it is not what i want.

Can the characters be on multiple maps at the same time? The ERD says so but it seems quite weird .

1. If you plan on having a few attributes, such as str, agi I would store them in the character table. If you plan on having tons, another table

no. User has n Characters, Character has n Attributes and n items. Item has n Attributes.All Characters are on Maps each Map has n Tiles.. but youre right, i gues char->map must me 1:n so map_id, x,y must be foreign keys.. in Chars Table.. will edit it.

2. Items modifying your stats is a quite complex problem, the solution I probably would go with is making item classes, such as potions, armor, weapons and handle them differently in php. Possible jsonify or serialize the data, for example potions, { hp: 100 } or { mana: 10 }.

another way could be store items and item stats as json file and write just the name of item into database, but it means i have to check if item is really exists

3. Well, look at 2, and for weapons you could have something like { type: "sword", dmg: 10 }.
There are many more ways to do it, but it should give you some ideas on how to do it

hm.. serialized arrays, i dont really like this idea.

i thought about something like, i add new item to user_items table, and in my view my char has another stats, depends on stats of the items , the char is wearing.. i want to make a huge query, store it in view and use the view as table on php side.. that was/is the basic idea

ah and thx for your comments;)

best regards Blackscorp

[Nox]

Always a joy having to agree with Chris a bit performance is a thing one should consider

If you want to have it flexible and in DB then probably the EAV (what you have) is the solution.

As for the items, there is also a possibility of OOP-like composition where you'd have common attributes in one table and the rest in specific table... or well, just have a different table for each item type... would have good performance, good readability, low complexity, just a bit worse maintainability.

This is a wonderful resource, found a lot of stuff I haven't heard of (EAV is on slide 16)
http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back

Plus you might look into "mysql views"

If you'd decide to go with current design, I suggest you also create an AttributeModifiers table to take a full advantage of the solution considering the notable cost paid

[Chris]

Download any free script and check how they did it. It's always much simplier that what you posted.

i didnt told that i want to get a simple result. i want to have a good result. others makes everythink over PHP and with much hardcoded if else switch case structure.. its simpler.. but it is not what i want.

Well, I always assumed simple = the best, unless there is an important reason to do it non simple way. But as a rule of thumb the simpliest solution that work as I want is the ideal one. Somehow, I always assumed everyone thinks this way

If this was a singleplayer game I would say "OK, he has his coding style, maybe he likes it that way so maybe it is best for him". But it is massive multiplayer. Whatever coding time you save because of flexibility or personal preference here you will almost surly pay double during code optimization step...
I guess there are two kind of coders, those who got their server crashed because of performance issues and those who will

At least get rid of attributes table (you can keep all these in character table) and change VARCHAR to CHAR in tables where you have only 1 text variable (this way the table will be fixed width, much faster row finding possible).

Always a joy having to agree with Chris a bit

Nox, I would not be so happy... It is a bad sign to agree with me and you know it
(at least you added the "a bit" part )

[BlackScorp]

well chris, what if later in your game forums someone suggest to add new kind of ability or new kind of item.. so you have to go into the code, find your if else switch case stuff and add new lines. i want just to add new stuff to table and display it in my view.

nvm.. after couple JeamBeam Cola mixes i made a new one.. the equipment table looks kinda strange, maybe it is possible to optimize it?



Best regards BlackScorp

[Chris]

well chris, what if later in your game forums someone suggest to add new kind of ability or new kind of item.. so you have to go into the code

The fundamental difference between making business software and a browser game is that you have no client that can force you to change requirements in the middle You rarely *have to* code something You can reject suggestions as you feel like it. I love that aspect of making games the best

And the items+inventory table are OK, that's how everyone does it. I meant only attributes and map tables.

Anyway, in the 6 years of running a game I never wished that I coded something more flexible. Althrough, it does not necessarily apply to everyone, it might be just me. Anyway, I think you worry too much about flexibility and user suggestions.

nvm.. after couple JeamBeam Cola mixes i made a new one.. the equipment table looks kinda strange, maybe it is possible to optimize it?

I will repeat myself, do yourself a favour and check some source codes, it will be instantly clear why they did it that way.
You don't need "equipments" table at all. You have all that data (slots) already stored in the "inventory" table.
You also don't need "inventory_id in characters table, inventory tables that belong to that player should just have the same id as character (and I would change id to owner_id, because it's quite confusing what is what, plus you might need the row id for other reasons later).

[saljutin]

one rule of all rules - make it simple and fast
queries make your server/game slow - too many queries or not optimized queries

I agree with Chris with downloading some game just to get basic idea but not to COPY everything.
in one free script I have seen for loop which has read and update queries - it was some sort of mafia script where you trained to raise your skills, and you used turns/money in some field...so 100 turns it is for i=1 to 100 loop which makes 200 queries! they could solve that with 2 queries and more math

[BlackScorp]

in one free script I have seen for loop which has read and update queries - it was some sort of mafia script where you trained to raise your skills, and you used turns/money in some field...so 100 turns it is for i=1 to 100 loop which makes 200 queries! they could solve that with 2 queries and more math

dont worry iam using Kohanas ORM it has Joins, Prepared Statements, Cached Results, so its not acutally a Problem.