I've decided to venture into the world of database design as I have the urge to build a web-based content management system. This application would specifically tie into a tabletop game (not of my own devising - this is another company's IP and most assuredly done without their consent or knowledge) and would be meant to track campaign play for the game.

I've chosen to write up a list of table and column names in a spreadsheet (not in a traditional columns-by-rows layout) with descriptions and sample data. If I so choose, I could transpose some of those tables into a new sheet and populate it with data to see how it might look as the result of a query. In the initial stages of development, I'll settle for any level of normalization but the cleaner I can make this thing the better. (Obviously!)

Despite the fact that I work with databases to some extent at my job, I feel like a complete and utter newbie with all of this. I sit there and look at what I've written and start asking questions, "Does this look right at all? Can I break this down into even more separate tables? Is that really the only unique item in the table?"

So, before I get to questions about specific tables in my schema or design/code questions, I have one really general question:

What's your preferred method of laying out a DB schema?

I thank, in advance, anyone who responds in case I don't get back to this site in a timely fashion!

I'm not sure if a content management system is what you need but don't have enough information about your requirements to be sure. There are freeware CMS systems available which might help you but I haven't tried them.

Before building any system it's worth asking yourself whether anyone else is likely to want to use it. If the answer is "most likely just me" then it might be worth continuing on with paper and pencil. It takes time creating the database, providing a front end and then getting it all to work correctly.

It wouldn't of been a bad idea providing the field names and perhaps the name of the game so we can get some idea of context. As it stands I can really only suggest you look up third normal form and use a paper and pencil.

Mike

The game is Necromunda, a table-top game from the company Games Workshop. The usage would primarily come from myself, although several friends would have access to it as well. In all honesty, I sincerely doubt anyone else would ever use it, but I would definitely make it accessible to the public... When it's finished.

I work with a database at work - MediaPlus Circulation from DTI - but that really doesn't involve any database design or even any true SQL queries. Their software has a built-in query builder, although it's becoming far too limited for my needs. I don't have the authority to just go and write straight-up SQL queries, despite generally knowing how! I think I get 1NF and 2NF no problem. 3NF is where I begin to fail.

It's worth noting that 1) I am expanding on the original content and intention of Necromunda with this campaign manager (which I guess could also be abbreviated as CMS!), 2) I intend to build upon it for bigger projects, and 3) this is ultimately a learning experience to springboard my way into other database-driven projects (both for work and personal use).

I don't know how familiar with the original game many people are likely to be, but here's a brief overview:
The players take control of gangs in an underground city set in the Warhammer 40,000 universe. The gangs are represented with miniature figurines, moved around on a tabletop amongst terrain pieces. You roll dice for things like shooting and combat. While you can play one-off games, the real heart of the game is in running a campaign and actually advancing the gangs by earning experience and gaining abilities (not unlike an RPG). This campaign system inspires a lot of trash talking and showboating, naturally.

Although we've taken a long break due to work, relationships and family, my friends and I are die-hard fans and have been playing the game since it was originally released. I'm basically looking for a way to NOT use pen and paper and this CMS seemed like the best solution.

Some of the ways I intend to build upon the original game design are with tracking statistics not normally paid attention to. I don't want to go too far into discussing the front-end/application part of the whole project because, well, the database needs to be made first! The basics of the stat tracking would be things like win/loss records, how effective gang members are (ie, shots fires, hits made, wounds caused, etc). Normally, beyond keeping track of wounds caused for earning experience in a given game, the game has no need for you to keep track of these things. With the plans I have, despite the scope of the stats, tracking them would be very simply for the end user.

Some of the hardest decisions I've been having to make are, basically, how much do I want to break things down into different tables. Obviously, normalization is crucial, but sometimes I wonder if it'd be advantageous to go even further than that. Would I be adding undue complexity or extra flexability?

Let me throw in some sample, work-in-progress tables and you guys can tell me if I'm WAY off base or not. I will present these NOT as SQL, but as a list of column names, the very generic data type, and whether or not they are some kind of key.

The columns that would clearly be updated regularly, such as GamesPlayed and ExpEarned would not be calculated by the database. The front-end would do the math and plug the numbers back into the table. Unless, of course, that turns out to be monstrously inefficient in terms of resources.

The idea with this table is, rather than store the values for a given attribute (such as Weapon Skill) in the data for a given gang member, they are stored here and linked to the GangMemberData table with a FK. Similarly, the starting and max values for a given gang member type (Class) are stored here and linked to that class via a FK. MinValue and MaxValue may actually be pointless. Min is always 1 and Max is always 10. That can be checked via code in the application-end of the whole project and raise up any necessary warnings or errors or potentially force data back to an acceptable number.

This is the type of table where I get confused. If there are 20 classes throughout the game, you're going to have 20 instances of each AttributeID and that makes me think I am either doing this backwards or just don't know what I'm doing! Hahaha.

So, to wrap this post up, this confusion and lack of knowledge is why I intend to lay the whole thing out first before even beginning to code the DB.

Here are two more sample tables to show that I clearly need more practice:

Those ~Equiv columns are probably going to be removed. They may come in handy for some aspects of the game (the LeaderEquiv gets challenged in a Leadership Challenge, for example) and maybe for the unnecessary statistics tracking.

If you want me to write these up as SQL, with sample data, let me know!

I was hoping someone else might chip in and suggest a design as sadly I just don't understand your data. My guess at your tables would be Players, Campaigns, Gangs, Houses and Equipment. Next assign each field to each table. If a field isn't directly linked to the key of that table then it probably needs a new table.

I just can't help feeling that you'd do better sticking with paper and pencil or perhaps using a spreadsheet but that's your choice - it just seems like a lot of effort producing a database (the easy part) and a front end (the hard part) to allow users (yourself?) to enter data and get similar (the same?) functionality as the paper system you use at the moment. Obviously if it's just done as a learning process then I'd suggest reading up on 3NF and doing a few tutorials on the net.

I assume you're familiar with building applications?

Mike

Mike, you're probably right in that this may be a fruitless effort. I have bigger plans though (who doesn't?!), and I just feel taking the time to do this would be worth it in the long run.

Yeah, I clearly need to read up on 3NF. As for application building, I've done some C++ and ObjC (nothing complex) and a few simple PHP scripts. I've dabbled in some other scripting languages and I do a lot of formula writing in Excel for work. Oh, and some SQL. I don't get to write queries directly against our database, which is annoying. But I can come up with queries and pass them to off to another team.

There's no doubt in my mind that this'll be a serious challenge and I fully expect it to take me well over 6 months to complete. Realistically, with the amount of time I'd have to devote to this, it'll take well over a year. I don't have a programming team, but I will have access to some help and a pool of beta testers. Initially, the application side of it will be somewhat simple. Bare-bones GUI, no concerns for color schemes or layout. I'm a big fan of CSS whenever I do find the time to do web design, so the final product would be pretty (I hope) but probably not IE-friendly.