Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2011
    Posts
    63

    Unanswered: Starting a browser-based RPG, database design

    Hey guys, im new here. Iv been meaning to start this project for a while and i finally did so. My plan is to create a simple browser rpg, and im starting out with the database design. The thing is i havent designed databases in ages, so i tought maybe you guys could look at my initial design (mySQL 5):

    http://img839.imageshack.us/img839/2020/69211909.jpg (dont know how to embed on this forum)

    Some things i wanted to mention:

    -For dbDrops i wanted the primary key to be a combination of both keys, i think this is called a composite key, so i selected both as PK, but one of them showed up as "pf" (primary foreign?). What is this?

    -As far as i understand, an identifying relationship means that no entry in the child database can exist without a corresponding entry in the parent database. So if i want to create an item in dbItems, and make it so it doesnt drop (ie. not link it via dbDrops to a monster), i use a non-identifying relationship, right?

    Thats all for now, thanks for reading and will keep this up-to date with future development.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Had a quick look at your design and here are a few comments:
    • Why start all your tables with "db" - isn't it obvious they are in the database?
    • The many to one relationship between item and drops is wrong in the diagram.
    • Why is it called drops?
    • There is no relationship between characters and items - is this correct?
    • Hopefully the password in Accounts is md5 encoded.
    • Why does Account have a PK of charID - isn't that the PK for Characters?
    • Shouldn't there be a table of locations with the drops linked to that?
    • Should there be some sort of multiplier in the items table - so you could have up to say 20 swords in drops. At the moment you'd have to create a new item for each sword.
    • If everything was linked to a main table of things where it stores the type of object and it's id then you could link any item to anything else using this table.
    • You could also store images here in order to have an image for anything ie an item, a character or a location.


    I haven't played an RPG so I may be way off the mark. If this is actually all about rocket propelled grenades then please disregard the above.

  3. #3
    Join Date
    Jun 2011
    Posts
    63
    Quote Originally Posted by mike_bike_kite View Post
    Had a quick look at your design and here are a few comments:
    • Why start all your tables with "db" - isn't it obvious they are in the database?

      Habit i guess, i tought it was good practice to do this. Will remove them!
    • The many to one relationship between item and drops is wrong in the diagram.

      Yes you are right, because one drop is linked to one item, but one item can be linked to many drops. It should be the other way round, will fix it when i get home.

    • Why is it called drops?

      Because it links the monster to the item. It is called a "drop" in an rpg when a monster drops an item, so i named it like that.
    • There is no relationship between characters and items - is this correct?

      I haven't implemented inventory yet.
    • Hopefully the password in Accounts is md5 encoded.

      Dont know how to do that, but i will look into it.
    • Why does Account have a PK of charID - isn't that the PK for Characters?

      You cant use the same PK but in different tables? CharID is an increment integer so it will always be unique, but i guess so will be the username so i can just use that.
    • Shouldn't there be a table of locations with the drops linked to that?

      Yep, havent got to that part yet.
    • Should there be some sort of multiplier in the items table - so you could have up to say 20 swords in drops. At the moment you'd have to create a new item for each sword.

      I know what you mean and i already thought about this. What i was going to do is put a field in "dbDrops" like "chanceToDrop" which will be a percentage deciding if it will drop. In the actual code i would then do a roll of some sort to see if more than one item will dorp. A field "multipleDrops" which will be a boolean will specify if the item can drop more than once.
    • If everything was linked to a main table of things where it stores the type of object and it's id then you could link any item to anything else using this table.

      Dont i already have this as dbItems?
    • You could also store images here in order to have an image for anything ie an item, a character or a location.

      You can store images in a database? What i was going to was store a link to the image in "monsterImageLoc" and "itemImageLoc", but it would be easier if i could store the image directly in the database.


    I haven't played an RPG so I may be way off the mark. If this is actually all about rocket propelled grenades then please disregard the above.
    Thanks for your feedback mate, hope to hear from you again. In the meantime, can you reccomend me some books for MySQL 5 and ASP.NET?

    THANKS!

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Habit i guess, i tought it was good practice to do this. Will remove them!
    Rpg might be a better prefix or whatever you will call this game.

    I haven't implemented inventory yet.
    I think it would make the coding easier if you could store the location of anything (characters, items, monsters) in one place in the database.

    You cant use the same PK but in different tables? CharID is an increment integer so it will always be unique, but i guess so will be the username so i can just use that.
    You can but I don't see why you'd have a character id as the PK of an account especially if a user can change their character (as implied by your diagram). The user name field would be a far better choice of PK.

    Dont i already have this as dbItems?
    To me it looks like dbItems only stores items and not monsters or characters etc.

    You can store images in a database? What i was going to was store a link to the image in "monsterImageLoc" and "itemImageLoc", but it would be easier if i could store the image directly in the database.
    You can store images in a database but I always just store the location of an image like you were doing in the monster table.

    I don't know much about what you're doing but I'd have a design a bit like this where an image can be stored against anything and relationships between anything can be held ie item "is in" location, monster "is in" location, character "has" item, character "killed" monster etc.
    • RpgItem ( id, name ... )
    • RpgMonster( id, name ... )
    • RpgCharacter( id, name ... )
    • RpgLoc( id, description, northLocId, southLocId, ... )
    • RpgThings( id, type(ie monster), imageUrl, locId ... )
    • RpgRelationships( id1, id2, typeOfRel )
    • RpgRelationshipTypes( typeOfRel, description )
    • RpgAccount( id, charId, name ... )


    I didn't spend long on this so it might be full of holes but it does allow you to expand the system more readily without having to add new tables each time. Personally I'd do it in PHP rather than ASP as MySQL and PHP work quite well together but obviously if you already know ASP then keep with it.

  5. #5
    Join Date
    Jun 2011
    Posts
    63
    Quick update before i go out, will give details later:

    http://img851.imageshack.us/img851/1...basedesign.jpg

    Ok, let me explain the diagram a bit.

    Basically, every location can either spawn a "non-combat adventure" or a "monster". A non-combat adventure is just some text and an item drop, while a monster is a monster fight. Now i linked both of these tables to the table "Items", via 2 other tables "MonsterDrops" and "nonCombatAdventureDrops". The way i want it to be designed is that i want it to be possible to have an item that never drops (ie. it is never referenced in "MonsterDrops" and "nonCombatAdventureDrops"), and make it possible for monsters/non-combat adventures to never drop items (again, is never referenced in "MonsterDrops" and "nonCombatAdventureDrops").

    So, in short:

    -An item can be created but never drops.
    -A monster can be created but never drops.
    -A non-combat adventure can be created but never drops an item.
    -A player character can be created but has an empty inventory.
    -A location can be created that has neither monsters nor non-combat adventures in it (but can have a combination of either/or).

    However, a problem i have now is that i cant properly set the primary key for the "connecting tables" such as "MonsterDrops". The proper key would be a composite key of both the values, but when i set it like that the relationships instanty become identifying, meaning that to create an item i would be forced to also link it to a monster. How can i fix this?

    Another thing i need doing is linking the equipment for the character to the items table. The fields i am talking about are "helmet", "weapon" etc. How would you go about linking all these values to the items table?

    Quote Originally Posted by mike_bike_kite View Post
    Rpg might be a better prefix or whatever you will call this game.

    I removed the prefix alltogether.

    I think it would make the coding easier if you could store the location of anything (characters, items, monsters) in one place in the database.

    Id rather have them in sepereate tables, wouldnt really know how to work with just one table containing everything.

    You can but I don't see why you'd have a character id as the PK of an account especially if a user can change their character (as implied by your diagram). The user name field would be a far better choice of PK.

    An account can only have one char. It is set to 1:1 relationship so why would it imply an account can have multiple characters?

    To me it looks like dbItems only stores items and not monsters or characters etc.

    You can store images in a database but I always just store the location of an image like you were doing in the monster table.

    I don't know much about what you're doing but I'd have a design a bit like this where an image can be stored against anything and relationships between anything can be held ie item "is in" location, monster "is in" location, character "has" item, character "killed" monster etc.
    • RpgItem ( id, name ... )
    • RpgMonster( id, name ... )
    • RpgCharacter( id, name ... )
    • RpgLoc( id, description, northLocId, southLocId, ... )
    • RpgThings( id, type(ie monster), imageUrl, locId ... )
    • RpgRelationships( id1, id2, typeOfRel )
    • RpgRelationshipTypes( typeOfRel, description )
    • RpgAccount( id, charId, name ... )


    I didn't spend long on this so it might be full of holes but it does allow you to expand the system more readily without having to add new tables each time. Personally I'd do it in PHP rather than ASP as MySQL and PHP work quite well together but obviously if you already know ASP then keep with it.
    Last edited by TheGateKeeper; 06-11-11 at 07:04.

  6. #6
    Join Date
    Jun 2011
    Posts
    63
    Post updated

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by TheGateKeeper View Post
    However, a problem i have now is that i cant properly set the primary key for the "connecting tables" such as "MonsterDrops". The proper key would be a composite key of both the values, but when i set it like that the relationships instanty become identifying, meaning that to create an item i would be forced to also link it to a monster. How can i fix this?
    Not put anything in the monster drops table for that item.

    Quote Originally Posted by TheGateKeeper View Post
    Another thing i need doing is linking the equipment for the character to the items table. The fields i am talking about are "helmet", "weapon" etc. How would you go about linking all these values to the items table?
    In my suggested design was a table RpgRelationships( id1, id2, typeOfRel ). It might contain:
    • insert RpgRelationships ( id1, id2, typeOfRel ) values ( 'char_id', 'helmet_id', 'owns' );
    • insert RpgRelationships ( id1, id2, typeOfRel ) values ( 'char_id', 'weapon_id', 'owns' );

    The ids would refer to a table that contained all items, characters, monsters etc and their type.

  8. #8
    Join Date
    Jun 2011
    Posts
    63
    Currently working on the design part of the site, will get back to you soon!

  9. #9
    Join Date
    Jun 2011
    Posts
    63
    I updated my design a bit...

    What do you think?

    http://img825.imageshack.us/img825/1119/69959359.jpg

  10. #10
    Join Date
    Jun 2011
    Posts
    63
    Bump
    123456

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Well, it's certainly getting more complicated. It's obviously difficult for us to comment as we have no idea what half the fields mean or know how RPJs are played. Looking around your extensive diagram and trying to follow the lines between the boxes makes me wonder whether I need to roll a 6 before starting

    Here's some random comments:
    • The fields in Characters look like they should really be in a separate table (ring1, ring2 etc).
    • Having separate tables for everything and not aiming towards a more generic table structure means you have to supply tables for anything you want to do rather than just have data to do this - this is just my opinion here though.
    • It looks as if only characters and monsters can have items. Is this correct? surely a location could have an item.
    • Shouldn't the locations link together? ie if you go north you end up here.
    • Should it be classname in the Characters table?
    • Rather than having a Spawns and a nonCombatSpawns table, why not just have a Spawns table with a typeOf field that allows you to know the type of spawn. This means if you add another type of spawn in future you don't need to alter your design.
    • You have a nonCombatAdventures table but no combatAdventures table.
    • You have lots of type fields (ie typeEquipment) but no lookup tables.
    • I still can't see how certain items might appear more than once without having them duplicated in the items table.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you need to rethink your use of dbforums
    virtually no one has the time to fully understand and therefore comment on your design as it evolves. people can and do comment on specific problems or sticking points, but IU'd be surprised if you could get a critique or validation of your whole data model

    that depends on detailed knowledge of what you are trying to model, what your requirements are, the time available to get to that stage.

    so if you are looking for help on developing your project then you need to find a resource that has that knowledge and time or be prepared to pay for that time and or knowledge.

    I wish you well with your project, but I'd hate for you to come to rely on dbforums to help you design, then find you are not getting the assistance you feel you need and leave here frustrated. we can hep with problems but we cannot act as surrogate developers or analysts
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Jun 2011
    Posts
    63
    Quote Originally Posted by mike_bike_kite View Post
    Well, it's certainly getting more complicated. It's obviously difficult for us to comment as we have no idea what half the fields mean or know how RPJs are played. Looking around your extensive diagram and trying to follow the lines between the boxes makes me wonder whether I need to roll a 6 before starting

    Here's some random comments:
    • The fields in Characters look like they should really be in a separate table (ring1, ring2 etc).

      You are right, i did it like you said
    • Having separate tables for everything and not aiming towards a more generic table structure means you have to supply tables for anything you want to do rather than just have data to do this - this is just my opinion here though.

      Everyone has their way
    • It looks as if only characters and monsters can have items. Is this correct? surely a location could have an item.

      A location can have an item, but it can have it throu the monsters and non-combat adventures it has!
    • Shouldn't the locations link together? ie if you go north you end up here.

      No, that isnt the way it works.
    • Should it be classname in the Characters table?

      I linked it to the class table so it gets its name from there
    • Rather than having a Spawns and a nonCombatSpawns table, why not just have a Spawns table with a typeOf field that allows you to know the type of spawn. This means if you add another type of spawn in future you don't need to alter your design.

      I cant because they are completely different...
    • You have a nonCombatAdventures table but no combatAdventures table.

      Combat adventures is monster spawns
    • You have lots of type fields (ie typeEquipment) but no lookup tables.

      What do you mean by lookup table? TypeEquipment is a bool that will determine whether the item can be equiped.
    • I still can't see how certain items might appear more than once without having them duplicated in the items table.

      I added a chance to drop field
    Quote Originally Posted by healdem View Post
    I think you need to rethink your use of dbforums
    virtually no one has the time to fully understand and therefore comment on your design as it evolves. people can and do comment on specific problems or sticking points, but IU'd be surprised if you could get a critique or validation of your whole data model

    that depends on detailed knowledge of what you are trying to model, what your requirements are, the time available to get to that stage.

    so if you are looking for help on developing your project then you need to find a resource that has that knowledge and time or be prepared to pay for that time and or knowledge.

    I wish you well with your project, but I'd hate for you to come to rely on dbforums to help you design, then find you are not getting the assistance you feel you need and leave here frustrated. we can hep with problems but we cannot act as surrogate developers or analysts
    Im not really relying on it, its more of geting a second opinion. Im not forcing anyone to help me and i am asking specific problems. I just upload the full diagram because it helps with context.


    Ok, so more questions if i may:

    -I created an equipment table but i cant figure out how to link it to the items table. I need to link all the fields in the equipment to the "ItemID".

    The links between the linking tables (such as "Inventory") and the other tables (such as "Items"), do they need to be identifying? I mean, the entries in the linking tables cant exist without an entry in the other tables, but i want the entries in the other tables to be allowed to exist without ever being linked to in the linking tables.

    Here is my design:

    http://img194.imageshack.us/img194/2525/dfgfdgg.jpg

    Thanks and sorry if i came across wrong!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •