1

I'm starting to build my application database, and I'm really new to the this world, so I have a lot of doubts about my ERD and if its entity types and relationships are ok.

This is an application that calculates how much me and my friends profit in a video game and saves all the data so we can check it later. I'll explain it a bit so you can understand the ERD.

We want to save this data about every Hunt:

  • Where the HUNT was (RESPAWN)
  • How many people we were (1 to 4)
  • The date of the HUNT
  • How much everyone WASTE (Knight, Druid, Paladin, Sorcerer and the Total)
  • The loot value
  • The HUNT balance
  • How many profit we did (each one of us)
  • The amount of money everyone have to recieve (paying waste + profit -> TRANSFER)
  • And if the HUNT is already paid or not

I'm already doing all the math and saving this data in a .txt file for the moment, but I want to take the next step and build a decent database, and to make sure the following diagram is ok:

My ERD

Are some relationships wrong? Do you think I should add more entity types, properties, etc.?

-1

A question I have is what is the significance of the _EK/_ED columns? I can assume they are separate columns for each person in your party? If that is so, this design would have a lot of NULL values for whenever there isn't 4 people in a hunt, and will outright prevent accounting for a 5 person hunt. I hesitate to suggest a more flexible design until I can better understand what exactly each column is for. I would be happy to expand on what you have if you could post some sample data, such as from a hunt or two, along with a quick explanation of where the information is coming from.

I would also review the datatypes chosen. SMALLINT is fine for key id fields, but it would be best to choose a DATE or DATETIME for your hunt date, instead of varchar. The Paid column has two issues that I see. Including a question mark is not encouraged, instead keep it to alpha-numeric and maybe underscores. If the only two options for that field are Yes/No, then it can be a bit datatype. That way you only store 0 for no, and 1 for yes. That saves a lot of space compared to varchars, and prevents you from having "Yes","YES", and "yes", which are all seen as different depending on your chosen software.

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.