type=text in mysql for seralized data is teh best? or individual columns

[133794m3r]

I'm asking b/c right now i'm currently setting up my db for quests.  And what i wanted to do was to be able to do something in a column called "requirements" just put in there some seralized data. Aka Type of Requirment, ID of said requirement, amount of requirement. I'd be of course using the serialize function or something similar.  As i just found out about it today. Then when i "unserialized it" it'd show up to the players hopefully as "kill this many of these" or "go talk to this person" etc. Or should i set up an individual column for each one of those things i talked about? Whilst also taking into fact taht if i did that i'd have to add quite a few columns since i'd have to have 3 for each type of request. Which do you guys feel is the best bet for this type of data?

I understand that there's probably no cut and dry answer when it comes to how to store data. But i'd like to think there's something that'll be better for here.

edit:i've actually decided upon doing two text fields since i don't know how long they'll get some quests might have a bit longer fields than others especially when i start adding in more npcs and such other things. So i've changed it to be. requirements_type and requirements_amount. Both of those will have serialized data. The first one will hold all the requirement types. Then when i have it shown to the players i'll obviously change the integers to actual text. Same with the amounts. Is this the best way to do this? Or is there some better way taht i'm unaware of?

[jannesiera]

I'm not very experienced with programming / db but that seems quite heavy to store everything in a db. Aren't you better of setting up some sort of templating system, so you could create a new template for each quest?

[133794m3r]

Well actually i guess i didn't explain it well enough in my last post.

Basically this is what'll be put into the db. requirement_type which is an int, also amount also an int. So all i'd have is just a series of integers. That would then when a player takes teh quest or looks it at it to see the requirements again. It would then figure out what is required based on those integers. So all i'm goign to be storing is ids.  It's going to be int for type of requirement. another column is there for what the player needs. IE the id of whatever needs to be killed or collected. What it shows would be the name value of that particular item. The third column is just a bunch of integers showing the amount of each thing that needs to be stored.

That's how i'm doing that part. I don't think that's requiring too much storage space for it.

edit: here is teh exact code from my mysql command i'm about to run. So that everyoen can see how i'm going to do it. AS they say a pictures worth a thousand words. I imagine codes worth a thousand also especially in this community.

Code: [Select]

CREATE TABLE quests (
id int NULL AUTO_INCREMENT,
Name varchar(250),
starts_quests int,
given_by_npc int,
level_quest int,
level_required int,
type int,
ending_npc int,
requirements_type text,
collect_or_kill text,
requirements_amount text,
PRIMARY KEY(id)
)
ENGINE=INNODB;

[Marek]

Serializing the data into a TEXT column will work, and is probably the simplest solution. You can safely store large arrays into a TEXT field, but there's a condition: you won't be able to use the data in a WHERE clause. In other words, you won't be able to display only the quests with certain requirements.

Some database pros seem to demonize serializing data, but it's just not that realistic to use  a database to represent complex data structures that aren't relational anyway.

I don't see why not use just a single TEXT column for the entire requirements data. You can have an array of requirements, with each value being an array of two elements: type and amount, for example.

[133794m3r]

Well my main thing was that i had to have what you need to do as in what's required, then there's also requiring what you need, and also how much. So that'd be 3 things of data. And i thought it'd be a bit better like that. And i doubt i'll ever really need to do a "WHERE" clause in my game. As i doubt i'll ever be sitting there like OOOH I NEED TO FIND EVERY QUEST THAT HAS YOU GET AN ITEM etc. etc. Sure they'll be related somewhat but what game doesn't do that?

[jannesiera]

OH I NEED TO FIND EVERY QUEST THAT HAS YOU GET AN ITEM etc. etc.

It sounds like, when your game gets bigger, it's exactly what you are going to need when analyzing and balancing. When you are sure it's not going to get that big anyway, it's nothing to worry about .

[travo]

If you do decide to serialize instead of using separate columns, dont make my mistake, dont use serialize()

