• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Devaka Cooray
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Knute Snortum
  • Bear Bibeault
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Piet Souris
  • Ganesh Patekar
Bartenders:
  • Frits Walraven
  • Carey Brown
  • Tim Holloway

How to create a good ER diagram about a videogame profits scenario

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ER model and if it's entities and relationships are ok.

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

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 this model is ok, I'm asking for your opinion.

Are some relationships wrong? Do you think I should add more tables?

https://imgur.com/a/7sgdknn
 
Saloon Keeper
Posts: 2617
329
Android Eclipse IDE Angular Framework MySQL Database TypeScript Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It looks like Respawns is effectively just an attribute of Hunts - I don't see what value that the Respawn_Per_Hunt and Respawns tables bring.
 
Gerard Ramon Monte
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Ron McLeod wrote:It looks like Respawns is effectively just an attribute of Hunts - I don't see what value that the Respawn_Per_Hunt and Respawns tables bring.



The thing is that there are several respawns like
* Hero Cave
* Medussa Tower
* Prison -1
* Prison -2
* Prison -3
* Mirror
...etc

In this case it should be just an attribute of Hunts? I thought that but then trying to apply the 4rth normal form I thought I had to make it an independent entity.

And about all the rest? Do you think the relationships are okay being 1:1?

Thank you
 
