Howdy, Stranger!
It looks like you're new here. If you want to get involved, click one of these buttons!
Quick Links
Dev Blog | Databases
BlackLotusDragon
Member Posts: 33
Yevi details his work designing and creating the databases for the ToA technical demo:
My first task was "We need a player/login database". The good news is that I know databases and database design. The better news is that this is a fairly easy database to start with. However, where do you start? Well, welcome to the world of a software engineer.First, think about what will be needed. We need information about players, like names, email, addresses, and passwords. We will want to track subscriptions. We might want to tie in to their characters.
Second, write it down. For database design, there are types of diagrams that are used to show the particulars of a database structure. I used MS Visio which is a diagramming tool and I picked a template called an Entity-Relationship diagram. An entity is a single type of data, like a person. That's a block. Within the block are the particular values I want to save for a person, like first and last names. Another important value is the person's ID. An ID is just a way to keep all people separate. Some people have the same name, and it is possible to have the same name and birthday. Email could be used, but changing an ID in a database can be difficult which would make changing email difficult. A database stores multiple types of data. An address is a different piece of data. Relationships are how different types of data tie together. The person data is related to address data and the persons ID is used to connect them. Relationships are important mostly for keeping the data clean. If a person is deleted from the database, what goes with them? You don't want to have an address saved when there is no player attached to it. So, a relationship will delete an address along with the associated player.
Once things were written down, I sent the diagram to Teddy for comments. Between the two of us reviewing the diagram, we modified it until it looked like we had all we wanted. The next step was to create the database with a database application. We chose MySql as our database. It is free and fairly common. It also comes with a nice tool called MySql Workbench which helps create and manage databases.
SQL calls its entities "tables". Individual kinds of data in a table are called columns. Each instance of data in a table is called a row. In Workbench, I created each table, and all the columns for each table. Then I created all the relationships. With everything I created, I refined the designed and I saw things that could be better, or were missing. The tables, columns, and relationships are collectively called "Schema" and are the structure of the data. No data is contained, but how the data will be stored is all laid out.
The last step is to create test data. This is used for testing the database and how the rest of the system handles the data. This is sort of the fun part. You add people like Brax, Loestri, and Varl. You give them fake addresses. One of the tables in the database is for aliases, so I made one of the programmers (other than Teddy) Master of the Universe.
Once that is all done, I exported the structure and the test data out to files so they can be passed around and used to recreate a copy of the database as needed.
To use the data, we need software (programs) to manipulate the data. This is done by creating program layers or modules. A layer is a segment of the code that operates within itself and data flows into and out of the layer at very specific points. The layer that accesses the database directly is called the Data Access Layer or DAL. This is very simple code to write, but it tends to be tedious and repetitive. Usually a tool is used to generate this code automatically. The next layer is called the Object Relational Model (sometimes object relational mapper) or ORM. This is a mapping and translates database structure to an object structure more suitable to high level programming languages like C# and Java. The can also be tedious and usually tools are used to generate code. However, this code is more often modified than the DAL.
I am using a framework called nHibernate to do the mappings. It is a common tool to use, but it is not the only tool and using a DAL and ORM arent the only ways to setup database access. Generally when you use a tool written by someone else, there is a basic set of pros and cons. The pro is that you dont have to write all that code and the con is that you have to learn how the tool works. I suffered a bit from that in using nHibernate. It took a while to make stuff work according to how nHibernate wanted it to be.
The ORM is a very important piece because how data is represented in the database is very different than how it is stored in memory for a program to use. Programmers like to program using objects while SQL databases store things as grids. The ORM allows for the best of both worlds.
Once the ORM and DAL are completed, then we write queries. A query is a question asked of the database. This is typically more layers of code called the Repository and Query layers. The repository represents the basic queries. It uses the ORM to access the database. It looks more like database access even though it does not use the database language directly.
The query layer uses the repository objects to do more complete operations. Typically repository objects focus on one specific entity/table while queries can combine different entities.
Finally, there is the top level code that gets information from the database and uses it for things like Logins and subscription maintenance.
How do you see my work? When you click the Login button, all the stuff that happens behind the scenes chugs away, that is my work doing stuff. The information you type in goes into a query to check against the list of names and passwords in the database. Whether or not there is a match is reported back. If a user doesnt have a login, they will create an account. Once again, my code is used to create a new player entity with some additional information. My queries also check whether or not a particular user is allowed to create an account.
Thats it. That is how the player database was created and used. Next time I will cover how the work for the game data went differently.