Find a nice json library (dont use the json library with php5, its crap and doesnt work properly imo) and use that. You will be able to read it without having to parse it, and you will be thankful.

However I do think yu shouldnt use text fields. To be honest Im not sure aout the overhead of haing a new column, but Im sure it will be bigger than the overhead from all the crap needed to seperate values in a serialized string. Not to mention if your games under load, you dont want to be unserialising things.

Is it possible you can limit the needed items to a certain number?

[Periculi]

You could try an EAV table for the requirements.  No serializing, unlimited values per entity set, ability to filter for anything you need easily.

[133794m3r]

well the whole reason i was going to serialize it was b/c i have no idea how may requirements each quest will have. Some may have 1 some may have 4. Some may even be epic chain quests with 15 parts. So to save myself the time of making what would end up being up to 45 columns of data i thought to just serialize it. Also why shouldn't i be using PHP? That's the language i'm doing the entire thing in. I have a great dislike for javascript.

Also the needed items isn't really the problem here. It's the number of requirments. I don't want to make EVERY quest the exact same thing. So some will have 1 requirement, some will have up to 5. It's all about the quest. So i don't see how i could honestly move it down to one value unless i wanted to sit down and plan out every single type of possibility for each quest and then make that it's own value but then that'd be completely pointless as no two quests would be the same and i'd just might as well make each one send in the entire data strings and all.

[Periculi]

EAV is a database structure thingy.  Entity - Attribute - Value.

Here's a model:

id | entity | attribute | value

1 | quest_a | name | 'The A Quest'
2 | quest_a | req_exp | 250


So, here I have an entity called "quest_a" that has 2 attributes, 'name' and 'req_exp', with their associated data.

The database structure is very very simple, but what you can do with it is very very dynamic.  To reconstruct any number of attributes and their values query for the given entity.  You can have an entity with 2 values, as above, sitting in the same table as an entity that has 20, 200, or 2000 values.

Expanding the model could give us:

id | entity | attribute | value

1 | quest_a | name | 'The A Quest'
2 | quest_a | req_exp | 250
3 | quest_b | name | 'The B Quest'
4 | quest_b | req_exp | 450
5 | quest_b | req_key | 'Skeleton Key B'


Some purists may notice that my EAV has an extra column.  

Now you have a table that can be used for any number of requirements, with the same number of columns (4) and you have a number of potential filters that you would not have from the serialized data: you could have dozens of quests in there and retrieve any set of associated data - i.e. all the 'name' values as a list, all the 'req_exp' in ascending order, all the quests that required a certain type requirement, and so on.

[133794m3r]

hmm... that does seem to be a tad bit interesting to say the least. I may attempt to do it. Now then onto something i've yet to find an easy way to do with anything i've seen. How do you set something to a foreign key in mysql? I've yet to see anything talking about it. I've looked over their documentation. But sadly teh mysql people don't give examples like the people at php do. They just show structure etc. etc.

Since using that model i'd probably do it like.

id| entity   | attribute | value
1  (questid)  name        Tutorial quest

then just go down it. Now the thing i'd like to do is to set some foreign keys up so that i can reset the daily quests all at once instead of having to go in and manually changing them.

[Periculi]

That's actually pretty easy:  http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html provides a few good examples, the basic parent/child relation is something like this:

Code: [Select]


CREATE TABLE parent (id INT NOT NULL,
                     PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
                    INDEX par_ind (parent_id),
                    FOREIGN KEY (parent_id) REFERENCES parent(id)
                      ON DELETE CASCADE
) ENGINE=INNODB;



Oh, and an addit - you might try a db structured like this if you to get into some strong normalization with foreign keys:

id | parent_id | entity | attribute | value

[133794m3r]