Saloon Keeper
Posts: 10308
217
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's hard for us to judge your diagram without having any background on the problem domain.

  • What are hunts?
  • What is the goal of a hunt?
  • What are the roles of each participant in a hunt?
  • Is there one of each kind of participant in a hunt, or can there be two paladins, for instance?
  • Why do participants waste loot?
  • What determines how much loot a participant wastes?
  • When are transfers made?
  • Who makes transfers?
  • Why does everyone profit equally when some waste more?
  • What are respawns?
  • Why are respawns significant?

  • I can spot some weirdness in your diagram though.

    First of all, your data isn't normalized. You store much information that can be calculated from other fields. For instance, I imagine that the fields Balance, Paid and Profit can all be calculated from Transfers and Wastes.

    I don't like that you have fixed columns for the different kinds of participants. Apparently there are not always four participants in a hunt. So why then are wastes specified for each type of participant? Can one transfer be made to more than one participant at the same time? What if the application is extended with an extra participant? You would have to make a structural change to your database.

    I think the reason Ron is confused about your Respawn_Per_Hunt table is because you indicated a one-to-one relationship between Respawn_Per_Hunt and Hunts. I think it should probably be a many-to-one relationship from Respawn_Per_Hunt to Hunts. Another reason is that the naming of your tables is confusing. Respawns should be named Locations, and you want a many-to-many relationship between Hunts and Locations, implemented through a junction table called Respawns or SpawnPoints.

    How do you determine what kind of participant somebody is if you only specify the number of people in the hunt? If there are always four or less participants in a hunt, why do you need two bytes to store the number?

    Why are you using FLOAT to store exact values like Loot? Why are you using a VARCHAR to store Date? Why is Paid not a boolean value (assuming you're not going to normalize it away)?
     
    Gerard Ramon Monte
    Greenhorn
    Posts: 5
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    First of all, thanks a lot for your help and time, as you can see I'm really new to this database world and a total noob hahah.

    I want to start learning with a little database, as simple as possible, but doing it as well as I can while keeping it simple, so I'd like to learn how to normalize it.

    Let me explain you a little bit more about how this should work so you can understand.

    I want to build this database as a replacement of the txt file I'm using to store all the data at the moment.
    This is an application that calculate the monney me and my friends earn in a game we play, and where we go, becouse there are different zones like Nighmare Isles, Hero Cave, Medussa Tower, etc...
    It's important to know we don't have acess to any "internal" game data, so everyone just says how much monney they spent and how much we won doing the hunt to calculate all the data.
    I'm already doing all the math, and storing the data in a txt file using "|" to separate the info, and then reading the file to show it in a table, so we can check this data later.

    This is an image of the txt file, and the table I'm talking about: https://imgur.com/a/vILNKMP

    _____________________________________________________________________________
    Now that you know how this should work, let me explain you about the game.

    (EK=Elite Knight - ED=Elder Druid - RP=Royal Paladin - MS=Master Sorcerer) I'm gonna use the abreviate forms.
    Normally it will only be 4 members max and of diferent vocations, becouse you recieve a bonnus % experience for each different class you have, so being more than 4 is not efficient.

    * A HUNT, is when we go as a party to kill monsters and earn experience points and the game monney (gold)
    * To kill monsters, we need resources like mana potions, attack runes, and others wich cost monney (gold), so that would be the WASTE.
    * The monsters drop loot (wich we sell to earn the value of it in gold) so that will be our LOOT value
    * When we finish, the EK (knight) keeps all the loot (Then he sells it to NPC) and pays everyone in monney, this are the TRANSFERS
    * The payment works this way: First, everyone have his WASTE value covered, and then we add to it the profit/each.
    _____________________________________________________________________________
    Let me put you an example of our last hunt (check the image from above to more info, you can see it there)

    In this hunt we went to Nightmare Island (RESPAWN) and we were 3 people (PEOPLE)
    The wastes of everyone in gold value are the following:
    - EK: 146k
    - ED: 269k
    - RP: 31k
    - MS: 0k (Becouse there wasn't a Sorcerer, so the application automatically detects we are 3 people)
    - TOTAL: 446k

    The loot (in gold value) we looted was: 882K
    That makes a balance of 436K positive profit.
    Now, we split that profit in the number of people (3 in this case) and the result is: 145.33k profit/each.
    To calculate the transfers, First, everyone is paid waste, and then we add the profit so:
    - EK: 146k waste + 145.33k profit = 291.33k (EK is who pays everyone so he will not pay this to anyone, just keep it for himself)
    - ED: 269k waste + 145.33k profit = 414.33k (He will get 414.33k from the EK)
    - RP: 31k waste + 145.33k profit = 176.33k (EK will pay him 176,33k)

    At this point, the checks the table I've shown you in the picture above to know how much he has to transfer to everyone.
    And the last column of the table is the column Paid (pagat in my table as it's in spanish): This have 2 possible values yes/no (and if you press the button next to it, the value changes between yes and no)
    _____________________________________________________________________________

    This is all already being calculated, so I would only need to store the data in the database becouse all the math is done before storing it, I only need it to read the values and show them in the table I've shown you.

    It's important to know, me (ED) and my friend (EK) are the only one using the application, just to check all the data, so It's nothing professional, just for hobby.

    My first idea, was to only have 1 table (HUNTS) and store everything as attributes of it, but then I tried to apply the 1-4 Normal forms, as you already said, wrongly hahah.

    Anyway, you make an strong point about this "I don't like that you have fixed columns for the different kinds of participants. Apparently there are not always four participants in a hunt. So why then are wastes specified for each type of participant? Can one transfer be made to more than one participant at the same time? What if the application is extended with an extra participant? You would have to make a structural change to your database."
    Becouse in a future, I pretend to do a "Custom Hunt" feature, were you can say we were 6 people or 7 or whatever (ignoring the vocations) and doing all the math then, but I have no idea how could I do it in the database.

    Hope you can understand better the case now

    Again, thank you a lot for your time and efforts!!
     
    Stephan van Hulst
    Saloon Keeper
    Posts: 10308
    217
    • Likes 1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Yes, it's clear to me, thanks for the explanation.

    Normalization is the process of eliminating all data that can be calculated from other data. In the diagram you've shown, Balance, Profit and Paid are redundant, because they can be calculated from Loot, Wastes and Transfers. TOTAL_Waste is also redundant. Instead of adding these properties to your tables as columns, you can create functions or views that calculate these properties for you.

    As Ron pointed out, the Respawn_Per_Hunt table is unnecessary. A hunt is performed in a single location, so you can have a direct reference to the Respawns table from the Hunts table.

    To get rid of the fixed columns in Wastes and Transfers, you will want to create a table for participants.

    I attached an example of a database design.
    Hunting.png
    [Thumbnail for Hunting.png]
     
    Gerard Ramon Monte
    Greenhorn
    Posts: 5
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Oh! Ok now I get it! If i'm not wrong this relations are all 1:N, where the 1 is the key icon, isn't it?

    A tiny detail is that i don't really want to store the people names, so could i use the table Hunter this way?
    ID - Role
    1 - EK
    2 - ED
    3 - RP
    4 - MS

    And then, in participation asign 1 to 4 depending of the vocation?

    And in the future, when I add that custom Hunt feature, I could add another table to store that data and a different way to show the information in my app so it adapts to how many people were in that hunt.

    So storing all the data this way, will allow me to keep showing the information in my table i shown you before? I supose i will have to do some SQL queries to get the information i want.

    In my head this works this way:
    **The ¿? fields are things I don't know if I would have to add, or I can just calculate it and then show it in the application table using SQL queries.

    A Hunt will have an ID, a location ID, a date, and a loot.
    HuntIDLocationIDDate¿¿People??Loot¿¿Profit/each??
    15(Prison -3)14/06/2019390050


    Then the participation table, will have (in the case we are 3 people in party), 3 rows refering to that hunt
    HuntIDHunterIDWasteTransfer
    11(EK)100150
    12(ED)300350
    13(RP)50100


    Then, to show this information in the application table, I can do SQL queries to get almost all the data, exept the things in ¿? wich I could just calculate at the moment.

    I am ok?

    Sorry for asking for your help, but I'm really new and I don't have anyone to ask, so I have to ask you hahah

    You're being so nice, and for that I'm so gratefull

    Thanks again!
     
    Stephan van Hulst
    Saloon Keeper
    Posts: 10308
    217
    • Likes 1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator

    Gerard Ramon Monte wrote:A tiny detail is that i don't really want to store the people names, so could i use the table Hunter this way?
    ID - Role
    1 - EK
    2 - ED
    3 - RP
    4 - MS

    And then, in participation asign 1 to 4 depending of the vocation?


    Sure, but don't use abbreviations. Instead, create a table Role where you can give roles a name, and then let the Hunter table refer to a Role with a roleId column.

    So storing all the data this way, will allow me to keep showing the information in my table i shown you before? I supose i will have to do some SQL queries to get the information i want.


    Yes, or you can create views of your tables. They are like tables, except their values are calculated by performing queries on real tables.

    **The ¿? fields are things I don't know if I would have to add, or I can just calculate it and then show it in the application table using SQL queries.


    Correct, you can remove People and Profit and calculate them in a View.

    Then the participation table, will have (in the case we are 3 people in party), 3 rows refering to that hunt

    HuntIDHunterIDWasteTransfer
    11(EK)100150
    12(ED)300350
    13(RP)50100


    Instead of Waste, call it Expenses. Transfer is redundant, because it can be calculated, you only need to keep track if the participant has been paid yet. Unless payments to a participant can be made in multiple transactions for a single hunt, but you haven't explained that case yet.

    Then, to show this information in the application table, I can do SQL queries to get almost all the data, exept the things in ¿? wich I could just calculate at the moment.


    Yes. Here are some T-SQL queries I made so you can create views based on the tables I showed in the attachment above:

     
    Gerard Ramon Monte
    Greenhorn
    Posts: 5
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Wow man, I have no words, you're my idol...

    I'm gonna use that so I can start trying new things and learn

    You're being so helpfull, really, lots of thanks.

    Can I buy you a coffe or something to thank you?  

     
    Stephan van Hulst
    Saloon Keeper
    Posts: 10308
    217
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Hah, unless you're willing to come up to Cologne, no thanks. Let us know when you managed to sort it all out.
     
    With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
    • Post Reply Bookmark Topic Watch Topic
    • New Topic
    Boost this thread!