Initial Database Schema

[eralston]

Since Iscarot is (or at least was) making such headway in the UDK, I figured I should start from the place where we already have some work done (the C++) stuff and work toward his accomplishments.  I have put a little work into the initial database schema for the World Server, thinking this will give me insights on what needs to go and what needs to stay in the current codebase.  I have taken a screenshot of my current work and submit it here for review.

A few observations:

The schema focuses on just ideas and relationships at the moment.  There is very little actual data held in each table, so the description of intended data is entirely dependent on connecting the name.  If a table doesn't have an obvious function from its name and context within the diagram, please don't hesitate to ask.

The "Character" concept tracks all of the relevant SR character information for both PCs and NPCs.  This means we don't have to repeat relationships and data for each type.
 
The active skill, knowledge skill, gear, and spells table represent unique definitions (such as for the Fireball spell or the Ares Predator IV).  The instances of the definitions in the world are held in the various character_* tables.

I am on the fence about it, but currently I am thinking the Gear table will include cyberware, bioware, and nanoware.  For the data, they are items that are part of the player's gear and able to confer bonuses or penalties, the idea they can never be put down is really a presentation issue.

The "story" idea is intended to hold data for all three levels of Mission/campaign/storyline.  From a data perspective, all three phenomenon are truly the same: they each may have scenes, the each have objectives that need per-player data instances (pc_objective_data),  and they each need per-player instances of data to track them (pc_story_data).  Sub-stories enables you to make the necessary composition of stories (so a single mission is a sub-story of a campaign, a single campaign is a sub-story of a storyline).  For the data's sake, there is no reason to bind this to three levels.

Conversations are composed of one or more "Statements" for the PC or NPC.  They relate to each other using the statement_decisions table, which is intended to hold the possible PC and NPC responses.  Either statements or statement_decisions will need to track the prerequisites for a statement (EG some options may only be available if the NPC is racist or the PC has a certain skill, etc).  I don't think the table will encode this into a queryable quality, perhaps just a string description that the system can parse and then connect to the meaning of in C++.

Conversations currently stand alone, even though they have enormous bearing on scenes and NPCs.  I am unsure if we need a connection in the database because the strategy for creating conversation instances may be held entirely in script in the database.

Zones are related with a list of connections.  This will need to be supplemented with some sort of identifier for the entrance/exist in the Unreal level (IE spawn points where the players enter and trigger volumes that cause the player to leave).

[eralston]

Find attached a revised draft schema, this time in PDF form given that a screen-cap is not possible given the new size.  A few key differences:

1) Several tables have a "game_effect" column.  I would envision this column either holding onto a reference to a script or the script itself necessary to mutate the owner, or their equipment, or provide the system with a definition for the item.  For instance, a spell script would set the parameters for its visual effect, game effect, and other ad hoc qualities.  This is currently an integer since I don't quite know what it will be.

2) Several tables have a "data" column.  I envision this column holding onto free-form data, potentially a whole dictionary serialized into memory or the like.  The important this is that this will hold variables in the database for the related items.  EG, objective data could be used to count how many items out of a set have been eliminated, for instance, 4 out of 5 rat pelts recovered, etc.

3) I have broken out "Augmentations" for a character into a table separate from gear.  This should enable the system to determine a character's essence loss by simple inspection.  I may end up also doing this for weapons given how complex they are.

4) The columns for most tables have been filled in a little more.  I would generally recommend having more selects in the system go off of views as opposed to the actual tables.  That would allow for the related items, such as spells, skills, and gear, to be presented with their definitions as opposed to across two data structures.  The views would also enable such things as making character essence, initiative, and other derived qualities exist only in the view, so they can be calculated upon selection.  For instance, when selecting a player-character, the essence could be separately calculated as 6 minus the sum of all augmentation loss.  This would prevent errors accumulating by storing computed values.  Such a strategy must also be balanced against what aspects are computed about the character upon bringing them into the game, for instance, total character damage tracks might be best done in the engine so they can be manipulated in the config files, etc.

