Since 2.5 months ago I have been addicted to play this game in my iPad: Dragonvale. Its a product from Backflip Studios, you can download it from here. In this game we try to raise, and breed various of dragons. You can get some money and gems here.
It went in my mind to reverse engineer their process and figure out their database structures. First, we have to know the scenarios of the game which we already knew. Second, to know how many entities on the game and its functions. We will not discussed about how to programmed it, its only database structures that I will try to elaborate here.
We will do some half-normalization, just to speed up the process considering I’m a programmer too. Judging from the hosting service that Backflip used, which is in Amazon it should be NoSQL but either way I like to make it simple and make it in MySQL style.
Several entities in this game are;
- Games status: Level, Coins, Gems, Foods
- Level: name, experience
- Treasures: Coins(info,price), Gems(info,price), Foods(info,price)
- Dragons: variety of the dragon, coin rate/minutes on their level, price, habitats, info
- Habitats: price, dragon capacity, max dragoncash, info
- Decorations: price, info
- Buildings: price, info, elements
- Islands: price, info, status
- Goals: objective, money, xp
From those entities we can conclude minimum we need 9 tables to be created on the database. Plus another extra columns that should be added on the table for additional information such as for which user the dragon assigned.
1. GAME STATUS
Its the status/condition on the upper parts of your screen that tells you how many coins, gems, level and foods you have.
We will need a table that has columns consist of; xps, coins, gems, foods. With additional columns; id, userid, level name, first input, last update. For the last two columns, I like to have a log when the date first time inputted to the system and when it has the last update.
CREATE TABLE IF NOT EXISTS `game_status` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`lxp` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`lname` int(4) NOT NULL DEFAULT '0',
`coins` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`gems` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`foods` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`inputDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`inputBy` int(11) NOT NULL DEFAULT '0',
`updDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`updBy` int(11) NOT NULL DEFAULT '0',
`del` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
2. LEVEL
On the main Level table we have to create the down limit and the upper limit so our code can decide and update players game status. Its a little bit tricky because we have to create another table besides the main table that will contain information on leveling rewards. It is when players reach certain level some items will be unlocked in the Market, players also gain XP and coins. Even without leveling rewards table we can make it done by having additional columns for each table items on market that contains lock level information and lock status.
Main Level Table
CREATE TABLE IF NOT EXISTS `level` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` int(4) NOT NULL DEFAULT '0',
`xpdown` int(11) NOT NULL DEFAULT '0',
`xpup` int(11) NOT NULL DEFAULT '0',
`inpBy` int(11) NOT NULL DEFAULT '0',
`inpDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`updBy` int(11) NOT NULL DEFAULT '0',
`updDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`del` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
3. TREASURES
Treasures consist of 3 items; coins, gems, and foods. We will need to create only 1 table that consist information about those items: name, qty, cost, category (whether its coins, gems, or food). Every time player goes to the next level, quantity of the item will be increase so we’ll add column ‘level’ on the table.
CREATE TABLE IF NOT EXISTS `treasure` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`cat_id_fk` int(11) NOT NULL DEFAULT '0',
`qty` int(11) NOT NULL,
`price` double NOT NULL,
`level` int(11) NOT NULL DEFAULT '0',
`inpBy` int(11) NOT NULL DEFAULT '0',
`inpDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`updBy` int(11) NOT NULL DEFAULT '0',
`updDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`del` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
Why there’s ‘cat_id_fk’? Because probably in the near future Backflip Studios probably will another categories of Treasures beside coin, gem, and food 😉
CREATE TABLE IF NOT EXISTS `treasure_cat` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` int(4) NOT NULL DEFAULT '0',
`inpBy` int(11) NOT NULL DEFAULT '0',
`inpDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`updBy` int(11) NOT NULL DEFAULT '0',
`updDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`del` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
4. DRAGONS
The main character of this game, player have to breed, hatched, and raised. Player can also make a hybrid dragon by cross-breeding its parents in the Normal Breeding Cave or Epic Breeding Island. We have to make at least 4 tables that contain basic information of the dragons; basic data, hybrid, coin rate, picts.
CREATE TABLE IF NOT EXISTS `dragon` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`dragon_id_fk` int(11) NOT NULL DEFAULT '0',
`lup` int(11) NOT NULL DEFAULT '0',
`ldown` int(11) NOT NULL DEFAULT '0',
`price` double NOT NULL,
`pict` text COLLATE utf8_unicode_ci NOT NULL,
`inpBy` int(11) NOT NULL DEFAULT '0',
`inpDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`updBy` int(11) NOT NULL DEFAULT '0',
`updDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`del` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
For dragon coin rate per minute table, I just want to make it simple and fast but still using MySQL style. If we are using NOSQL a row-based model are much easier to used for converting data that need many columns. Reference on dragon earning rates can be read here
CREATE TABLE IF NOT EXISTS `dragon_rate` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`dragon_id_fk` int(11) NOT NULL DEFAULT '0',
`1` int(11) NOT NULL,
`2` int(11) NOT NULL,
`3` int(11) NOT NULL,
`4` int(11) NOT NULL,
`5` int(11) NOT NULL,
`6` int(11) NOT NULL,
`7` int(11) NOT NULL,
`8` int(11) NOT NULL,
`9` int(11) NOT NULL,
`10` int(11) NOT NULL,
`11` int(11) NOT NULL,
`12` int(11) NOT NULL,
`13` int(11) NOT NULL,
`14` int(11) NOT NULL,
`15` int(11) NOT NULL,
`16` int(11) NOT NULL,
`17` int(11) NOT NULL,
`18` int(11) NOT NULL,
`19` int(11) NOT NULL,
`20` int(11) NOT NULL,
`inpBy` int(11) NOT NULL DEFAULT '0',
`inpDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`updBy` int(11) NOT NULL DEFAULT '0',
`updDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`del` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
Everytime a dragon having its level up, its appareance change. We will also need to create a table that contains its picts. Column ‘lup’ and ‘down’ is the limit ID from table ‘level’ as a parameter to change the dragon picture when its level up.
CREATE TABLE IF NOT EXISTS `dragon` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`dragon_id_fk` int(11) NOT NULL DEFAULT '0',
`lup` int(11) NOT NULL DEFAULT '0',
`ldown` int(11) NOT NULL DEFAULT '0',
`pict` text COLLATE utf8_unicode_ci NOT NULL,
`inpBy` int(11) NOT NULL DEFAULT '0',
`inpDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`updBy` int(11) NOT NULL DEFAULT '0',
`updDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`del` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
– I will update it soon –