Database / Data Model

Post by **McQwak** » Tue Dec 29, 2009 3:43 pm

Great idea and very long way to success. Check this thread for ideas that we gathered from users here. Very challenging dream

Regarding the database: I consider myself "a database guy", not sure about expert

. Anyway, what do you want to know? Do you have some data model or basic relations etc. in mind yet? I'm not sure how I can help you.

I'd suggest you to discover EHM database using the pre-game editor and you'll see how EHM data model looks like.

Post by **dabo** » Wed Dec 30, 2009 6:35 pm

So about the database, I have discussed this at another forum earlier but more input is always welcomed.

Obviously I need a way to store the game data in a good way. If we for example take a look at a player, basically everything that don't grow in size/number while playing is easy to save like for example a players personal data. But if we look at stats for example, what's the best way to store those? For each season (and perhaps everytime a player gets traded) new stats need to be stored separately. Like this:

Season Team Games Goals Assists +/- etc...
09/10 TOR 20 5 11 2
08/09 TOR 80 22 37 -1
07/08 TOR 50 12 19 -3
07/08 NYR 31 7 20 4

Here we have 4 but this could be different for each player.

I proposed this design at another forum:
One table where personal data and other data that is fixed in size/number is stored.
One table where player stats are stored (then use a player id to look up his stats)

Also when it comes to the contract, the salary can be different each season and also the length of the contract. Atm I don't have a good idea how to store this but I guess I could use a similar manner as described above. Or make it easy for me and just store a seasonal change in salary, for example that the salary decreases by 5% for each season during the contract.

To sum it up, I need design ideas on how to store data that can vary in number between players. For someone who has a lot of database experience this might be a bunch of nonsense and the easiest thing in the world; but I am certainly not one of them so I wouldn't know

.

Post by **McQwak** » Wed Dec 30, 2009 7:09 pm

OK, I know what you mean. I will give you some solutions next week when I get to work

Post by **Manimal** » Wed Dec 30, 2009 7:13 pm

dabo wrote: Season Team Games Goals Assists +/- etc...
09/10 TOR 20 5 11 2
08/09 TOR 80 22 37 -1
07/08 TOR 50 12 19 -3
07/08 NYR 31 7 20 4

I think you should have the latest season in the bottom like this

07/08 NYR
07/08 TOR
08/09 TOR
09/10 TOR

Post by **dabo** » Wed Dec 30, 2009 7:51 pm

McQwak wrote:OK, I know what you mean. I will give you some solutions next week when I get to work

Hehe, sounds good, no hurry

Manimal wrote:
dabo wrote: Season Team Games Goals Assists +/- etc...
09/10 TOR 20 5 11 2
08/09 TOR 80 22 37 -1
07/08 TOR 50 12 19 -3
07/08 NYR 31 7 20 4
I think you should have the latest season in the bottom like this

07/08 NYR
07/08 TOR
08/09 TOR
09/10 TOR

Yeah I could do that, I just had Football Manager's way in mind, don't remember how it is in EHM:NHL.

Post by **Manimal** » Wed Dec 30, 2009 9:21 pm

Not just EHM, it is that way all over. I've never seen the latest season on top, anywhere

Post by **B. Stinson** » Wed Dec 30, 2009 9:48 pm

TSN and The Hockey News both show the latest year on top.

But regardless, it'd probably still be better to go from earliest to latest, for the sake of future editing. It's easier to just tack-on entires at the bottom of a list than it is to continuously shift everything down to make room for a new entry on top.

Post by **Jypfan92** » Thu Dec 31, 2009 7:58 am

Manimal wrote:Not just EHM, it is that way all over. I've never seen the latest season on top, anywhere

NHL games have latest season on bottom, i have NHL 99, 05, 08 and 09.

Post by **dabo** » Thu Dec 31, 2009 2:47 pm

Don't worry about the order people

. Whether I want ascending or descending order is just a matter of adjusting the query string accordingly.

Post by **dabo** » Sat Jan 02, 2010 9:02 pm

I am trying to figure out what is needed to store for each player, team, league etc. in order to come up with a good database structure. The thought of all the possible league rules is giving me a headache

. I guess I don't even know about half the rules in the NHL

.

Post by **dabo** » Sun Jan 03, 2010 12:19 pm

There are many SQLite editors out there, this one for example: http://sqlitebrowser.sourceforge.net/

Post by **McQwak** » Mon Jan 04, 2010 2:37 pm

So I started to design some data model and I found that it would be very time consuming. Do you want EHM data model or only changes to cover bigger customizability or what? I can do EHM data model and then we can together think of changes. But simply I don't know whether you want to make it based on EHM or not. What do you plan and what have you decided already regarding db (besides SQLite)?

btw: I think that the most important will be game engine and logics and it will be extremly hard to tweak it to desired results. Risto did it for couple of years AFAIK.

Post by **dabo** » Mon Jan 04, 2010 11:42 pm

McQwak, I have not decided anything really. Once I have figured out what to save I will move on to how. Do you have any ideas? I am open to anything. You are the one with database experience, whatever you think is the easiest/best is fine by me. Sometime this week I will post a list of the player data that I have thought of so far.

