• Welcome to Final Fantasy Hacktics. Please login or sign up.
November 28, 2020, 05:55:36 am


Please use .png instead of .bmp when uploading unfinished sprites to the forum!

@Lirmont: RE Database Design

Started by Pickle Girl Fanboy, May 07, 2012, 06:27:03 pm

Pickle Girl Fanboy

I had some kind of flash of intuition last night, and I realized that having a database which users can script to develop specific game mechanics is a good idea, and should be built into as many FOSS game clones as possible.  Since I want to clone a few games myself, I figure I'll ask you to tell me more about it.

The following is for a clone of The Legend Of Zelda: A Link To The Past and Link's Awakening, but with job classes.  I'm not actually working on it (or anything else) right now (because I can't program yet), but I woke up with this bee in my bonnet and you're the most qualified person I know to answer questions related to this.

Actor Database
*Actor Stats /*Create the stats which will be given to each actor.  This doesn't actually do anything about what these stats do, it just gives them a name convenient for you and the database to remember, and tells the game what the data type for that stat is.*/
**Stat 1 /*Health.  2 bytes.*/
**Stat 2 /*Mana.  2 bytes.*/
**Stat 3 /*Strength.  1 byte.*/
**Stat 4 /*Intelligence.  1 byte.*/
**Stat 5 /*Agility.  1 byte.*/
**Stat 6 /*Can equip Knives?  1 bit.*/
**Stat 7 /*Can equip Swords?  1 bit.*/
**Stat 8 /*Can equip Spears?  1 bit.*/
**Stat 9 /*Can equip Hammers?  1 bit.*/
**Stat 10 /*Can equip Rods?  1 bit.*/
**Stat 99 /*Spritesheet.  1 byte.*/
**Stat 100 /*Palette.  1 byte.*/

*Actor List /*Create an actor, and give that actor a name to use in the database.*/
**Actor 1 /*Player Character, no job.*/
**Actor 2 /*Player Character, Knight.*/
**Actor 3 /*Player Character, Wizard.*/
**Actor n

Item Database
*Item Stats /*Create the stats that will be given to each item*/
**Stat 1 /*Item type (Sword, Knife, Spear, Shield).  2 bytes.*/
**Stat 2 /*Other things*/
**Stat n

*Item list /*Create an item, and give that item a name to use in the database.*/
**Item 1
**Item 2

Formula Database & Scripting /*A bunch of simple formulas (basically, all the formulas you'll find in a spreadsheet, maybe with some other stuff you'll need).  I'm not sure if it would be a better idea to just have no restrictions on what the modder can do here, or to force some structure or rules or something.  I'm not sure if it makes sense for this to be a database, an unorganized bunch of scripting, or some combination of both.*/
*Let's say I want to tell the game that if an item is a sword, then knights can equip it in their weapon slot.  Does it make more sense to create a list of possible values for Item Stat 1 (item_type) and to specify possible values in the item stats database, or to leave the whole "Possible Values" thing out of the Item Stats database and do it all in this database?  This is a question that applies to all databases in such a game, by the way, not just the ones listed here.

Also, should the scripting all be done in one place, or is it a better idea to split it up into pieces spread all over the game, in their most relevant database?  In other words, is it better to have one gigantic database for all the scripting that a modder can access without modifying the game engine; or is it a better idea to have many small scripting databases, dumped into their relevant category (Item database - scripting, Actor database - scripting,...), and then have a smaller centralized database for game mechanics that link categories together?

The goal being to make something that is inherently easy to maintain (for a newbie modder), where it's easy to make the best decisions, hard to make poor decisions, and easy to fix when you do make poor decisions.  And of course where you only need a spreadsheet-level knowledge of programming.

I don't like hiding complex things from newbie eyes, but I do like reducing tedium.  Complex doesn't have to mean tedious.  It should reward people for doing more with fewer lines of code, and maybe teach them something in the process.


Well, I think the most important thing is what you mentioned last. Basically, your skills exist in the realm of spreadsheets, as do a lot of other people's skills around here. So, what you would focus on is a small amount of programming (say, less than 500 lines) that uses someone else's library (Microsoft has interoperable libraries for its office software available in .NET) in order to use your spreadsheet data. This is the same thing Tethical is doing, except a MySQL database takes the place of Excel, and the library to read stuff in that database is Oracle's MySQL driver (versus Microsoft's Interop library for Excel). There's very little difference to the process here. Also, if you use something other than Excel for spreadsheets (i.e. OpenOffice), you'd want to use a library that's been developed to read their stuff; writing file-level code to read these files is not work you should have to do; there are libraries available.

As to the other questions, you've skewed your ideas to be more database-oriented than Excel spreadsheet oriented. Basically, as long as you have a unique place for your information, that's all that matters. It's all that matters because that's all that it takes to reference the cell the data is in. After that, you can ask the software that's reading the file what the data type is (and Excel, to my knowledge, doesn't let you stray far enough from the beaten path to make length apply there).

