Archive for February, 2012

Crafting database SQL conundrum

2

Hey all!

Ok, so while working on the crafting system for EverSky, I came across this SQL issue and wanted to share it:

I’m trying to create an SQL based crafting system, here are the current tables relevent to the discussion:

Items table:

itemid: autonumber

itemname: text

 

Recipes table:

recipeid: autonumber

recipename: text

 

RecipeIngredients table:

recipeid: number

itemid: number

count: number

RecipeResults table:

recipeid: number

itemid: number

count: number

CraftingIngredients table:

itemid: number

itemcount: number

So clearly, there is a one to many relationship between recipe and recipeingredients, plus there is a one to many relationship between recipe and reciperesults. The crafting ingredients are stored in the crafting ingredients table for convenience, they are basically the items (and counts) current displayed in the craft slots area of the crafting UI.

So the required functionality is that given the items in the craftingingredients data, find all recipe id’s that contain EXACTLY the same ingredients in the RecipeIngredients and ONLY those id’s. So basically exclude any recipes which have any fewer ingredients, any more ingredients, or any ingredients not in the craftingingredients rows.

What I currently have is:

SELECT recipeingredients.recipe, items.itemname, recipeingredients.item AS Item, recipeingredients.Count AS [Count]

FROM items INNER JOIN (recipeingredients INNER JOIN craftingredients ON (recipeingredients.item=craftingredients.item) AND (recipeingredients.count=craftingredients.count)) ON items.ID=recipeingredients.item

ORDER BY recipeingredients.recipe;

Which currently returns all of the rows for any recipe which has ANY matching ingredients. So if you have one of the ingredients in the craftingredients table then the row is returned, which isn’t really cutting it.

 

Now I do have a fallback here, in that I can simply concatenate all of the itemid+count at recipe design time into a big old string blob and then insert that into a simple recipe row with id and blob, then the lookup simply becomes a text comparison, which should work reasonably quickly. But its not very SQL is it? :)

Here is a link to the access 2007 db file for anyone who wants to have a try and play with the issue. There’s a form in there to add recipes (I plan on using access because it has pretty nice user interface stuff for forms design). Final thing uses SQLite though.

 

 

 

 

 

What is player generated narrative?

4

One of the more popular trends in AAA games right now, is the use of linear narrative to drive gameplay. This works well for AAA because with linear narrative, you can guarantee some great screenshots and movies to drive your marketing spend. The current pinnacle of that art I think is the Uncharted series, which tell the story of Nathan Drake exceptionally well.

uncharted image

The power of the Uncharted series is that the developers Naughty Dog pay particular attention to the characterisation of each main character in the game. Using examples from traditional media they cast the Drake character superbly as a Indiana-Jones style adventurer, which makes the game thematically interesting in the same way that the Indiana Jones movies are.

But of course all this lavish attention to the character is costly. Both in production cost and in design effort. So the last thing the developers want to have happen is for the player not to experience these great production efforts. For the most part, this ends up as a strange mix of camera-stealing, design directing the player and general cut-scene usage that delivers a compelling package, but one that feels incredibly restrictive.

The problem with the linear narrative style employed in the Uncharted games is that you are simply a passenger that occasionally gets to interact (often by pressing a single key to progress).

Unfortunately the linear narrative style has sold so well for multiple games that it has become the de-facto standard for “AAA” development. Lately games like the Call of Duty franchise and even traditionally multiplayer games like the battlefield series have fallen prey. It is understandable, given they have to justify huge production budgets and need to ensure that the production quality plays out in promotional materials. But this kind of narrative is not the only alternative when it comes to the player experience.

Player generated narrative

In creating EverSky, the intention is to employ a very light touch so that players can create their own narrative. The key to this is to never take control away from the player to highlight any particular aspect of the world. But that leaves open the question “how do we tell a story?”. The notion here is to convey an understanding of the world and its origin in a way that allows the player to figure it out themselves. In addition we accept that players like to share their knowledge of a world, so we use that to our advantage and enable this shared knowledge as a core gameplay element. This means that players not only create their own narrative, through their own experiences, but help create a group narrative via a shared knowledge space. Our role as designers then is actually to allow for interactions and to offer ways that players can learn and share this knowledge with others.

How does a player learn something without being told?

There are plenty of experience we can have that teach us something without someone actually having to explicitly tell us. As children we constantly experiment and explore the boundaries of our own limits and we seek to replicate this in the game. So things like trying out new artefacts to see if they have some effect on the world, sharing that with others and exploring the space of things like trade and crafting can help. Having a consistent design philosophy and set of themes that underpin the design choices is also useful, in EverSky themes such as “exploration” and “entropy” guide the choices that affect player experience and world design. Similarly the inhabitants of the world are crafted to allow them a level of autonomy that is unpopular in linear narrative games. This allows for some level of emergence, but also promotes the notion that the characters are actually self-aware and have their own purpose to be in the world. Taking this “world centric” rather than “player centric” view of design is definitely not in keeping with the linear narrative style.

As a boost to people who prefer linear narrative, we can also seed artefacts that may be activated by the player to impart knowledge or produce an effect. Similar to picking up a book and deciding to read it, we give the player choices that let them participate in the designed narrative, or simply ignore it and continue with their own narrative.

Sandbox play by it’s nature is about exploration of possibilities, so we embrace that concept with mechanics that allow players to explore the world and its nature. In essence the world itself becomes a big playpen with learning part of the core of the experience.

Does it work?

This is the big question and one that is as yet unanswered. As we get further along in production we might have a better feel for the outcome of this style of design. The feeling so far is that it will be a problem for a set of players very well versed in being told exactly how to experience a game, however there are players (such as those that prefer real “roleplaying”) that are more open to a more sandbox play style. The wider question of non-linear narrative still requires further experimentation within the game world before we can definitely say that it does work as intended.

You can follow development via youtube to learn more:

http://www.youtube.com/zoombapup

Or get in touch via twitter @zoombapup

Thanks for reading.

.Z.

Go to Top