hmm so then for some of the tables i already have done such as "characters" which is not said to be a "parent" table, should i delete the table then remake it into a "child" table?
ah ok so i can just use them right away there. Just alter my tables to include the "foreign keys". As my quest table won't really use "too many" foreign ids but it might. Since well it's going to be ending up having refernces to various ids in it. but i doubt those will change just the quest id shoudl be used i hope. Since that one's going to be updated via all players accounts if they took the quest. What i'm going to hopefully do is just change the value if they have it from "taken to not taken" via a tinyint(1). Basically that time of the day it's reset to "0" showing that one one's taken it. Then it'll go back to the normal one once a player takes it.

[Periculi]

I suppose an obvious question would be this: How often does your quest data change in regard to itself?  

Player data - whether a player has taken a certain quest is different - is specific player data, much like their name and if they logged in today.  

But, perhaps, your quest requirements and other specifics are fairly static, common to all players, and although they may have different specifics one to the next, the specifics themselves don't change much or very often?  I would imagine you could use something nice and lightweight like XML in that case - if you just want to store a set of static data that gets referenced, XML might be a nice format to look into, php can read in xml nodes very quickly.

[Nox]

I thought it's the other way round, that xml is not really fast and bloated, but feel free to correct me, no problem

@OP
should your table have a variable number of columns you put in into separate table

original table quests: id | name...
auxiliary table quests_data: id | id_quest | key | value

that's example of scheme for 1:N relations

[133794m3r]

Well, my thing was that there is going to be varying number of "requirements". I've yet to actually code my game code which'll refernce all of this data. As i might be doing it "assbackwards" as some say but i'm doing the DB design before i start coding my game.  As far as actual codes goes.

Now then using foreign keys which reference other things. Would it be possible and this is competely from a standpoint of no idea, to be able to have it for example. Let's say a quest has someone to kill some mob with an id of 25. So then in the quest table it has just the "target id" which is 25. Since that id is a foreign key via the monsters table. So then could i using this "reference" system make it so that i can pull the name of the mob at time that i code it? By just basically telling it to find the name value. That is if i use your table thing.

[Periculi]

I don't believe that FKs will help you to aggregate data on a SELECT query.  They are "referential constraints", you are probably wanting some JOIN queries instead.

addit - found a little demo that shows both concepts in action for you:
http://www.sitepoint.com/blogs/2009/03/12/mysql-foreign-keys-quicker-database-development/#

[133794m3r]

well currently i know nigh to anything about mysql save what i'm learningon the go. It's usually how i do these things. Also i seemed to have skipped over JS's reply. Well i'm being completely realistic. It's a browser based game with some graphics not too graphically heavy but has an option to view it with almost no graphics for those on slow connections.

so in complete reality, i dobut it'll ever get "big" like what others are expecting big to be. My main reasoning for this is because at the end of the day it's still not a real time combat game with big company names behind it. So if i SOMEHOW get 5k active players that stick to teh game. I'll consider that to be "big" since afterall this is a very underdeveloped market. Unless you're doing 3d games which requires your players to download stuff ie that thing they're using in fusion fall and other such games like also fallen earth. You have to "DOWNLOAD THE GAME" but you still play it in the browser. I don't understand how they can get away with calling themselves browser games are you're downloading a client. So as long as those who can use that and get away with calling themselves "browser games" i doubt any browser game will ever get to be above 200k active players. 

The game i'm currently developing has no turns, or energy like system. But the battles are turn based. With the option for players to leave it up to the AI system to handle based upon their inputs via the options menu like thing. Basically how probable it is that something will happen is what they're selecting. So with it only having that kind of battle system i doubt i'll get a ton of people to play due to it not being real time combat. Also the fact that it's not complete graphically pimped out. It's also going to lose people. So all in all if 3 years down the road after the game's launched and i maintain a stable player base of ~1-2k players i'll be happy. I doubt it'll happen but atleast i tried.

[Chris]

I would do it other way round. Sit down and write 20 example quests on paper. The odds are, there will be less types of quests you expected. I would say below 10... If this would be true in your case, you can greatly simplify the database compared to your initial expectations.
At least that's what happens in my case most of the time