5) I severed the zones from the stories, placing them by themselves.  This is because I am unsure if a particular mission will truly specify a zone in the server or only the zone in scripting.  There was also talk in the past of putting a layer of indirection between the concrete zones (which the zones table is intended to be) and the concept of describing the area to a scene.  This would mean that the same scene definition could ideally be used in multiple zones to enable randomization of actual location.  A perfect example might be storing a standalone scene of a random go-ganger attack.  Any place with roads might be suitable to host such an encounter, so tying it to a particular zone might not be a good idea.  This means we, in the very least, need another table between Scenes and Zones, if we choose not to leave it up to script entirely.

[Shadowrun: Awakened Forums]

[lobon]

In relation to conversations I am wondering (and will now begin showing my lack of programming understanding) will we have the round robin conversation set up or will we have the progressive conversation set up.


Round robin being the player can just cycle through all the options in the conversation - I am thinking this would be best for giving information such as background and information based on a good reaction or high skill check. If so we should have it listed in a white color in the interface.

For the progressive I am thinking it should be in a different color so as to let the player know they are making a choice. Say green or red.

For example:

Player 1 is talking to Mob Flunky 1 and the initial conversation gives 3 options for player response: Snarky, neutral, positive. Once the player makes their choice they cannot go back and the reaction of the NPC is influenced by their stats, standing, rep, skills and attitude. This would cascade down the conversation tree and direct the player experience. Hence if the player has a low rep and is snarky the Mob Flunky decides they can go get their hoop fried and fails to share additional details of the mission.


Direction?

[Eggmunkee]

Regarding attributes for a character, there may be additional values that should be kept, at the very least something to distinguish between the current augmented (as in the accumulation of all effects) value. On the other hand if you want to put that logic to be calculated in a view, it would need to take into account the metatype for min/max values, and check for any gear, augmentations, qualities, etc that would effect the given attribute. I guess I think that since those values would seldom change, it might be worth storing the current value directly. It could be done in stored procedure. So it could have Strength_Min, Strength_Max, Strength_Base, Strength_Current for Strength. Seems like a lot, but that was my thinking.

Second, where do transient NPCs within missions fit into the DB schema. Say a mission calls for two thugs to jump the PC when he meets at an agreed upon location. Are those characters created in the DB with their own skills, qualities, gear, spells, lifestyle, and augmentations each time a PC gets to that part in the story, or would there be a system of template characters which can be referenced and modified? Each time they will start with the same Health, gear, etc, but they will each (hopefully) lose health and die each time that mission is run depending on the outcome. It seems like there could be a referenced template character and then another table which just has modifiers for attributes. I suppose if a particular NPC instance had an extra piece of gear or skill (something determined dynamically), they could have their own extra rows in the character_gear or character_active_skills tables, perhaps. I don't know the best way to do this, but I wanted to raise this possible issue.

I guess more concisely than I just stated it would be to say, what is the general idea between what is stored in the database and what is stored only on the engine side. I think clearly things like graphical effects would be engine-side only, but what about ammo? Character's exact location and rotation? Are all these things going to be constantly saving to the database for every character, every NPC, every Grenade lobbed, etc. What is the general divider between what needs to be persisted constantly and what is transient data. I know all these things will already have to be communicated from client to server to run the state of the game, but wonder how far this extends to the DB storage.

Can you expand more about the total damage tracks being manipulated in config files? If you mean have the total be not just calculated based on attributes, I can see how there would be a use for that, such as bosses that have some unnatural aspects such as a higher than calculated total stun track. Thus it could be overridden with script, but I wasn't sure where a config file would come in to changing total damage track amounts.

You are doing a great job though, as always. My comments only aim to assist if possible and help me understand the gameplan or concept.

[eralston]

the initial conversation gives 3 options for player response: Snarky, neutral, positive.

