Choosing a database

[InsomniacPro]

Hello community
So I've seen the types of database handling used around the community. The normal mysql statements as seen in the source released by impulse, then nhibernate, which has been used a lot lately. Then theres the MsSql I've seen used by Jack. And then there's flatfile which is seen in Roy's source. So question to the community, which is the best choice to use when it comes to the server. A source coded in C#. Looking for anyone's input.

[patcac-patcac]

i havent seen them all but right of the bat i would never go with a flat file since the disadvantage outweigh the advantages.

Consider flat file database example below

ID	Title	First name	Surname	Address	City	Postcode	Telephone
1	Mr	Tom	Smith	42 Mill Street	London	WE13GW	010344044
2	Mrs	Sandra	Jones	10 Low Lane	Hull	HU237HJ	022344033
3	Mr	John	Jones	10 Low Lane	Hull	HU237HJ	022344033

1. Potential duplication. With perhaps thousands of records in a file, it can be a very tedious process to spot duplicated records. Especially if more than one person is maintaining the table.


2. Harder to update. Suppose this table now needs to also store their work details - this will result in multiple records for each person. Again, this is fine - but suppose Sandra Jones now wanted to be known as 'Sandra Thompson'? Now multiple records need to be updated.

3. Inherently inefficient. What if an email field needs to be added?. If there are tens of thousands of records, there may be many people having no email address, but every record in a flat file database has to have the same fields, whether they are used or not.

4. Harder to change data format. Suppose the telephone numbers now have to have a dash between the area code and the rest of the number, like this 0223-44033. Adding that extra dash over tens of thousands of records would be a significant task in a flat file database.

5. Poor at complex queries. Flat files are excellent for simple filtering. For example, show all records where the field 'City' contains Hull. But for anything a bit more complicated, a flat file becomes very difficult to use. For example, find all records whose post code contains '23'.

6. Almost no security. You can protect a flat file database using a password for opening it. But once it is open, that person can usually see all fields. This is often not a good thing, for example there may be a confidential field containing their salary that only some people should be able to see.

Better to use a relational databse

[turk55]

NHibernate (ORM) solution for the Microsoft .NET platform: it provides a framework for mapping an object-oriented domain model to a traditional relational database. Its purpose is to relieve the developer from a significant portion of relational data persistence-related programming tasks. NHibernate is free as open source software that is distributed under the GNU Lesser General Public License. NHibernate is a port of Hibernate

Source: NHibernate - Wikipedia, the free encyclopedia

I would go with MySQL or MSSQL. (NHibernate should work on both)

[Y u k i]

I went for INI files on a ramdisk Uses way less memory than mysql (300+mb) and is faster

[Super Aids]

I used mssql in mine too. I personally prefere mssql because its implementation in .NET is already flawless especially with the tableadapter and dataset implementation in Visual Studio. The amount of coding you have to do is minimal, although I didn't use tableadapters/datasets when I used mssql in my source since I didn't use Visual Studio to develop it in.

[Aceking]

Personally I prefer MSSQL, I find it extremely robust and clean.
Handles data very well, I work with it on a daily basis at work and it manages millions of rows of data just fine.
I can't say much about MySql. To me, it feels like childs play compared to MSSQL. But thats just my personal opinion.

Your next decision is if you want to use a wrapper like NHibernate to manage your database, or if you are going to manage it yourself.
You could very easily create your own wrapper to handle your database transactions tailored to your needs.
But in a project that requires a large amount of traffic between the server and database, especially on so many levels. A wrapper is almost a must. Otherwise you would be creating SQL statements left and right and it would just get ugly.

[InsomniacPro]

Originally Posted by Aceking

Otherwise you would be creating SQL statements left and right and it would just get ugly.

Oh lord, I think we all got a whiff of that million SQL statements when LOTF was around. Talk about redundant.

I really think I may go with MsSQL since its intergrated with MSVS. I just have to read up a bit on it, from what I hear its similar to MySQL more ways than not. I'll just need to get a handle on it

[Super Aids]

Originally Posted by Aceking

Personally I prefer MSSQL, I find it extremely robust and clean.
Handles data very well, I work with it on a daily basis at work and it manages millions of rows of data just fine.
I can't say much about MySql. To me, it feels like childs play compared to MSSQL. But thats just my personal opinion.

Your next decision is if you want to use a wrapper like NHibernate to manage your database, or if you are going to manage it yourself.
You could very easily create your own wrapper to handle your database transactions tailored to your needs.
But in a project that requires a large amount of traffic between the server and database, especially on so many levels. A wrapper is almost a must. Otherwise you would be creating SQL statements left and right and it would just get ugly.

