Framework for PBBGs? – DB Planning

Good day all.
I don’t know if I mentioned it here, but I am looking into creating a framework for PBBGs using PHP. This is not only to help myself create games but to make it easier others to start to create their own games.

What the first problem you think of when you think of making a framework for games? For me it was databases. We all approach these differently, and will want different info to be stored. After a bit of thinking, the easiest way to do something like this would be to put objects of data into the database and have your code know the schema of the info.
Doing some searching it seems Google beat me to the punch with this idea.

http://en.wikipedia.org/wiki/BigTable

So this gives me the impression its plausible to do what I’m thinking, but is it really efficient? Can it be done is such as way so that each call isn’t grabbing ALL the info related to a player. The goal would be to return a query then to deserialize it. And not to perform string manipulations on it during the query.

Let me give an example of the problem of having all of ones data in a single object.
You have 5 stats, experiences, weapons, armour, magic, a guild, quests, inventory, gold, friends, mail, an attack history…

I think you start to get the point. That’s a large amount of info when you only want your players names and id. I can think of a number of problems for using a large scale solution like bigtable, but is there a way to keep a similar structure on a smaller scale?

To make descent framework for games, the DB needs to be incredibly flexible and this is the only thing that comes to mind when I try to think of a solution.

So for you all DB guru’s out there, what are your thoughts? Can it be done? I will try to put more thought into it over the weekend.

Also, does anyone know of any good database stress testing tools?