If it were me, here's how I'd organize it with the stipulation that I wanted to use Excel to accomplish this:

  • Data Structures (XLSX)

    • Actors workbook (put an example character's workbook here for copy/paste into the actual XLSX used by the game)

    • Items workbook (put an example item's workbook here for copy/paste into the actual XLSX used by the game)

  • Actors (XSLX, each workbook here could be treated as a referenced character)

  • Items (XSLX, each workbook here could be treated as a referenced item)

  • Formulas (XSLX)

    • Battle Formulas workbook (specfic cells are referenced and used in-game)

    • Job Formulas workbook (specfic cells are referenced and used in-game)

Basically, you're just using someone else's library to reference cells with your programming after you get all that set up. Go out on the internet and find one of these libraries for whichever programming language you want to use, and then try to get data out of a single-workbook spreadsheet. Once you've accomplished that, I can provide you with more directions.

As for scripting, putting too much stuff in one spot will ultimately confuse you later. Split things up into their own named files, and use the filesystem to categorize them. Your other assets (XLSX files) will just be referencing the file names of those scripts, so you don't have to worry too much about finding the things in-game (though you should check to make sure the thing exists and is a valid script first). This is what I did for Tethical. Because it has a client and server component to it, the events are sorted into at least those two folders. After that, it's up to the designer to sub-categorize by adding more folders. Then, your assets (XLSX files) just have to reference the file name of the script they want, and the code can load them at runtime. Of course, if you're not using a real scripting language to begin with, you'll have to do a lot of extra work here. Scripts in Tethical are loaded with about 5 lines of code, several to check that the file exists and can be loaded, and one to load it at the right time during the program flow (this step also runs it after it's loaded).

As a point of differentiation, Tethical could have gone this route (using spreadsheets), but a triple-A budget game like FFT (which Tethical seeks to emulate) is too complicated to depend on your own methods of combining data. Real database software helps you do this step, and, most importantly, it helps you do it fast (re: response time). No matter what, you're responsible for doing meaningful things with the data. For simple things, spreadsheets may actually be the perfect idea: they're organized, they're quick to load, they're quick to edit, and you can ask other people for help on getting more complicated features working (re: functions). In Tethical's case, there's too much stuff to make it a good design, but that doesn't mean it's not a feasible design.


@lirmont: can we say that the scripting in spreadsheets is comparable to our database named procedures?

@PGF: there is two open source project you could fork or contribute if one day you want your dream to come true.
- the mature http://www.solarus-games.org/ you may already know
- a very young project http://www.youtube.com/watch?v=wFvAByqAuk0&feature=youtu.be http://blog.sujets-libres.fr/?p=1134
Tethical, an online FFT clone


May 08, 2012, 09:20:00 am #3 Last Edit: May 08, 2012, 07:17:22 pm by lirmont
@Kivutar: I hadn't thought of that, but yes. Conceptually, that code acts when called just like a database's named procedures. In a spreadsheet's case, it's always in the context of a cell (unless you call it directly from programming, which I have no experience with). A lot of the benefit I see with the spreadsheet approach is what databases refer to as "materialization", which, in plain terms, means that "what you see is what you get". In other words, you control the end result right there in the spreadsheet, and you can see the end result right there in the spreadsheet. Again, for less complicated things, great approach.

Pickle Girl Fanboy

May 08, 2012, 06:46:26 pm #4 Last Edit: May 08, 2012, 06:55:00 pm by Pickle Girl Fanboy
Well, all the simple scripts are meant for the modder to write as the want their mod to operate.  There's no point in hard-coding them.

Basically, I woke up one day with your Tethcial database intermingled with all my other modding ideas.  The idea fairly took a shit on my head.

I once heard spreadsheets referred to as an example of a functional language.  Is this WYSIWYG or "materialization" a related concept?


Yes, the idea of a functional language and database table materialization is the same. All data is already combined and calculated at that point, regardless of how it is actually stored. By comparison, it would be more like high-level programming languages if you were able to edit your spreadsheet (or materialized table) while the game was running and have that effectively cause different states of values to occur (but it would still ultimately fall short of the idea because it requires the outside help of such a language in the first place).

Pickle Girl Fanboy

Quote from: lirmont on May 08, 2012, 07:25:26 pmBy comparison, it would be more like high-level programming languages if you were able to edit your spreadsheet (or materialized table) while the game was running and have that effectively cause different states of values to occur (but it would still ultimately fall short of the idea because it requires the outside help of such a language in the first place).

If the game engine itself isn't hardcoded to use anything, and the player can edit scripts for, say, making a reaction ability while the game is playing, isn't that kinda like an interpreted language?  But that would slow down the game, so it should be some kind of debug mode.


The scripts you're talking about would be interpreted (if they aren't ever compiled into a binary representation). Setting up your engine to respond to a key stroke (or use a timer) is pretty trivial, but you have to be careful about making sure your program doesn't prevent the files it's reloading from being edited (by locking them and never letting them go).