Database Layer Architechure Question (Flat Vs Sql)

This forum can be used to talk about general design strategies, new ideas and questions in general related to wxWidgets. If you feel your questions doesn't fit anywhere, put it here.
Post Reply
DaveK
Earned a small fee
Earned a small fee
Posts: 17
Joined: Thu Jul 27, 2006 4:29 pm

Database Layer Architechure Question (Flat Vs Sql)

Post by DaveK »

The question is the following. It seems a discussion between using a flat file and using XML and using a Relational Database will come to pass with our application soon. Some say, flat file, some say Relational Database. What do people here recommend. What I can say about the application is that it will be data intensive.

Thanks!
qgranfor
Experienced Solver
Experienced Solver
Posts: 89
Joined: Sun Aug 29, 2004 7:32 pm
Location: ND...USA
Contact:

Post by qgranfor »

Depends on what you're doing. You don't really provide much information.

"data intensive app" doesn't really specify anything.

1,000 a day?
10,000,000 a day?
1,000,000,000 a day?
Multi-user environment?
Remote access via wan?

Right tool for the job......but I don't know what the job is......so I can't comment. :D
DaveK
Earned a small fee
Earned a small fee
Posts: 17
Joined: Thu Jul 27, 2006 4:29 pm

Post by DaveK »

okay... more info...

The application is a Fantasy Roleplaying Character Generator and Manager program. It will be data intensive in that it is accessing information from a ruleset contained in books known as a Rolemaster Fantasy Roleplaying which contains many tables and a lot of information on how a characters are created using these rules. For example there is a whole table devoted to statistics for the character a whole table of skills, and so on. Most of these tables will be somewhat small but some of them will contain a LOT of information. In some cases the tables are related and some calls are so complex that it may need information from 5 or 6 tables at once. The application is a single user interface for now, but there is a possibility down the road of making it a LAN (maybe even WAN) application so information can be shared. I don't think we will ever share a DB across a network though. I think the db will always be a local to the user file.

I've done some reading into flat files and databases and I have come up with a list of pros and cons in relation to this application. The pros a flat file are that it highly portable and can be a lot faster to access simple table data and even insert table data. The cons are it becomes slower when I have to compare more than one table to another and get specific information out them. This is where an Relational database is stronger. the cons to dbs is that they can be system specific most of the time and they are hard to update.

However i'm pretty convinced that this program is going to need a RDB in the long run and that a flat file may be good at the beginning, but down the road it will shoot us in the foot. First of all I'm thinking because of the system rules, there will be a lot of tables to define, hence a lot of flat files to keep track of. A db is all self contained so no worries about version control or anything. Also, sqlite is highly portable from what I hear so it solves the system specific database problem and it also it easy to update by using webtools that are written to work on any platform.

I guess my whole purpose of this topic is too see if my opinions and information are good or if there are other concerns and things I should know. :)

Thanks!
qgranfor
Experienced Solver
Experienced Solver
Posts: 89
Joined: Sun Aug 29, 2004 7:32 pm
Location: ND...USA
Contact:

Post by qgranfor »

Uh, wow.....faints (not in bad way). I struggled with this same question years ago when starting my project (wxDMHelper, unfortunately the website is down for another month or two). My application is being developed to handle many different gaming systems (Gurps, DnD, Warhammer, etc....Rolemaster is on the list too actually although it's been a long time since I've read the rules) and to try to do everything under the sun. It's meant to keep track of EVERYTHING which has caused it too become a bit unwieldy in areas. If you hit an orc with a fireball doing 10 damage three years ago.....it's still recorded. Random character generation and treasure generation are currently in place for AD&D 2nd Edition. So, I know exactly where you are coming from.

Currently, there are 35,000,000 rows of data in my program, which equals about 39GB (video, music, textures and models for the 3d environment are the space killers)........guess which route I took. :)

I was originally using Clipper5 xBase files (which are basically ASCII text) but quickly ran into issues due to the complexity of the data being using within the program. Also, I'll have tables with the potential for billions of rows in which case text files just really aren't an option.

Rolemaster is a complicated system as you know and I think long-term you'd probably be better off going with a RDB for the flexibility and ease of joining data together. Course, this all depends on how complicated you're going to make your application.

I chose SQL via wxODBC for the following reasons on my application:
1) Ease of backup. Just do an sql dump and save to another machine.
2) Flexibility in retrieving complicated data relationships. For example, I built my application to have items/skills/etc tied to specific books (like Blood Law, War Law, etc). I did this so that the Gamemaster could select which books he/she allows in a specific campaign. If the campaign doesn't allow Blood Law book no items/skills/etc would ever be randomly generated or available to the players that are contained in that book unless the items/skill/etc also appear in a book the Gamemaster does allow. Also, the Gamemaster can ban specific items/skill/etc, which would then not be randomly generated or available to the players. This way you can customize your campaign quickly and easily for the items/skill/etc available to the game world.
3) Volume of data. I have the potential for billions of rows of data due to things like the kingdom generator, which actually generates records for each citizen.
4) Remote connectivity via LAN or WAN. Clients can connect to the game server from around the world this way and retrieve data.
5) This relates to #3. I'm currently at 39GB of data, which is unrealistic for people to download and install on their client machines. The application is being built to only download what's required for the next gaming session (sounds, models and like), but can download them on the fly for when the unexpected happens.

The downsides for the route I took:
1) Added complexity for the users. In my case they have to install an ODBC driver and setup the appropriate DSN.
2) Overhead of the database, but really this isn't that much of an issue as they are quite fast nowadays. Realistically, I probably couldn't write a script to parse 5-6 text files and join the appropriate data that would perform as fast as a typical SQL server anyways.
3) Should have a fast network in place

I'll fully admit my program is overboard to a crazy degree and you might not be looking for that much functionality.

I
DaveK
Earned a small fee
Earned a small fee
Posts: 17
Joined: Thu Jul 27, 2006 4:29 pm

Post by DaveK »

It's cool to hear i'm not alone here with the application I trying to build. I built an application like this before under a .NET framework and I used a .Jet database to hold the data. Granted some of the calls were a little slow and inserts were also a little slow, the complexity of the data and putting together data to ahear to the rules I think is what not only made the database powerful but in turn made the application powerful.

Right now i'm moving the application to cross platform. I have other people on this team and we have yet to decide the framework and database. Some are pushing for flatfiles. From what you said and what I read, I will definitely be pushing for a RDB simply because right now flatfiles may look nice and will work for the application, down the road when the application gets more and more complex, flatfiles are going to become a lot more complex and will eventually slow the application down. I would like to be ready and have everything in place so the application is ready to "get" serious. Based on other's input I will pushing SQLite3 as it is cross platform and there are even managers that a person can download and use their browser to edit. I find that ahearing the cross platform rules VERY well! :)

Thanks for the input. I will certainly refer to it when it comes time to argue for a database layer.
Post Reply