1

I've uploaded an image of my database design below. This is the first database I have designed and I was hoping that I could get some input into whether or not it is set up correctly or if I just have no idea what I'm doing! I am using MySQL. It is for an rpg game. Thanks so much for your time.

Overview of what it should do:

  • Each user should be able to own multiple characters,
  • But each character should be owned by only one user
  • Each Character should have only one location
  • But many Characters can exist in one location
  • Each character should own one container (inventory)
  • But each container should only belong to one character/NPC (a container cannot be owned by both a character and an NPC)
  • Each container may or may not have only one location
  • But one location can have many containers inside of it

  • Each item/weapon/armor should either exist inside of one container or have one location

  • But one container or location can have many item/weapon/armor

  • Each NPC should have one location

  • But one location can have many NPC

enter image description here

1

Each user should be able to own multiple characters

But each character should be owned by only one user

Then there is no need for user_character. A foreign key column in character would suffice.

I'd suggest you roll character and npc into the one table, perhaps with a flag to distinguish. This will save a lot of duplication and UNION statements later.

Combine all the item/ weapon/ armour into a single item table, perhaps with a foreign key from an item_type table. This will remove a lot of complication, too.

  • Yes. Note that NPC is Non Player CHARACTER - so you can say a NPC is a character with... no player ;) This will make a lot of things easier - also down the road with logic. You POSSIBLY could do the same with location and container. – TomTom Feb 27 '15 at 10:49
  • thanks for the advice! those sound like great ideas to implement. It is a bit over complicated in it's present state – user3044394 Feb 28 '15 at 1:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.