!-- Header-->
Home Blog
Logic LAMP Stack PHP Logic NodeJS + SQL Learn HTML Learn CSS ReactVR
Shop | Coding Commanders Twitch Donations Donor Box

Battle Card SQL

Battle Card Game Dev > PostgreSQL > Battle Card Database

This tutorial uses PostgreSQL on a Linux OS (Ubuntu). Before begining, you wil have to install and set up PostgreSQL.

✶ Install PostgreSQL (Linux, Windows, OSX)
✶ Setup PostgreSQL (Ubuntu)
✶ psql commands
✶ Tables + Queries
✶ Set up Oracle VB with Ubuntu

Create Database

$ createdb ccBattleCards

Create Table: card_types

We will call the character classes "card types" and stick them in a "card_types" table.

 -- Card types table

create table if not exist card_types (
  id serial,
  my_timestamp timestamp
    default current_timestamp,
  name varchar,
  intelligence integer,
  strength integer,
  dex integer,
  spirituality integer,
  brutality integer,
  diplomacy integer,
  PRIMARY KEY (user_id)
);



➼ id - A unique numerical idetifier that we will use for our primary key.
➼ my_timestamp - Automatically insert a timestamp when a data entry is inserted.
➼ name - The name of the card type. varchar datatype is like a string.
➼ skills - Finally, we have a column for each skill. Each skill is an integer 1-20.

Insert Data

-- data to insert insert into card_types (name,intelligence,strength,dex, spirituality,brutality,diplomacy) values ('paladin',20,5, 15, 18,5,10), ('knight',13,15,10, 5, 15,10), ('priest', 18, 8, 10, 20, 1,18), ('wolf', 8, 15, 10, 10, 5, 2), ('mage', 18, 5, 20, 15, 10, 5), ('druid', 15, 10, 10, 20, 1, 15), ('dragon', 15, 18, 15, 5, 18, 10), ('warrior',10, 18, 15, 5, 15, 5), ('mage', 15, 5, 15, 15, 5, 10), ('unicorn', 15, 5, 12, 18, 1, 15), ('mimic',10, 10, 10, 10, 10, 10);

➼ name
➼ intelligence
➼ stregth
➼ dex
➼ spirituality
➼ brutality
➼ diplomacy

Take a look at the data that you just inserted.

ccBattleCards=# select * from card_types;


Twitch: daisychaincosplay Live Coding!!!


Create Table: cards

Next we will create our cards table. It references card_types because each card character belongs to a character class.

When one table references another table, we can use a foreign key constrate. In relational databases, one table's primary key is often another table's foreign key.

-- cards table: references card_types (id)
  create table cards (
    id serial,
    my_timestamp timestamp default current_timestamp,
    name varchar,
    image varchar,
    alignment integer,
    type_id integer references card_types(id)
  );

➼ type_id - The name of the column which holds our foriegn key.
➼ integer - Datatype
➼ card_types - The Table we are referencing
➼ id - The column we are referencing

Insert Data

insert into cards (name,image,alignment,type_id)
values   ('Paladin Hatnix','paladin-hatnix.png', 0, 1),
  ('Sir Diealot','sir-diealot.png', 0, 2),
  ('Priestess Jill','priestess-jill.png', 0, 3),
  ('Afro Wolf','afro-wolf.png',0, 4),
  ('Tux Digi-Wolf', 'digital-tux.png', 0, 4),
  ('Verrucktes Huhn', 'crazy-chicken.png', 1, 5),
  ('Vampire Mage', 'eva-mage.png', 0, 5),
  ('Doove Druid', 'doove-druid.png',0,6),
  ('Shayna Madela', 'nahama.jpg', 0, 6),
  ('Master Warrior', 'nahama.jpg', 1, 8),
  ('John Smith', 'john-smith.png', 1, 8),
  ('StregthInside Unicorn', 'strength-unicorn.png',0,10);
Previous Lesson | Next Lesson