Writing a video game, use DB?
-
I have a pencil and paper RPG that my friend made, years ago. it's incredibly complex - has 20 or so playable races, thousands of ships, tons of planets and systems, this sort of thing. I'm wondering, for keeping track of things, if it would be best if i learn how to do some form of simple database (mySQL?) or just use binary files to keep track of what i have spawned in, as i have usually done in the past.
I ask this because i don't know anything about making, using, or querying a database, and it might be cool to learn.
Stuff that would be kept in the database would be stats for all the races and ships, weapons, etc; and then what is currently active. any ideas?
-
SQLite is your friend. It gives you a small (only 250KB for the engine library itself), fast, and very flexible database with access to most standard SQL syntax. What language are you developing the game in? There are wrappers for it in most popular languages.
-
First, draw two boxes with a line between them. One box is the game. The other box is the saved data (binary file, database or whatever).
Any data kept entirely within the game (never saved) is fast to retrieve, but it's lost when the game is ended and there's a size limit.
Data kept in the other box takes time to retrieve and must be written back when changed.
Next, think about what goes back and forth between the boxes. One extreme is that the entire state of the game is saved as an opaque unit. A new version of the game, with changes in the internal data structures, can't read the old data. That's good enough for some games but maybe not good enough for you now.
The first description of what goes back and forth should be in terms of the "problem domain", i.e. ships and planets, not bits and bytes. Later you will model the ships and planets as bits and bytes. But for now, think of things that go back and forth between the game and the database as a single unit, like a ship.
Relational databases like mySQL are good for systems that break down into "entities" and "relations". An entity is like a player, a ship, or a ship's crew. Examples: Bob (player), Carol (player), Enterprise (ship), EnterpriseCrew (crew). Examples of relationships: EnterpriseCrew isCrewOf Enterprise; Bob IsCrewmemberOf EnterpriseCrew; Carol IsCrewmemberOf EnterpriseCrew.
If you can describe your game in terms like that, a database is a good idea. Then as a bonus you get a pair of side benefits:
1. You can write a separate program that analyzes data in the database, for instance reporting on crewmembers' hours worked or point scores.
2. With a multi-user database you can write a multi-player distributed version of the game.
3. You get RDBMS experience for your resume.
(Hey! I found a use for that three-valued Pair we were talking about.)
-
once again, newfie, you're my hero.
Here's the thing. it's very relational, such that certain ships belong to certain races, for instance, Til'Mek fighters, Human Fighters, etc. Can you keep expanding the relation? such as enterprise (ship), 42nd Flotilla (fleet), Human occupation (race)?
If so, this sounds purrfect. And the crew of the ships is kind of irrelevant, it's assumed when you buy ships that you have people to operate them (each ship has an aribitrary crew limit), since this is an RPG of sorts, you essentially strive to become the owner of a very large fleet, and then take over planets. so it's not like eve online (i don't think), it's closer to an RTS in the play style, but it's closer to an RPG in that you can personally go down to planets and talk and buy stuff. But a majority of the game is Huge HUGE space battles, with thousands or tens of thousands of ships. and each of those ships has stats.
originally if you had a battalion of fighters (for instance) say 1000 fighters, since the GM(game master) can't keep track of a thousand ships you would have to treat the thousand fighters as a larger class ship, with 1000*armorClassFighter and 1000*attackPowerFIghter, but i figure computers can handle several thousand things going on at once a lot better than a human, so it would be cool to have each ship as a seperate entity. hence the whole database thing.
I'd need to have someplace where all the data for the ships and races is stored, and then i'd need another data structure or whatever to hold currentBattle stuff. is this still a relational database issue? :-) are databases fast at this sort of stuff? I.E. when you're fighting an enemy in World Of Warcraft, is the database being accessed each time you cast a spell? or is that all local and it just reports to the database when it is done? the two boxes thing was a help. i am going to start reading up about sql stuff, i heard SQLite and mySQL... what do you guys think about PostGres or some of the other ones? I also have a really old legal version of FoxPro which i don't know anything about. Anyhow thanks for all the input and i hope that once i can get some data stored and some nifty battle algorithms (i already have a 1v1 algorithm designed) that you guys would check out the betas and stuff!!!
-
If you don't have much experience with databases, this is a perfect opportunity to learn. Start by getting a book on "Relational Databases" or "Fundamentals of Databases", rather than one on a specific database. Go to a big bookstore or library and look up "normal forms" or "normalization" in each of the books and pick the one that explains it best to you.
Relational databases and object-oriented design go together but they are not a perfect match. You don't have notions of "inheritance" or "subclass". In a program you might have "Creature" as a base class and "Human" and "Til'Mek" as subclasses, or you might use multiple inheritance with "Human Fighter" deriving from "Human" and "Fighter". This has to be represented in a different way in the database.
On the other hand, relations are more flexible. In object-oriented programming you have built-in relations like "contains" (a member of a struct) or "refers to" (a pointer). In a database, a relation is a table itself and you can have relations between relations. So if a ship is a part of a fleet and a fleet is part of a navy and a navy is part of a department of defense, you could use a single "isPartOf" relationship but it may be better to have separate relationships "isPartOfFleet" ((Enterprise, Fleet1); (Constellation, Fleet1); (Challenger, Fleet1)); "isPartOfNavy" (Fleet1, UFPNavy); "isPartOfDOD" (UFPNavy, UFPDOD). You probably don't need that last one. You may also have "shipIsPartOfNavy" or "shipIsOwnedBy". It all depends on what use you are going to make of that relation. For instance if taxes are collected on privately-owned ships but not navy ships.
From the book you'll be able to draw diagrams of all the entities and relations. Keep everything in the "problem domain", i.e. every decision because it makes sense when thinking about ships and crewmembers, not because they're ints and strings.
Commercial databases are very fast (when set up right and when the analysis and design is done right). You're using gigantic databases when you order books from Amazon.com or bidding on eBay or playing World of Warcraft. For Warcraft there may be optimization with some things done first within the main memory and then saved to the database (this can get really complicated) but basically, if you see another player, the rendering instructions have been pulled out of the database (or cached in the main memory). When you cast the spell it has to get the information on how resistant the other player is to the spell, then compute the damage and save that with the character's data, so that when a third player walks in he sees the character with burned hair or a missing leg.
This will give you experience that if everyone had it, there'd be no database WTF's here.
-
Sorry if this takes up too much of your time, as i assume you have better things to get paid for!
O'Reilly is a favorite publisher of mine, so i was wondering if Database In Depth is a good pick?
Or do you have a good book laying around to recommend? I usually shop through ABEBooks (i love them so much), so i can get them cheaply.
-
[quote user="GeneWitch"]
Sorry if this takes up too much of your time, as i assume you have better things to get paid for!
O'Reilly is a favorite publisher of mine, so i was wondering if Database In Depth is a good pick?
Or do you have a good book laying around to recommend? I usually shop through ABEBooks (i love them so much), so i can get them cheaply.
[/quote]
I have not seen this book, but the book I learned from (30 years ago) was the same author's "An Introduction to Database Systems." I would not recommend "An Introduction to Database Systems" because it's more appropriate to a college course and covers all kinds of different database systems you'll probably never be exposed to. But judging from the reader comments, "Database In Depth" looks like a good book. It's better to learn the basic design issues first, and learn SQL from that -- like learning algebra before learning Java, rather than trying to learn algebra from Java.
So ... learn good database design first, building your game model as you go along.
Update: I just looked through the book here: http://www.oreilly.com/catalog/databaseid/
I like it. It's short and it concentrates on the important stuff. His tone can be a little irritating about how bad SQL is etc. etc. Just ignore that.
-
[quote user="newfweiler"]
I like it. It's short and it concentrates on the important stuff. His tone can be a little irritating about how bad SQL is etc. etc. Just ignore that.
[/quote]
I picked it up and read the first chapter, and the first thing i see is SQL <> relational model. That sort of worries me.
I'll dive in deeper, the whole relational thing is sort of making sense now. I really like the O'Reilly in that they're fairly informal and convey a great deal of information. I know that SQL is a language used to communicate with database engines... Are there other variants? for instance, if you wanted to make a GUI, you can use VB.NET or java's AWT or Swing; is there an analogous ... something in databases? the reason i ask is he speaks as if there were an alternative to SQL. :-)
-
Another option esp if your using VS 2005, MS SQL 2005 Compact Edition, it's still a RC, with the finished version coming out within the next few weeks.
-
[quote user="GeneWitch"]
Are there other variants? for instance, if you wanted to make a GUI, you can use VB.NET or java's AWT or Swing; is there an analogous ... something in databases? the reason i ask is he speaks as if there were an alternative to SQL. :-)
[/quote]
Most databases use SQL, but there are "little" differences between the SQL dialects used by e.g. Oracle and MS SQL Server. Some exotic databases (e.g. object oriented databases) use languages other than SQL. Anyway, if you use a framework like (N)Hibernate, you don't see much of the SQL, since you only use the framework's API.
-
Oh, BTW, going on your description of the game itself, I think you should check out OGame.org an extremely popular german made mmog, all done in php & mySQL. takes a bit to get into but once your well into the game your flying. Socialising with other players in the game is a must for this, and in the forums there are sections on game logic and the equations for some of the various aspects of the game.
WARNING: you may loose your life to this game.
-
from reading through the book, it looks as though most of the data you store in the database isn't strings and integers and such, but rather tangible things like ShipName:shipname("Dawn Killer"), Fleet:fleet("ComStar"), Race:race("Til'Mek"), Where the first is the Type, and the second is the data that goes in the tuple.
That's awesome! I hope that learning SQL after all of this is going to be trivial. I'll check out that RC software, i don't plan on porting this any time soon, and after i have everything i want to put in the database written down on paper (there's a lot of extrapolation that i have to do from our documentation) it shouldn't be too hard to pick a different DB solution, i wouldn't think, and put the data in there in stead.
-
@GeneWitch said:
I picked it up and read the first chapter, and the first thing i see is SQL <> relational model. That sort of worries me.
The author was an inventor of the relational model and he's quite protective of his baby. No implementation is good enough. (Too bad.)
-
@newfweiler said:
@GeneWitch said:
I picked it up and read the first chapter, and the first thing i see is SQL <> relational model. That sort of worries me.
The author was an inventor of the relational model and he's quite protective of his baby. No implementation is good enough. (Too bad.)
SQL doesn't really describe any implementation anyway. Every known SQL-like engine implements a (different) subset of SQL. The SQL spec got committeed into bloat on a galaxy-spanning scale - there's tons of stuff in there for no particularly good reason, so every implementer just does what they feel like and skips the rest.
Which is a real pain when trying to port some code between implementations.
-
@asuffield said:
SQL doesn't really describe any implementation anyway. Every known SQL-like engine implements a (different) subset of SQL. The SQL spec got committeed into bloat on a galaxy-spanning scale - there's tons of stuff in there for no particularly good reason, so every implementer just does what they feel like and skips the rest.
Which is a real pain when trying to port some code between implementations.
What should be implemented is the relational model, not SQL. Maybe that is what C.J. Date is complaining about: taking SQL as the standard and designing a database to match, rather than building a proper database and using SQL as the query language.
It's like computers vs. programming languages. There have been some attempts to design computers based on programming languages (most successfully the Burroughs Algol machines; least successfully the Intel iAPX-432 based on Ada). Usually it works better to design a computer that works well as a computer, and let the compiler writers deal with the languages.
-
Yah, he finally relents in chapter 5, the one about relational algebra. The more i read, the more i realize why this game is going to need a database. I have a question, and maybe this is answered somewhere else in a long fashion, so forgive me (and ignore if you want)
Let's say there's 100 retailers in the universe, and some of them sell a candybar called the foobar. what would the 'recommended way' be to illustrate this in a database?
A) Have a 'table' that shows all available vendor goods, and a boolean for each vendor stating whether that good is sold there;
or
B) having a table that shows all vendors, and somehow describing what they sell there?
In VB i would just do foobar.availibility(booleanArray[]) because i suck at translating these sorts of things to code. If it's cool to have that many columns in a database table, It'd be easy after it was implemented to pull up an inventory screen, select your current vendor, pull up all items from the item table such that the vendor flag for current vendor is true.
This is the sort of weird stuff that has kept me from doing DBA until now. Without explicitly putting the information in either the vendor table or the item table, i'd assume that there's no real simple way of doing this?
In morrowind and oblivion, for instance, items are unique, and vendors are unique, so if you go to a vendor, it pulls up its list of items (which means that they implemented option B above). There's gotta be an easy way!
Edit: I realize that vendorID is going to be the key for the vendor table, so it can be a foreign key in the items table, i'd assume? Can you do {vendor01, vendor07, vendor87} a set as an attribute (tuple)... i'm wondering how you'd derive the table that showed you the vendor/item combinations.
-
B), like so (postgresql SQL syntax, hopefully it should be pretty clear what's going on)
CREATE TABLE vendors (
vendorid INTEGER PRIMARY KEY
-- other vendor fields
);CREATE TABLE goods (
goodid INTEGER PRIMARY KEY,
name VARCHAR(200) NOT NULL
-- other goods fields
);CREATE TABLE vendor_goods (
vendorid INTEGER NOT NULL REFERENCES vendors (vendorid),
goodid INTEGER NOT NULL REFERENCES goods (goodid),
PRIMARY KEY (vendorid, goodid)
);This is a pretty standard approach to many-to-many relationships between entities. To add a good to a vendor you add a row to vendor_goods, and you can get all goods by vendor with something like
SELECT goods.* FROM vendor_goods, goods WHERE vendor_goods.vendorid = 27 AND vendor_goods.goodid = goods.goodid;
-
@Angstrom said:
B), like so (postgresql SQL syntax, hopefully it should be pretty clear what's going on)
CREATE TABLE vendors (
vendorid INTEGER PRIMARY KEY
-- other vendor fields
);CREATE TABLE goods (
goodid INTEGER PRIMARY KEY,
name VARCHAR(200) NOT NULL
-- other goods fields
);CREATE TABLE vendor_goods (
vendorid INTEGER NOT NULL REFERENCES vendors (vendorid),
goodid INTEGER NOT NULL REFERENCES goods (goodid),
PRIMARY KEY (vendorid, goodid)
);This is a pretty standard approach to many-to-many relationships between entities. To add a good to a vendor you add a row to vendor_goods, and you can get all goods by vendor with something like
SELECT goods.* FROM vendor_goods, goods WHERE vendor_goods.vendorid = 27 AND vendor_goods.goodid = goods.goodid;
aha.. sweet. that also helps me with a few other questions i was having.
-
@Hitsuji said:
Another option esp if your using VS 2005, MS SQL 2005 Compact Edition, it's still a RC, with the finished version coming out within the next few weeks.
A.K.A. Microsoft's proprietary Windows-only version of SQLite, which I recommended in the first reply...?
-
@djork said:
@Hitsuji said:
Another option esp if your using VS 2005, MS SQL 2005 Compact Edition, it's still a RC, with the finished version coming out within the next few weeks.
A.K.A. Microsoft's proprietary Windows-only version of SQLite, which I recommended in the first reply...?
From a quick read of Microsoft's writeup of Compact, I'd have to agree with djork on this one; for a ground up project where SQLite or MS-SQL Compact would fill the bill, I'd go with SQLite. The only reason I'd see to go with the MS version would be if you had an existing MS-SQL codebase that you needed to port to a smaller footprint...
But then, I frequently don't know what the hell I'm talking about.
-
Angstrom is correct on the vendors-and-goods example. One rule of thumb is that you set up the database so you never have to change the structure. You don't want to add a new column to a table when a new candy bar is invented or a new vendor signs on.
Some tables represent entities; some represent relations. In the table of vendors, each row represents one particular vendor (one entity). Each row is like an instance of an object. The primary key is the unique ID for the vendor.
In the table of goods, each row represents a kind of candy bar, not an individual candy bar. (Candy bars are an example of a "fungible" good: one 35 gram Milky Way bar is the same as any other. When you lend a Milky Way bar to someone and later get it back, it's not the "same" one but it's an identical one.)
Another table is a relation between those two tables. It holds the information that VendorA sells Milky Way, Vendor B sells Milky Way, Vendor B sells Mars Bars. (That's three rows.)
The "right" way of breaking it all down depends entirely on what you are doing with the information. What is an individual "good" in the table of goods? If you are a candy distributor, each row describes one product (one SKU, or "Stock Keeping Unit") with everything that's constant about that one product. You've probably noticed that SKU's have arbitrary unique identifiers instead of using a descriptive name (which is not necessarily unique) as the primary key. Example: SKU # 1234, Desc: Milky Way 35 gram 50 count English/French, Market: Canada, Mfr 434, Mfr # 8783. (A box of 50 candy bars with packaging in English and French for the Canadian market. Made by manufacturer 434. When you order it from the manufacturer you ask for his product number 8783.)
A separate table keeps track of inventory. You have 4,000 boxes in Warehouse A and 3,000 in Warehouse B.
The manufacturer of the candy has a similar table for each product but keeps track of batch numbers, ingredient lists and so on.
-
To use SQLite efficiently and not produce spaghetti code you'll need to write your own wrapper class or get acquainted with some existing one. The better option for you is probably to write your own, because
- It's a good exercise in C++ (assuming that's the language you're using)
- You won't have to learn how to use it
- You'll get to know underlying SQLite functions better
- You can add custom functions that will cater to your game's specific data manipulation needs
- It's a good exercise in C++ (assuming that's the language you're using)
-
@djork said:
SQLite is your friend. It gives you a small (only 250KB for the engine library itself), fast, and very flexible database with access to most standard SQL syntax. What language are you developing the game in? There are wrappers for it in most popular languages.
I agree on this one!
One added benefit of SQLite as compared to MySQL, Postgres, MsSQL and most others is that you don't need a database server with SQLite. All you need is the SQLite library and it works!
You mentioned VB in another post. If you use VB.Net you can try the .NET driver for SQLite found here: http://sqlite.phxsoftware.com/. I have tested it a little and it works very well!
-
@runegri said:
@djork said:
SQLite is your friend. It gives you a small (only 250KB for the engine library itself), fast, and very flexible database with access to most standard SQL syntax. What language are you developing the game in? There are wrappers for it in most popular languages.
I agree on this one!
One added benefit of SQLite as compared to MySQL, Postgres, MsSQL and most others is that you don't need a database server with SQLite. All you need is the SQLite library and it works!
You mentioned VB in another post. If you use VB.Net you can try the .NET driver for SQLite found here: http://sqlite.phxsoftware.com/. I have tested it a little and it works very well!
The database is eventually going to be centralized to one location, with the game accessing it from another (think World of Warcraft).
i was reading (N)Hibernate's mainpage... it said something about XML and whatnot. I'll look into it.
You guys are a fountain of knowledge, so here's something for you to chew on:
I once had a few programs that would log into a database server, and allow you to inject stuff via a GUI... (i even think it let you dump excell spreadsheets or some variant thereof directly into tables). this was a long time ago (3 or 4 years?)... Between mysql and postgresql (since i want to have central servers as opposed to just using a database locally - unless i'll need to do both... sqlite for local and postgresql for the centralized one) which has the best "free" or "opensource" tools, and which has the best commercial tools for editing the original state of the database? my memory fails me at some examples of what i am looking for, but i want to say that foxpro (which i had for the macintosh, i believe) had a thing where you set up all your rows and columns and gave all the attributes their names and such, and then you would just enter the data in as if you were filing cards away...
The sheer volume of data that we're going to have to create by hand with pencil, paper and notepad is severe, and anything to make putting that stuff in the database would be helpful. and, newf (i keep shortening your name cause i don't want to mispell it) if it was you that said keep things on the problem level... that is helping a lot. i finished that book (database in depth), and i am now reading SQL In A Nutshell 2nd edition, to get up to speed on SQL Syntax, as it has the syntax for several versions of the SQL implementations.
I do know however that a majority of the "putting crap into the database" is not going to be done from within the game program... so if i have to write some sort of specialized application to inject and update the database, i'd like to get that out of the way first, since there's bound to be a lot of content added as i write the actual game - "crap the Grell can't fly in Human Occupation space because the Gleff hate them, so i have to set up a station ... here, and put vendors there and a shipyard" - that sort of thing.
Just so you all know, i come from a psychology and statistics background, with a minor in english, so compressing all this programming stuff is going to be a challenge and entertainment for at least a few years. I can only hope that the HL2 engine or something similar goes opensource around the time i need a graphics engine for the game.
-
We prefer the term "firehose of knowledge".
Pretty much every database server under the sun can import CSV to tables, so you can always edit data in Excel and then import it. It's a bit clunky to go back and forth, but not very hard. MySQL has phpMyAdmin, which you can run on any web server with PHP installed, but then you're saddled with MySQL's copious "quirks" -- a lot of which turn into bad habits very easily. PostgreSQL for Windows ships with PGAdmin, which is a combination of data editing tools and database administration tools and runs as a standalone app (not a web application). It's also available for linux. There's also, apparently, a PHP-based application similar to phpMyAdmin. (Edit: phpPgAdmin)
If you're feeling adventurous you can set up Access as an ODBC client and install the postgresql ODBC driver, allowing you to use access' front end tools to manipulate data in postgres. Don't use this to manipulate the database structure.
Writing a simple CRUD (create-retrieve-update-delete) app to enter data in, say, Ruby on Rails will take a couple of hours, tops, and give you a bit more customization.
There's more than one way to do it.
Amazon.com
