Questions regarding databases.

Having never set up or run a web-page of my own, and being a relative newbie to programming, I've never had any experience with any of the popular databases.

I just picked up a book on MySQL and breezed through it in a day and a half and, while it was primarily just an introductory text on MySQL and SQL in general I feel like I've got a decent grasp on it.

I was browsing through the MySQL doc's and saw a section titled "Analyzing Spatial information." "Woah, wait a second," I thought to myself, "they've got a fair # of the functions I was thinking I'd need to implement for my coordinate based MUD codebase."

With my curiosity piqued I searched for spatial databases in Google. I narrowed the results down to MySQL and Postgres since I don't know of any other free (or really cheap commercially licensable) databases.

I found this article: http://wiki.astrogrid.org/pub/Astrogrid/DataFederationandDataMining/cross.htm

The article indicates that Postgres is much faster than MySQL when it comes to spatial data, and also offers more features. Of course, the article is a bit old and I'm not sure that the speed differences are as vast anymore.

It will be a while before I get to implementing the spatial aspects of the MUD (I'm still working out the framework of the server code along w/ numerous other details), but I do plan on whipping up a basic database interface for testing. So despite the fact that I won't need the features right away, I want to make sure I pick the right DB so I can start building the interface. Reports seem to indicate that MySQL is faster for general queries and such, while PostGres has the upper hand when it comes to features and spatial indexing.

Should I just go with Postgres (as most of the queries - at least the processor intensive ones - will be regarding spatial data?). Anything else I should look at or consider?

The other option I forgot to mention was simply holding all relevant data (data that is currently active & loaded) in my own spatial partitioning structures in the server program itself, and only unloading it to the database when saving a region or character to disk.

I was thinking though, that if I created a copy of relevant spatial data tables in the database that resided in memory that the database lookups might be as fast or faster than doing it within the main server code. Also, having the database itself do all lookups would allow me to distribute the computing (should it become necessary) by moving the database to another machine, (and allow concurrent lookups if I wanted to incorporated multi-threading or a multi-process approach from the server.)

Haven't done anything much with the spatial datatypes and functions but I can make some comments on DBMSs in general.

Firstly I strongly doubt that you'll find a significant difference in performance between the two. Sure one may be faster than the other for this or that but overall they are probably going to be fast enough. If your choice comes down to just that difference then you'll need to benchmark, tune and failing anything else apply additional hardware resources.

MySQL for example has multiple storage choices and other tuning options that can greatly affect performance. Some people argue that it foregoes normal DBMS functionality for speed but this is less the case since V5.

In any case, aim for database independence. Design it to run on any DBMS that supports spatial functions. There are ways to insulate your code from DBMS particulars. That way you can easily use either - or a third option that comes along later.

You'll probably also find that compared to your own code the outright performance of the DBMS functions could be slower. This can be offset if you are using a DBMS anyway to store the data. Depending upon the DBMS and your design, a lot of work can be avoided by the DBMS optimiser. It might for example screen out records based on spatial criteria and thus not even return them to your application.

Considering the DBMSs, I view PostgreSQL as being solid and complete. 'Mature' would be the summary in a single word.

MySQL is more up and coming. Not quite there as a complete DBMS but almost certainly good enough for what you want. There is a lot of support and some nice tools out there for it.

Summary - Design for independence and choose either. Slight preference here for PostgreSQL.

Thanks for the reply PGF. Some more searches here revealed Gman's DBMS wrapper code. It looks like it supplies a nice independent interface to both MySQL and PostgreSQL, so choosing one or the other at this point won't be much of an issue.

I need to give a big thanks to Gman for the module!

Regarding performance issues, I suppose you're right. I have a bad habit of fearing that the KISS solution will be too slow and end up trying to search out and implement systems that are far too advanced for my current skill level and then get discouraged by the lack of progress and end up giving up. Baby steps, baby steps. I think I'll just go ahead with MySQL for the moment only because I've got some documentation on it and it seems simpler than PostgreSQL. Early on during the implementation of the spatial handling code I'll test the hell out of it, and if I actually run up against performance barriers I'll make a switch.

They are. Postgres is not only faster, it also supports ataomic transactions (which MySQL only supports from version 5+).

You want a free database, get Postgres or Firebird.

Hmm. I did notice the transaction issue. I'd be running MySQL 5, so I could avoid it, but unfortunately any databases created to run in system memory are stuck using the MyISAM engine, and it's not transaction safe. I suppose that could lead to duplication bugs/errors and such.

Well, I'm going to do a bit more research. Looks like I might be switching to Postgres afterall. (Firebird looks nice but doesn't have support for spatial databases. Also, Gman already has a wrapper written to interface w/ PostGre and MySQL. )

Quick question for those who have more experience w/ databases than I.

I apologize in advance for the vague details of this question, but they just don't exist yet.

I'm at an early stage of designing/implementing my MUD codebase, and am wondering if I should build the necessary database tables and such as I implement the MUD, or if I should wait until I have my object types more clearly outlined. At this point the necessary info regarding in-game objects, mud settings, etc, are bound to change quite a bit.

I see pros and cons to each. Incrementally building/modifying the database along with the codebase allows it to grow organically, although sometimes the trimming process might be quite cumbersome. If I wait to implement the database, I'll have to either temporarily exclude certain functionality or code a temporary hack version of it.

I guess my main problem is I'm at a point where I've just got *so much* to design and develop that I don't know where to begin. Is there a standard 'best practice' regarding database implementation when at such a precipice?

Hello.

Design your database first, following good old fashioned normalisation techniques. The process of drawing out the structure and refining it will aid you in creating new fields and extending the structure, as well as improving your understanding of what you're trying to acheive.

Following correct normalisation techniques will allow you to extend the structure without causing impact on the rest of the structure.

It can be a drag, of course, but having a clearly defined and extensible structure before you even begin coding will put a step closer towards succeeding with what you're trying to do.

Don't worry about the how to begin with, the actual db used should be a secondary consideration in many respects. Just draw out what you want to do on paper, then refine refine refine. You will have a much better idea of what you're trying to acheive when you finally get around to coding and won't be surprised by anything that people might throw at you.

Goodbye.

Thanks for the advice!

Drawing diagrams? Hehe, that's what I'm doing right at the moment, actually. :) I figure I'll design as much of the database as I can given what I currently have planned/designed. This is the first database I've ever designed, and while it's not difficult per se (so far) I've got to watch out for non-normalization. Thanks for the heads-up on that (and for introducing me to that term in this context. I'd not heard of database normalization before, although I knew the concept just not the name.)

I'm not sure if it's been mentioned, but if you create a database controller class in you web application, you can call instances of this for your database connections from your web app - if you later decide to change the database you are using, you will only need to modify the code in the class instead of having to modify the entire code of your application. Using this method, you could even insert support for multiple database types.

Yeah, I definitely plan on separating all of the database interface code from the actual game as much as possible. I've been trying to keep my whole design modular - for instance every player connection has it's own protocol object, allowing players to connect using say, Telnet, through a java applet, or through a custom client.