Given a sufficiently detailed description of the desired behavior, we can make the database do anything.  It's good to talk about mechanics, and especially examples, in this thread to better the design.

I am for having PC responses be listed as a summary of the sentiment as opposed to the whole statement for brevity.  Mass Effect has this and I found it enjoyable.  I also think this plays into the "decisions NOT management" mechanics goal.  I would like to still annotate things with an indication of what skill they are based upon.  An example might be:

NPC Johnson "So I told you all the specifics, do we have a deal?"

1) I'll do it
2) No deal
3) Let's talk again about the cred (negotiations)
4) Give me more cred! (intimidation)

The other aspect you touch upon, and something that is well done in ME, is that the "information" conversation options are presented separate from the "decision" options.  This makes it so some player can decide immediately while others choose to delve deeper into info, coming back to the decision after learning more.

what is the general idea between what is stored in the database and what is stored only on the engine side

Great jumping off point.  I have been working under these expectations (which I have been analyzing throughout the process, so they are not set in stone).  The part we'll call "database" is any stored procedures, functions, views, tables, etc that we build to hold and represent data.  For "engine" we can include Kismet, UnrealScript, C++, or even further scripting languages like Python that we may integrated into the C++ part of the engine to support customizability.  For me, the responsibilities divide as follows:

Database

• Per entity data (entities being: players, NPCs, missions, etc) - Data that must be stored on a per-entity basis where we have several entities should go in the database.  Character information is obvious (since we will have thousands of players one day), but things like factions.  Also, data that must be related to one of these entities.
• Data that must be shared between player sessions - Data that a user creates or consume on Monday night that must also be there when they log back in Tuesday morning.  Things like current character stats, storyline progress, contacts & enemies, etc, are all things that must be tracked over time.
• Data that must be shared between zones - Data that follows a user when traversing zones must be kept.  This includes inventory, perhaps sustained spells.
• Data we wish to control over time - Keeping everyone's copy of the engine in sync is more challenging that just changing database data.  That is why it is attractive to store things like mission and conversation definitions in the database, where we can add, remove, or change them at will without forcing people to patch the engine.
• Statistics - Any metric we want to apply to the player or character population (average strength attribute, character with highest karma, player with most friends, etc) we need to store in the database so we can make pretty SQL queries instead of ugly coding loops.  The place where this gets questionable is things that have potentially myriad effect, like cyberware on PC attributes, we should want a flexible definition that fits in a single column; however, such a definition is unlikely to be possible to unravel during queries.  This is quintessential for improving the game over time, as well as monitoring the community in general.

Engine (UnrealScript, C++, or TBD scripting languages)

• Universal Constants - Things like the coefficients of game rule equations (EG how many physical or stun boxes does a character have, how many automatic hits a skill rating will give a character) are likely to be used often and pervasively in the engine.  Especially constants used in operations that would not otherwise force a database call.
• Values Changed by Script - Characters represent a whole stack of effects (cyberware, spells, gear, adept powers, qualities, etc) laid atop a set of core stats.  Since the definition for the various abilities are likely to be held in script, and script can only be executed in the engine, a lot of character stats can only be determined by the engine.

Again, these represent the areas I've identified so far, not all possible areas or even the big gray stretches between.  The big grey area, IMO, concerns the capability for each side to cache values from the other.  For instance:

• Some universal constants in the engine could be queried from the database when a UE3 server is started, enabling the database to act as the total configuration for a server.  The client would be updated either via its connection to the world server or via the server.
• Some values calculated by scripting could be cached in the database when characters leave zones (or other conditions), enabling the zone transitions to be more expedient and leaving re-calculation only to events that provoke one (such as when cyberware is installed, adept powers are activated, etc).

Both of these could be useful and both subvert solid lines between areas.  I think both could be included given enough popular support and definite improvement from them.

where do transient NPCs within missions fit into the DB schema