The last part is not necessary if using Visual Studio since it already has database management and design implemented in the IDE and it can generate code corresponding to your tables with select, update, delete etc.

Look my post above and the link to tableadapters as well look up dataset design in Visual Studio.

[Yupmoh]

Originally Posted by patcac-patcac

i havent seen them all but right of the bat i would never go with a flat file since the disadvantage outweigh the advantages.

Consider flat file database example below


1. Potential duplication. With perhaps thousands of records in a file, it can be a very tedious process to spot duplicated records. Especially if more than one person is maintaining the table.


2. Harder to update. Suppose this table now needs to also store their work details - this will result in multiple records for each person. Again, this is fine - but suppose Sandra Jones now wanted to be known as 'Sandra Thompson'? Now multiple records need to be updated.

3. Inherently inefficient. What if an email field needs to be added?. If there are tens of thousands of records, there may be many people having no email address, but every record in a flat file database has to have the same fields, whether they are used or not.

4. Harder to change data format. Suppose the telephone numbers now have to have a dash between the area code and the rest of the number, like this 0223-44033. Adding that extra dash over tens of thousands of records would be a significant task in a flat file database.

5. Poor at complex queries. Flat files are excellent for simple filtering. For example, show all records where the field 'City' contains Hull. But for anything a bit more complicated, a flat file becomes very difficult to use. For example, find all records whose post code contains '23'.

6. Almost no security. You can protect a flat file database using a password for opening it. But once it is open, that person can usually see all fields. This is often not a good thing, for example there may be a confidential field containing their salary that only some people should be able to see.

Better to use a relational databse

From My opinion, I'd go for a FlatFile DB anytime, And just as a response to patcac The DB doesn't have to be all in one file or a couple of huge files, For example it could be Accounts in a folder, However you don't need to put all accounts in one big file, Make a separate file for each account which MUST be faster than the CPU loading all the accounts off 1 big file.
Also its extremely easy to update things inside an file, Almost as easy as adding a new line to be saved to whatever variables you have in each account.

And also as a response to insomaniacpro, I believe on Roy's thread they discussed that point so you might wanna double check his release thread for PM source.

OH and also MySQL would end up eating your ram TBH, I have it using up to 400MB and Yuki was there to witness it.

[Y u k i]

Originally Posted by Execution!

From My opinion, I'd go for a FlatFile DB anytime, And just as a response to patcac The DB doesn't have to be all in one file or a couple of huge files, For example it could be Accounts in a folder, However you don't need to put all accounts in one big file, Make a separate file for each account which MUST be faster than the CPU loading all the accounts off 1 big file.
Also its extremely easy to update things inside an file, Almost as easy as adding a new line to be saved to whatever variables you have in each account.

And also as a response to insomaniacpro, I believe on Roy's thread they discussed that point so you might wanna double check his release thread for PM source.

OH and also MySQL would end up eating your ram TBH, I have it using up to 400MB and Yuki was there to witness it.

Its faster if its all in one file as the I/O delay is less

But its much more organzied if its split

[Yupmoh]

Not what I've read but I could be wrong

[CptSky]

Originally Posted by Execution!

Not what I've read but I could be wrong

Better to have one big file. Less seeking on the hard drive as it should be one block (ideally not fragmented), plus you don't need to request an handle on the file (open) multiple time.

Anyway, flat DB isn't really good. It won't scale well, it will be pain to maintain and will be hard to have all the security around a real database. Plus, you won't be able to retrieve data easily as you can with query.

If you really don't want a server, at least use a real database like SQLite or .

[Korvacs]

Sad to see people still actively working with FlatFile databases.

[InsomniacPro]

I'm sorry for updating an old post, but just some heads up. I was working with a source that uses completely ini. As I was converting to MsSQL, I noticed this.
Loading NPCs went from ~300ms, to ~12ms. Loading MonsterData+MonsterSpawns went from ~1300 ms, to ~260 ms. Loading map information went from ~1700 ms to ~200 ms. Among other things. This speed improvement should let people know a bit about this.

[Spirited]

Jack and JP are both correct. Although the connection time to the server might be high cost, keeping them open to interact with the database only takes a few microseconds. The point is, when you try to access one file, or a lot of different files as IO increases with an increasing playerbase, it will slow down considerably. I've seen this before on my campus - when a lot of students (40-60) start writing files to the server, it just slows down to a point where the server is no longer usable (using one hard drive that is). I highly recommend against a flat-file database, avoid it at all costs. You can keep server resources (such as maps and data loaded once into the server) in flat-files, but I highly recommend that player data is kept in a SQL or similar database. It will scale much better than a flat-file database.