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.

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.

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!

Rpg might be a better prefix or whatever you will call this game.

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 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.

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.

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?

Post updated

Not put anything in the monster drops table for that item.

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.

Currently working on the design part of the site, will get back to you soon!

I updated my design a bit...

What do you think?

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

Bump
123456

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.

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!