I am not familiar with the EHM data model. I checked out a pre game editor for EHM:NHL 2005 (didn't find one for 2007) but I don't think it helped much.

Even if everything won't be used in the first version(s) I still want it in the database from the beginning. I like thinking ahead, less risk of bad surprises.

My plan is to setup a database, write the basic classes and then write a basic simulation model using the basic attributes stored in the database; basic is the keyword

. The simulation will be developed iteratively where I will keep adding more and more stuff.

Post by **McQwak** » Tue Jan 05, 2010 11:59 am

OK, so I created current EHM data model for inspiration.
http://i48.tinypic.com/2iizf5e.jpg
It's modelled using Enterprise Architect, so I can send you also file in EAP format. I avoided to describe all fields (columns, such as player attributes, club info, colours and many more) in db, I think these can be easily seen using the editor. I can add them later though, if you want. This diagram shows just relations and their cardinality. The game simulation uses these data.
There is a lot of enumerations, that can't be altered using editor - this is the biggest limitation in customization.
I'm not sure how we can continue with it and not to violate with law

Later this week I'll upgrade it to cover the most wanted needs (customization) and we'll see what to do next. Please write me any mistakes and recommendations. Esp. dabo, what do you want to have stored in db and is missing right now etc.

Post by **dabo** » Tue Jan 05, 2010 1:33 pm

Thanks looks good. I have a question regarding that image. Are the boxes tables in a database? and not the actual classes written in code? or both? I am a little confused

.

Anyway to show how I am thinking (the big picture) I can throw together a UML-like diagram describing some classes (without attributes and methods). Perhaps it will be easier to come up with a database design if we have one; I dunno.

I will see if I can find a trial version of Enterprise Architect so I can load the file if you send it to me.

Post by **McQwak** » Tue Jan 05, 2010 1:52 pm

These are tables. But I guess you can consider them as classes, too. For example "Draft_Details" and "Staff_Clubs_History" are contained in Staff entity, but have separate tables.
The rela way how data can are stored may vary from this. But basically I draw tables.

Post by **dabo** » Tue Jan 05, 2010 2:53 pm

Ok thanks for the clarification. I will upload a diagram of my ideas some time this week.

Post by **dabo** » Sun Jan 10, 2010 12:04 pm

Ok here are my ideas, some are the same as in your diagram McQwak.

Click here
(I expect to have more tables but this is the big picture right now)

I am gonna add a few words to describe it.

Persons and Competitions will not be tables, I just drew them to get less lines in the diagram. All lines going from/to Persons will be part of Staff and Players and the same thing for Competitions, in this case Tournaments and Leagues.

I have some boxes with a Stats suffix, these will be separate tables storing stats for Players, Staff and Teams.

The two relationship boxes will store relationships between Persons and Teams. As every person/team doesn't have the same number of good/bad relationships I chose to store them all in their own tables (same reason for the different stats). So for example relationships between all teams will be stored in the same table.

Tournaments will store tournaments like the Olympics etc, these events are usually associated with a specific arena or two; that is not the case for a league (every team has its own arena).

As for the Draft History, I drew it so that a player knows when he was drafted, a team knows who it drafted and when as well as the league who knows who every team in the league drafted and when.

I hope you understand the diagram, it got kind of messy in the end. If there are inconsistencies in the diagram/text please let me know as well as if something obvious is missing. Any questions and suggestions are welcome.

axwel3221 · Post by **axwel3221** » Sun Jan 10, 2010 2:56 pm

Oh, NOW I GET THE IDEA! Just about like, umm, now! (bam) I understand the diagram thing. and it also looks great. Keep up the work!

newsguyone · Post by **newsguyone** » Sat Jan 16, 2010 6:47 pm

Updates Please!

Post by **dabo** » Sat Jan 16, 2010 9:48 pm

Setting up tables in the database atm.

newsguyone · Post by **newsguyone** » Tue Jan 19, 2010 7:10 pm

dabo wrote:Setting up tables in the database atm.

If you ever require testing, I'll play the hell out it in search of bugs, issues etc.

Post by **McQwak** » Tue Jun 29, 2010 1:23 am

So can I help you in designing the db again? Do you have some issues/open questions? Or would you rather postpone changes/upgrades to next versions?

riggert · Post by **riggert** » Tue Sep 07, 2010 6:35 pm

Sounds like a great project. I've been considering taking something like this on for many years but am glad to see that it is already being worked on and it seems like it has a ton of potential. One thing regarding the DB design, you should really consider using Perst: http://www.mcobject.com/perst

It is an object database which means very intuitive to program with, very little code and very fast. If you want to store multiple seasons of stats well then you have a Player class and a SeasonStats class and the Player class has a List<SeasonStats> property. Updating or adding a season is 1 line of code. Grabbing a collection of player objects for a specific team is also 1 line of code - no SQL query followed by looping through the rows and filling in all of the objects - 1 line returns a collection of players.

I've used just about every DB out there over the last 10 years (been programming for 20) and although SQLite would be the best non-object db choice, I think you should consider Perst because it will save you a ton of development / design time. If you had any questions about it I could help with that as well

Good luck with this project, looking forward to be able to finally play a decent hockey sim since the original EHM freeware game has been by far the best to date.

Roy

riggert · Post by **riggert** » Tue Sep 07, 2010 6:37 pm

And one more thing, not 100% sure if Perst's licensing will work for you since it might require that your project be open source in order for it to be free but there are other object databases that are completely free and provide the same benefits