Well, if you think about it, all of the Character definitions are really templates which the game engine uses to instantiate character models with supporting stats in the game world.  One could just as easily create 100 copies of a character as they could 1 using the same database data as create one.  My intentions is to have NPCs be largely represented by a relatively small number of character definitions that are instantiated as needed.  For instance, you could make a character modeled after the Fixer in the SR4 manual and it would appear once in the Characters table, completed with associated skills, gear, etc.  Then, you could have many entries in the contacts table that reference the single Fixer definition, but are in fact one instance per player character that has a fixer.  The contacts table in this design would store the per-contact information like the NPC's name, faction associations, and other info.

It grows a bit more complex when it comes to NPCs that may traverse zones (such as an NPC that could chase a party throughout Seattle) or related to missions.  I think they will need to be tracked much in the way of contacts (a table that holds onto per-instance information, ultimately relying on a shared character definition underneath).  I am up in the air as of this writing, so feel free to interject.

Can you expand more about the total damage tracks being manipulated in config files?

This is related to the "universal constants" thing mentioned in the database vs. engine discussion.  There will be some formula somewhere in the engine that looks at a character's cumulative stats and spits out the number of physical boxes.  The constant numbers that might appear in such a formula could appear in either a config file or config section of the database, allowing for us to adjust difficulty settings or otherwise tweak gameplay by modifying the constant instead of revisiting the code.  Currently, most of these constants appear in the old OGRE code in the SrConstants.h and SraConstants.h files.

Keep asking questions and baking your noodles.  I know mine is still frying since it feels like this really sets out to be the sum of all mechanics knowledge in the game.  I will likely still start playing with C++ code for it a bit to get a bit more practical in my designing, as opposed to just twiddling on theoretical tables.  Obviously, the database can be designed and revised into the future while we build up core data features.  So, things like defining characters is a very NOW problem as opposed to perhaps defining conversations, which is a LATER problem.

[lobon]

In general terms NPCs can be formed off a template with very few interchangeable attributes. While this might seem limiting at first look at it from a storyline point of view.

Thugs are a dime a dozen. There are many who claim to be Shadowrunners but most do not make the cut. They are small time and happy to get their small cut of the creds. So by creating an archtype not unlike the base NPCs found in the books you could put a bit of randomness in by having a few things randomly selected while the character stays the same. Say their wardrobe, head and weaponry get selected randomly and the rest is the same. Visually and conceptually you get a somewhat different interaction with the same mission and different versions of the NPCs. Now take into account that the story will have them hitting different marks and cues in different missions and you acheive a higher level of diversity with a minimum of effort.

I see this alot in Star Trek Online where the ships are pretty much the same however the missions are different enough that it keeps you ingrossed.

[eralston]

Find attached the latest iteration of the schema (this time in MySQL Workbench because it has a couple little features more than SQL Architect).

It includes a separate Weapons table and association for characters.  I've loaded data for initial spells, skills, qualities, lifestyles, and races.  I am working on gear & weapons, but things continue to be a little slow.  Of course, the fundamentals of the C++ data access stuff is currently in SVN.

[Tharbas]

I think we will need an additional character attribute called sth. like  'currentZone' which is the id of the zone the character is currently located in.  Or do you already have this in your schema and i just cant find it?

[eralston]

We could store the current zone on the server.  At the time, I was trying to gloss over what I considered details.  I think this would be something that stores the current zone and a location to determine spawn points, no?  Seems like we might need two fields then, one for zone and one for a spawn location within the zone.  We'd need one sproc for reading and one for writing the location.  If you need it now, I can make it, just tell me what you want for columns.  If this is just for later, then duly noted.  In either case, way to burn that noodle 

[Tharbas]

Oh right i forgot about the location, currently i only need one column that stores the last active zone for a character, so if a player is connecting we know where to start. (We could use the location too, but i think that will need some unreal script coding to move a character to an arbitrary location in a map without using the predefined spawn points)

For testing my stuff i am using a constant zoneID and i think that will be okay unless we have at least two different sra zones, so no need to hurry