Game Basics SNG Data Table Planning
Hello
I am Park Hyung-Seon, from SNG data table planning
Let's begin the SNG data table planning lesson
Writing a Data Table
First, let's reverse-engineer an existing data table
You will probably already know
about the developer Supercell
They are the developers of the game Hay Day
Its name is a pun on the phrase 'hey day'
using the word 'hay' instead
and it is a peaceful farming social network game
Now let me explain what
a data table is
To develop a game
you need the plans for the rules of the game, its UI
and a data table
A data table is all of the attribute values
that go into the game
organized in a table
Then now, let's try creating a data table
for the game Hay Day by Supercell
In order to write a data table
you need to know the various values
that exist in the video game
You can do that
by either searching for datamined content online
or finding the values yourself
as you play the game
To do it the easier way
you can just search the Hay Day wiki
This is the Hay Day wiki
If you go to the Crops page
you can see a list of all the crops in the game
and the level they are unlocked at
There's wheat, corn, soybeans, candy canes
carrots, indigo, pumpkins, and so on
Though there are a lot of them
let's first take a look at wheat, which is available from the start
If you click on wheat in this list
you will be directed to its page
which additional details
The level it unlocks at, min/max price, time it takes to grow
experience, source, and default price
These are all of the values
that are associated with wheat
So you can get all of this information
like the attributes of wheat, what level you get it at, the gold it will earn you
how long it takes to grow, how much experience it yields
and where you can plant it
simply from its page on the wiki
Attributes such as unlock level
make up the columns of the data table
Using the information we have
we can now write a data table
Let me first explain to you about columns
In an actual developing environment
you will have to communicate with the programmers
You'll ask them each time
if you can add a column, edit it
or if it's okay to delete it
To put it simply
If there is a spreadsheet with rows and columns
rows go on horizontally, and columns go on vertically
Rows are horizontal
and columns are vertical
A single column represents a single attribute
so when an attribute is added
The spreadsheet will get longer horizontally
One thing to watch out for
is that the more columns there are
the more database capacity the table will take up
So you should be careful not to add too many
When writing a data table
you have to use your head
and design it in a way
so that it uses the fewest columns as possible
But the size of data tables
don't depend as much on their vertical length
so even if a game has over a thousand quests
because that's 1,000 rows and not 1,000 columns
it won't cause a problem
But if the table has 1,000 columns
it will take up so much data capacity
that it will become impossible to even save
That is why you should be very aware
of the table's horizontal length
Now let's write
a data table using the data
that we acquired from the wiki
Attributes of the crops, such as production time and experience yield
make up the columns
and are placed on the topmost row
After the columns are created
you can fill out the attribute values of each crop
and write the data table
Each column is a type of information or attribute
that items or game content will have
Also, values that are likely to change
as the game is played
also belong in columns
For example, number of crops produced, or crop price
since such values will change as the game is played
You also need ID numbers
because if you don't distinguish between things as you develop the game
The client server will have no way of knowing which is which
so you must enter IDs as well
Elements of the game that contain a lot of information
such as items or characters
especially require them
Now let's learn about the data values
Data values mean the actual values
that are entered into the cells of the table
such as the crop's ID, name, and unlock level
When entering data values
you have to make sure
they are the correct variable type
This is an example of a data table
ID, Name, Lv, Exp, productionTime
those are the name of the columns
When you're starting, there's no need to think too hard
Just think of it as making a regular spreadsheet
The first thing you need is the ID
You will probably use alphabets or numbers as IDs
but using numbers is the most optimal
so I used numbers in this example
But one thing of note
if you used
ascending natural numbers, starting from 1
and going up to 2, 3, 4, 5, and so on as the ID
is that there will be dozens of other data tables
as well
If the same numbers are used elsewhere
it will be confusing
and you might mess up the code
because there are different elements with the same ID
and that might cause a bug
So when you're using numbers
for the IDs in your data table
you should create your own set of rules
to make sure they don't overlap
This table now has data
of various crops
And the columns consist of
ID, Name, Lv, Exp,
Production Time, Product Count, price
MimPrice, MaxPrice
Place_Id and Place_name
I'll explain what these are one at a time
The ID number, as I explained before
are necessary for
the code to distinguish
between the different crops
on the client and server
That is why we need to create
IDs for these crops
And the names here
are what will be displayed on the user interface
So the names Wheat, Corn, Soybeans, Candy canes, and so on
will be shown in Korean or English, according to the language setting
There are two ways to code the language settings
If you're coding a game
on an engine like Unity
or Unreal Engine
you can hard code the data
as data tables into the database
Then the data does not need to be transferred
between the server and client
If you write a C# script in Unity
and hard code the data
you will have to enter all of the crop names
in the code yourself
What is bad about this method
is that it is fundamentally very easy to hack
A hacker can easily reprogram the client
and get access to items
without making any in-app purchases
without having to go through
verification by the server
Second, it is difficult to edit
If we had to make any patches to the game
and if we were using a data table
The designer simply has to change the values in the data table
and send it to the server developer
who can apply that to the server right away
and update the data in real time
which means the values can be changed at any time
On the other hand, if you hard code
every time you need to make a change
you will have to go to the client developer
and open the C# script either in plain text or Visual Studio
or the code editor in Unity
and edit all of the code yourself
If that only happened one or two times, it wouldn't be a big deal
But if changes have to be made all of the time
it will begin to affect the client developer's work
Also, it is very difficult to communicate as well
Which is why in order to prevent abuse
and to make the work easier
we save the values in spreadsheets called data tables
and keep them on the server
and make the client talk to the server
to get the data in real time
Also, as for the levels
Wheat, corn, soybeans, candy canes, and carrots
have to be unlocked
at level 1, 2, 5, 7, 9, 13, 15, and 18 respectively
so we should enter that into this column
And experience yield
is the total amount of experience points
you get from harvesting these crops
So whatever number you enter in that column
is the amount of exp the player gets
when they harvest each crop
As for production time
because this is a SNG
If you plant different crops
some will take one minute to grow, while others take 20 minutes
So some crops take long to grow
while other don't take very long
but whichever you plant, you will have to wait a while
If you don't enter the production time
that feature won't be implemented
which is why you need to put production time as a column
The column is named Production Time
and if you set the unit to minutes
if you enter 60 as the value
then the crop will take 60 minutes to grow
As for Product Count
you can see that wheat has a value of 2
which means that a single harvest
will yield two wheat
As for the price, like most SNGs
When you plant wheat
it will take some money to do so
You can see planting wheat
costs 1 gold
Now for MimPrice and MaxPrice
When you harvest these crops
and sell them using the shop system
other players will buy them
But the game has a set minimum
and maximum price
so that the player can only
set the price below
the set maximum price
The reason that players will buy crops from each other
is because in SNG games
if you wanted to make bread, for example
you have to harvest wheat
and turn it into bread at the bakery
And if you wanted to make something out of that bread
you could get milk from a cow
and make a cream puff, for example
Then you can take the cream puff and add strawberries to it
to make a cream & strawberry puff
So items are made by combining various ingredients
And completing quests that require higher-level items
will give you more experience and gold
So the market system exists
so that players can buy lower-level items
and be able to create
the high-level items
as quickly as possible
So whatever items you don't have
you can get by buying from other players
Next you need to enter the ID of the source
Like before
I will use numbers as IDs
1 represents a field
2 is an apple tree, 3 is a raspberry bush
and 4 is a cherry tree
If you can only see the numbered IDs
it's hard to recognize which is which
So even though it won't be used in the code
to make it easier for the designer to work
I put a column here for the designer
and named it 'design'
to help with development
This column will not be saved on the client server
and is only for the development process
That was the detailed explanation
of data tables
Now let's look at the details
of the development
When you're writing a data table
when a programmer uses the table
they need the column names to be in the English alphabet
so you always need English names for the columns
The reason for that is
when you give the programmer
the data table
the table will usually be in Microsoft Excel format
and the programmer will convert it to the right file extension
and save it to the database
But programmers do not use letters other than the alphabet
for the column names
because those column names will be going in the code
that is why column names should be in English
Another thing of note
is how you write those English names
Do you use capitals?
How should you use underscores?
There should be rules for that as well
This problem should be solved
by looking at the data table draft
that the designer srote
together with the client and server programmers
The three should talk to each other
and reach an agreement about that
Some studios will have rules
so that the first letter is always capitalized
and underscores replace spaces
But if you can't do that for some reason
or if you're not sure
it's okay to use just lower case letters
The purpose of these names is to distinguish between things
so if you don't speak English, you can just use a translator
and use short words
rather than sentences
or big words
You don't have to make it grammatically correct
You just have to give each asset a unique ID
so it's okay
Next, I will explain to you
about data types
The development is smoother
when the designers designate the data types beforehand
It's not necessary for the designers to do this
because the programmers can do this as well
But the reason it's better for the designers to do it
is because the designer is who writes the data table
and has planned the entire system
and so the programmer may not be aware
of what the designer intended to do with each column
So in order for the designer
to make their intention clear in each of the columns
the designer has to have some level of knowledge
about data types
and be able to write the data table draft
It is possible to change
the data type even after
you have handed the table to the programmer
But before you fix anything
you should let them know what you had first intended to do
The most often used data type in this table
is Int, Float, String, and Boolean
Int stands for integer, Float means floating-point number, which means it can have decimals
String means a sequence of characters, and Boolean is true or false
Then there's a type called design, which is used for values that only the designer will see
and the List data type is somewhat special
The developer needs to be provided explanation
on how the data works
and what information each column holds
You, as the designer, have to organize the data table well
using column names, their alphabetical names, and data types
That is because, in the case
that a designer joins or leaves the team
if the information isn't organized well enough
the designer who is handed over the project
won't know what is what
That's why information has to be organized
If you keep the data table
as only as a spreadsheet in the design document
Someone who sees it for the first time
will only be able to see it
as a table full of encrypted messages
So that is why you should write down as a table
all of the names, data types, and some short explanations
for the columns in the data table
and put it in the game plans
You should also add some example values
as well as the column definitions
The example values don't have to be real values
You can just add dummy data instead
Because the only thing that matters here
is being able to tell the role of each column
Then let's take a look
at some example values
Here's an example
These are the column names in Korean
numbered ID, name, unlock level, experience yield
production time, product yield, price
minimum market price, maximum market price
source ID, and source(designer)
Those are the Korean column names
And as for the English column names
there are ID, Name, Lv, Exp, productionTime
productCount, Price, MimPrice, MaxPrice
Place_id, and Place_Name
These are how you would generally name columns
As for data types, ID is Int, Name is String, Lv is Int
Exp is Int, productionTime is also Int
productCount and Price are Int
so are MimPrice and MaxPrice
Place_Id is Int And Place_Name is design
which means it won't be used in the game
and is just for the developer to see
So that is how you categorize data types
ID is the distinct number for each type of crop
Name is the crop's name
Lv is the level it's unlocked at
Exp is the experience you get from the crop
productionTime is the time it takes to grow
and productCount
is the amount you get from a single harvest
Price is the base selling price
MimPrice
is the minimum price you can sell it at the market
MaxPrice is
the maximum selling price at the market
Place_Id is the ID for the place you can grow the crop
Place_Name is the places that Place_ID represents
but in words, so that the designer can check easily
Write down descriptions like this and the table is complete
Let's look at some actual examples
of data tables, and how to improve them
One of the amateur designers that I taught
was creating a data table for the first time
and the first one they made
was one about character upgrades
How that upgrade system worked
was that you give
the base character, which is the one you want to level up
other characters you don't need
Then they will disappear
and the base character levels up
Characters also have rarity levels
which are represented by stars
So they have levels and rarity levels
Both rarity and level
are better the higher they are
but rarity is determined
at the point you get the character
so from the gacha roll
And rarity
can also be raised using a system called 'evolution'
which can be done using a special item
so a character can go from having one star
to two, three, five and so on
Because of that, there is a data table
that shows the max level by rarity
and a table for the experience
required to reach the maximum level
Depending on the character's
original and current rarity
the maximum level is different
But you can see that there are two tables
one of the max level according to rarity
and the other of the experience required to reach max level
and that some of the cells are empty
As I said before
the less columns, the better
So let's try putting these two together
into a single table
We have a table showing the max level
by rarity, and one showing the experience
required to reach that level
We can take these tables, which have blank cells
and turn them into one cell like this
We took the columns
for base rarity, current rarity, and required exp
from the two tables
and put them together
into a single table
Now the data table is fixed
Now let me go over
the original maximum level data table
once more
For example, rarity can be between 1 and 4
and the columns represent levels
like level 1, 2, 3 and so on
The problem here is that
there are so many levels a character can be at
and there are columns for each level
Because of this
the table has too many cells
so it's hard for the programmer to view
and it unnecessarily takes up database space
It's packed with tiny letters as well
You can improve this table
simply by switching between the rows and columns
So putting rarity data into the columns
instead of level data
Now the columns and rows are switched
so it doesn't matter how many levels there are
there can be up to 100 or 200
Before there were 100 columns
that were eating up space in the database
but now that's been reduced dramatically
into just 4 columns
This is why you should think hard about
how you can design the data table
to reduce the number of columns
This case was a very easy one
As I said before
It doesn't matter how many rows there are
There can be hundreds or even thousands of them
What you have to be careful of
is not to make too many columns
Reverse-engineering Data Table Design Plans
First I'll explain to you about
the daily challenges and season challenges in the game Clash of Clans
In order to reverse plan Clash of Clans'
daily and season challenges
it would help to look at
what the data table looks like
Daily challenges can be completed
of course, each day
and seasonal ones change each season
So COC has a thing called
a season pass
You will probably know what that is
You can complete
quests and such to get rewards
These rewards are given according to pass level
When you complete a challenge, you get points
and by accumulating points
you gain pass levels
And because this is on a seasonal basis
It will change once a month
for example
So if it's October
the season pass will open on October 1st
and you will complete challenges for one month
to earn pass points
and if there are 20 levels, for example
as you keep earning points
your pass level will increase
one by one
and for each level
you can get a reward
This was first introduced by COC
and other games saw this
and implemented similar systems
So, COC, or Clash of Clans
has two kinds of challenges
Daily challenges and season challenges
And if you meet certain conditions
just like completing a quest
you can earn points
such as pass points or experience
The challenge can be to attack another player
or to win in a battle
so beat another player
Do a versus battle
or build a defensive building
and raise it to a certain level
Anything that can be done in the game
can be turned into a quest
But when creating a quest
what you have to be careful about
is that you have to assign it a type
Because there are season passes
which usually last
for one month
That means the challenges will change each month
and so may the rewards
and season pass points
So the first thing you would do
is to decide beforehand
what challenges will be in the daily challenges
and what challenges will be
part of the monthly season challenges
Then there will be columns in the data table
for challenge number, name, description
challenge points you can earn upon completion
maximum level, and challenge type
As for daily challenges, there are three a day
and they are refreshed each day
They have IDs, titles, descriptions, challenge point yields
and the maximum level they can appear or be completed on
As for season challenges
they are not given every day
but are completed over the course of a month
and they can't be re-completed once you complete them
They're just gone
So they each have an ID, title, description, amount of challenge points they give
maximum level, and challenge type
Now you need to calculate
the maximum points you can gain from all of the season challenges
For example, in the season pass
there are a total of 3000 points that you can earn
And you need to reverse-engineer
these 3000 points
If there are 100 season challenges, for example
you have make sure
all of the points you can get
from the 100 challenges in a single month
add up to 3000
Also, there are the 3 daily challenges
so the game should actually be balanced
so that you can complete the season pass
when you complete all of the daily challenges
along with the seasonal ones
Usually, if the season pass
can be completed with 3000 points
you have to design the challenges so that the points earned
from daily and season challenges add up to more than 3000
in order to prevent bugs from happening
and make the game run normally
With that in mind
you will have to decide
how many challenges there will be
daily and per season
and how many points
each challenge will give
So let's learn about the reward tiers and placement of rewards
in a season pass
Now, if you have designed
what kind of season challenges
give how many points
next you have to design the reward map
There are two types of rewards
the free tier and the paid tier
As for the free reward tier
you can get the free tier rewards
without making any in-app purchases
If there are 15 rewards in a season
you will have to enter
the type and number of paid tier rewards
and the type and number
of free tier rewards
for each of the 15 levels
Paid rewards, since this is a seasonal pass
will be available for a month
And if you pay a certain price
you can get the paid rewards as well
This is how the game persuades the player to pay money
So the season pass system
acts as gaming content
and an in-app purchase model at the same time
By content, I mean
that you can complete the challenges, like completing quests
and get the free tier rewards
so that is half of the rewards
And once you get the rewards
you see that with a relatively small purchase
you can get extra rewards
Because of this nature
the season pass also acts
as an in-app purchase model
The paid season pass is called the gold tier in COC
So you can enter in a data table
when the player reaches a reward level
how many of what item
they will get as a free and paid reward
And the data table for the season pass
is complete
When planning an actual game
you will enter the points required yourself
And design the season challenges and rewards
and also design the UI and UX
and the plan for the season pass is complete
What a new designer might do wrong
is that they might design the UI first
without any thought for actual gameplay details
A quick way to increase your design skill
would be to create the data table first
A season pass may also have a store
that sells other bonuses
For example, you could pay a certain amount
to get boosters
So a season pass
can not only have free and paid rewards
but various other perks as well
Since we now have a list
of the season pass rewards
we can put them in a table
and make sure to enter the text that will go in the icons as well
so that it will show up in the UI
The rewards can be broken down
into ID, category, name, and description
When you select a reward
in the UI
you should get a description
of what exactly that reward is
To increase the chances
of a player purchasing the gold tier
you could add detailed descriptions
of the paid reward
Next we will take a detailed look
at system and commercialization, using a flow chart
Here is an analyzation
of the entire system of a collecting RPG
The flow chart shows how
battles, character growth, raids & extra content
guilds, and real-time cooperative & PVP battles work together
Also, for example
when creating a flow chart like this
you can see what element influences which system
and create new currency or resources
You can also analyze paid items
It can also help
when suggesting an improvement
since you can easily point out
what to fix and what to newly implement
using the flow chart
and create a single-page proposal
When analyzing in-game systems and content
you can gain insight on related commercialization opportunities as well
For example, items that can be commercialized related to the battle system
would be tickets to enter the stage
tickets to restart
and tickets to auto-clear
These are items that can be sold to players
By auto-clear, I mean that by using the ticket
you can clear the stage
without having to actually play
So if you are planning to become
a business PM, or commercialization designer
it will help a lot to try analyzing
paid items and thinking of ways to improve them
Now let's look at a example
of breaking down MapleStory M's system
In MapleStory M
experience and resources are gained
mostly from hunting and clearing dungeons
and those resources are spent either on growing your character, in the shop
in the trading station, or at the blacksmith
So that is how the game flows
So first you should make a flow chart
on what items can be obtained
from hunting in the field
and try connecting those
to how they are used in character growth
Once that's finished
cover the other parts and change the alpha value
and you get a transparent gray effect
Now that the other parts are omitted
you can highlight the part
that you want to improve
For example, you know that
there are such and such items in the field part
and you want to improve them
If you show your coworkers using the flow chart, it will be easier to understand
Or, if there is a new item
and you think that it should be fixed
you can write it down in text like this
and the others can easily notice
that you want to recommend a new item
to put in the game flow
Live planners usually
fix and improve problems
in a game that is in live service
so it helps to make improvement plans for any problems you find right away
I'll show you an example of a flow chart
It's about character leveling
A flow chart is a drawing
that uses standardized icons and diagrams
to create a rendering
of a system's flow
When creating one, you should be careful
not to make any logical contradictions
There are beginnings and ends, results, actions, conditionals
So you create routes for if the conditionals are true
and routes for if conditionals are false
That's how a flow chart is made
Here is an example of a character upgrade flow chart
You choose the character you want to promote
and go to the promotion screen
After moving, the game checks if the character is at max level
and determine whether or not
the character can be promoted
So depending on the conditions
whether there are enough resources, for example
the game can decide what it will do next
That was a flow chart about character promotion
Now let's look at a flow chart
of guild exploration, according to user action and system function
You can write a flow chart
of the system like this
UI, state, action, and result
these are the activities
Then we can depict the flow of time
and when items are obtained
to lay out which system the play interacts with
and what results each action that they can take
will bring
And we have a complete flow chart
of the gameplay flow according to the player's actions and the system
So for a system you are creating or reverse engineering
you need to try making a flow chart
of player action and system function
Player action means actions that the user takes
such as pressing a button
and system function is the content
of the result of said actions
UI is the user interface that is being displayed
Action is the action the player takes
State is the state of gameplay or the player
and Result is the result of the gameplay
The arrow depicting flow of time
indicates that the game is able to progress after a certain amount of time
The plus and minus symbols
show where resources are obtained
and used up
You could also create a flow chart
for equipment upgrades, using the same system
of UI, Action, State, and Result
Thank you
Writing a Data Table Gathering data Search on the internet for existing data Collect data through gameplay
Writing a data table Columns: Represent a single type of data, contain attributes of items or game content
More columns take up more DB space, therefore as few columns should be used as possible Columns have separate alphabetic names for using in code
Data types must be defined as to make clear the designer's intent Defining data table columns Columns should have descriptions of what data they hold
A separate table of each column's title, data type, and description should be inserted in document
Reverse-engineering Data Tables System and commercialization Game system flow chart Gain insight on game elements Add resources Analyze paid items
Proposing improvement or new idea Game system design Flow Chart A diagram that depicts the flow of the system using standardized symbols
Be wary of logical errors when writing Player action and system function A flow chart should exist of the interaction of player action and the system
Player action: Actions the player takes, such as a key input System function: The result of the player's actions