23 Comments

  1. Have you ever used Ruby on Rails and ActiveRecord? It does that sort of DB stuff. And the magic of Ruby makes it even "awesomer", in that it reads the table schema and auto generates methods on objects corresponding to those fields. It does all of that in a DB-agnostic way as well, so for a good portion of code, you could write all your database access for MySQL and then have it work for Oracle too. It also generates cool find methods, like find_by_id_and_username_and_rats_killed, assuming you had columns of those names on some table. It also manages associations between tables, such that if you had a user object, and it had a sword, you could do user.sword or something.

    If you're already aware of all of that, please disregard this message.

  2. I didn't know that. I actually know nothing of Ruby on Rails.
    But what I'm trying to do is not have the code read the schema, but to have no schema. Just to have an id row and then all your info serialized into one field so that there is no pre-defined schema that users have to force their elements to fit into.

    Maybe ruby can do this and I misunderstood you. But I know nothing of Ruby ^^

  3. there is currently one other db solution for this -> couchDB, but it's still alpha ( but working ).
    anyway, i think you need to define at least some specification for the framework, cannot be "generic" for everything, so when you think about it you will get some db schema for the basis of the framework, and everything else you can support through "bigtable"

  4. Hey storing serialized objects in PHP is bad, even though in PHP5 you can use spl_autoload to make it automatically include the needed class files when un-serializing a string-based object you really shouldn’t be doing this, say for example a user sells 1 apple you would then need to serialize that users object and store it, which is crazy as serialized objects get big fast.

    A better solution is to simply have a table with 4 columns, for example ID, UserID, VarName and VarValue. This allows you to do some cool things, for example if you want all user data you select on UserID, but if you only want to know how many apples a user has you select based on the UserID and VarName = apples 😀

    Writing a class to handle something like this is really quite easy, if you need a hand I should be able to, seeing as my ability to keep interested in my own PBBG project is pretty much non-existent :S

  5. by making "generic" table where one column is variable name and second is variable value, you are re-creating database system – very slow. also you would have to store everything as text/binary so that would be also size inefficient.
    For smaller things it can be usefull but putting everything in this kind of structure would be horrible.

  6. You can't create something more global that is as fast as something that is more specified. More flexible = slower. Like static and dynamic arrays. Live with that.

  7. I am aware it would be slow. But I wasn't sure how slow, but it seems it would be unusable.

    I like horrains suggestion of 4 four approach. Anyone have any other suggestions on a way to create a DB that would allow for 'generic' shape?

  8. Hey, to make it a little faster you could have a second table which only contains ID's and Variable names, then in the table with 4 columns you have ID, userID, contentsID and VarValue.
    Then you only have one instance of each VarName, and to get this information you could do a simple join 🙂

  9. Hey,
    So since the overall opinion of a Bigtable approach is that it's a bad idea, I' ve been trying to think of a way that allows the user to create a more specific db to their needs, but also allows the framework to easily know the shape of their database. Now, this idea isn't completely planned out in my head, I just thought of it while in the shower, but what about giving the user a interface, kinda like phpmyadmin, and create their db scripts that way.

    But while they are making this, it is also saving the db schema to another file that would allow for the framework to easily handle it.

    Not fully planned out, but pretty much have a file with their tables and variables that could be used in a single looping statement or something of the sort.

  10. The schema could be generated automatically from DB, for example Zend Framework's DB Tables do it.
    all you need to do more is to have really simple interface for changing the columns.

    question is if it would help in the end, since users of this would need to create their db structure anyway and such they would need to understand why/what => they could create it in normal phpmyadmin/whatever.

    But maybe specific game focused options and interface would help, for example working with everything as objects, the "user" would not "change db structure" but "add property to object".
    This would on the other hand need some ORM layer to work well and so it may end up more complicated then before 🙂

  11. This poses an interesting situation. My ultimate goal is to create a system that allows for the user to have the data they want stored in an simplistic manner and give them the tools to easily manage/interact with it.

    I don't think I'm too fond of using an ORM layer. Maybe it would just be easier to have the user create a schema file themselves or I could write/find something to convert their sql to a php script/xml file. I'll need to look into Zend Framework.

    @horrain It's an interesting idea, but how scalable would it be? I'd sure there would be overlaps in the variable names somewhere and if that were the case I'd need to know that variables ID. Or am I missing something?

  12. I think what you are trying to achieve is what the ORM layer does, you want to have control of DB just by simply changing schema.
    Symfony framework does exactly that – you define xml schema for db and it generates classes for using it ( with help of ORM ) so the php classes know exactly what properties they have in db and what type, along with basic relations ( 1:m, 1:1.. ).

    For the ZF, it just gets metadata from DB before first touching the table, and uses it for determining of properties, it works as an Active Record pattern.

    I liked what symfony does with the schema so much that i wrote basic db-xmlschema -> ZF class converter, and even DIA ER model -> db-xmlschema converter, so i can draw db diagram in DIA modeler, convert it to DB xml schema and use it to produce ZF compatible table / row classes.

    In same manner you could have web-based editor of the xmlschema file and that would be then converted to classes.

  13. Reading this leads me to believe your framework will not be used by anyone. If you have so little knowledge of database systems and how they are used, you have no business making a framework involving a database.

    I don't mean to be harsh but generic libraries are among the HARDEST coding challenges there are. They require expertise not only in coding, but extreme expertise in the problem domain and all domains it touches. For PBBGs you need to be an expert in your chosen language, HTML/CSS, database design, simulations coding, etc.

    I applaud your desire to learn. But before you can write generic code, you have screw up writing specific code a few hundred times. My advice to you is to abandon the framework and instead concentrate of making a game using a specific implementation. Finish that, then write a new game using a different implementation. That will give you real world experience with using the database and an intuitive understanding of why your current plan is doomed to failure.

  14. Thanks for the shining optimism.
    If ya don't mind me asking, could you expand on your statement of not know anything about databases? If you don't point out what it is I'm supposedly doing wrong, then I'm bound to repeat it.

  15. Optimism is a great thing and believe me I hate to shine the harsh cold light of day on optimism. But I really believe you need to crawl (among other learning stages) before you can run a marathon.

    I can't explain how to use a database in this little box here. The fact that you had to be convinced not to go with a attribute/value schema shows it was not obvious to you that such a schema is by design slow.

    Finally, didn't I tell you to go out explore and make mistakes? Folks above told you why your schema was wrong. But that doesn't mean you've learned something. You have to make those kinds of blunders to obtain expertise. IOW, I'm telling you to make some mistakes, repeat other blunders. Without that kind of background, you will never understand how to learn from others' mistakes. I'm not saying you need to make every database mistake that has ever been made before. But having a few mistakes under your belt makes it easier to spot mistakes before you make them. (Get it? I'm not sure I've made that make sense.)

    Step back from the problem, is there some other area you know you have expertise in? Have you ever observed how someone new to an area of knowledge thinks they know it and how easy it is on first examination? Only after some immersion do they find out (if they ever do) how much they don't know. There's a quotation I'm going to mangle here: The more you know, the more you know what you don't know. It only through doing that you gain the first "more you know". And at some point that knowledge opens your eyes to all the stuff you still need to learn.

    This was in fact studied by sociologists: http://query.nytimes.com/gst/fullpage.html?sec=he

    I want you to succeed. I'm merely suggesting that you paint a still-life on a small canvas first before tackling the ceiling of Sistene Chapel.

  16. @ jmucchiello Sorry about that. I was kinda put out, but I get where you are coming from. And I understand what you mean totally. I was just being moody lol. And sorry about your post not getting through… The spam filter caught for some reason… It can be pretty stupid sometimes. It for a long while wouldn't let me make comments.

    Thanks to everyone for the suggestions, but I'm thinking I'm gonna buy a few books on the subject and make my head against it till it makes sense, then approach this again. I do still intend on making a framework of sorts. I do have the PHP backend experience to do it. I spend most of my day writing re-usable classes, just in AS3, but the premise is similar.

    Anywho, once I get the ball rolling (hopefully when I get my new comp), I will be posting those files.

    PS: jmucchiello, i figured out what it caught your post. The url you entered as a website was causing it.

  17. I think the idea of a framework is a good one; although, it's a pretty sizable undertaking. Web development frameworks (e.g. Rails or Symfony, already mentioned in this discussion) have been very successful in the last couple of years because they offer the same advantages you are looking for: generators, templates, built-in classes, optional plug-ins, etc. The reason these frameworks came into existence was because certain developers found themselves reusing a lot of code from one site to the next and decided to pack everything together in a easy-to-use, generic format. In fact, some of them were started when the developer basically tore their own code from existing web applications they wrote or contributed to and used it as a base for a framework.

    So while you might think you can anticipate everything a PBBG framework might need, if you've never written a PBBG how can you be sure? I don't mean to repeat what jmucchiello already said, but I just wanted to give it some perspective with the example of web frameworks.

    Personally, I think a PBBG framework would be tricky to develop because it'd be easy to end up with something that results in a bunch of cookie-cutter PBBGs that all seem really similar (like we don't have enough of those already). If that's your intention from the beginning, then go with it, I guess – but if you want a really open system, then you're in for a long journey. I think it probably comes down to how much "game design" is going to go into the framework. Anything that can be modularized is a big plus. Also, you have to identify what your target audience is: coders, or non-coders?

    On a side note:

    ORMs are very nice for a couple of reasons: (a) most developers these days like object-oriented code. Not all _web_ developers are into OOP because so many use PHP, which has a pretty ugly OOP implementation, but every day there are more PHP projects that are making use of OOP; (b) an ORM let's you be database agnostic – it lets you write less SQL and therefore, the result is more portable. This is pretty important for frameworks, because you might want to use MySQL, but someone else might already have a posgres or Oracle server that they'd rather use; (c) the ORMs that are out there are typically widely used and well tested. Sure is nice to have your database connectivity all nice and tidy, taken care of by a library that's rock solid, thanks to large communities and years of testing.

    Of course, an ORM can get you into trouble if you're not careful. If you're into object-oriented programming, though, ORMs are a dream come true. Like I said, I know a lot of PHP coders stay away from OOP. If you've never looked into OOP, I'd highly recommend it; specifically, you (BK) obviously do some OOP in AS3; maybe you should consider investigating other languages (java, python, and ruby all have web frameworks and libraries on the open-source side) and try it out some more. It's not just about re-usable classes, it's so much more.

  18. I will look into that.
    Sorry for the delay. I've been rather busy lately working on a rather large AS3 project. I haven't even had time to work on my own code at home 🙁 Get off work and I'm burnt. Too much thinking 😛

    But the ORM sounds interesting. I will definitely look into it when we finish this project. And I have been thinking about Java a lot lately to test out some thoughts for just little games. From my understanding AS3 is pretty similar to Java so it shouldn't be a huge jump for me to attempt it. And I have a little experience with it to boot.

  19. you really should try out Rails.
    I'm programming in PHP for quite some time, and neve really wanted to try out Ruby/Rails, but then I just tried it and I love it. it's really very simple and fun to program webapps in it.
    ATM I'm programming a PBBG in rails, and it's a lot faster to develop then in PHP

  20. I agree with jmucchiello totally. The development of a framework is not an easy task, and it can only be accomplished by writing similar things many times, then finally you will know what is generic and what is not, what are the common tables and columns you will need and what you will be generalized as meta-data.

    I had an experience to share: I had been writing online shops for quite some times before (not currently though). At the beginning I know only write an online shop for a particular business, and the database design is specific for that shop. After writing a few different shops for different business, I gradually realize how to generalize the database, what columns got repeat many times and can be re-used etc. and I improve my online shop each time I write a new shop. Finally I got a generic online shop which can be used by many different business.

    Therefore, as what jmucchiello said, in order to gain the experience needed to write a framework for PBBG, you need to write several of them, using specific code instead of generic code, and improve it every time. Finally you will reach to the idea of what a framework will be needed.

    This is just my little 2 cents.

  21. I don't think ORMs are good for PBBG programming. Yes, they make the code simpler. But they do not create optimal SQL for data retrieval. Hitting the database is slow and every half second delay in returning the HTML to the user is an increased chance of the player saying "screw it, this game is too slow". I'm sure ORM evangelists will say a properly constructed object set will be plenty speedy. But there's a lot of object interdependence in game that common object structures try to remove. That leads to additional database hits. If you do go with an ORM, do one that has it's own native data storage as and not one that uses a RDBMS for storage.

  22. Most ORMs ( well good ORMs ) let you construct your own queries, so after you have basic prototype done, you can start to optimize db queries, usually that means only overloading method for getting the data you need.

    But i got to agree that sometimes the ORM way seems a bit overcomplicated when using long chain of joined tables one dependent on the previous one.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.