Converting JSON to a MySQL table, how should the table structure look like?

Question

The following is the JSON file...

{
    "name":"Magic 2014 Core Set",
    "code":"M14",
    "releaseDate":"2013-07-19",
    "border":"black",
    "type":"core",
    "cards":
    [
        {
            "layout":"normal",
            "type":"Creature - Human Warrior",
            "types":["Creature"],
            "colors":["Red"],
            "multiverseid":370735,
            "name":"Academy Raider",
            "subtypes":["Human","Warrior"],
            "cmc":3,
            "rarity":"Common",
            "artist":"Karl Kopinski",
            "power":"1",
            "toughness":"1",
            "manaCost":"{2}{R}",
            "text":"Intimidate (This creature can't be blocked except by artifact creatures and/or creatures that share a color with it.)\n\nWhenever Academy Raider deals combat damage to a player, you may discard a card. If you do, draw a card.",
            "number":"124",
            "imageName":"academy raider"
        },
        {
            "layout":"normal",
            "type":"Artifact - Equipment",
            "types":["Artifact"],
            "colors":[],
            "multiverseid":370581,
            "name":"Accorder's Shield",
            "subtypes":["Equipment"],
            "cmc":0,
            "rarity":"Uncommon",
            "artist":"Alan Pollack",
            "manaCost":"{0}",
            "text":"Equipped creature gets +0/+3 and has vigilance. (Attacking doesn't cause it to tap.)\n\nEquip {3} ({3}: Attach to target creature you control. Equip only as a sorcery.)",
            "flavor":"An Auriok shield is polished to a mirror finish even on the inside, enabling its bearer to watch foes ahead and behind.",
            "number":"204",
            "imageName":"accorder's shield"
        },
        {
            "layout":"normal",
            "type":"Creature - Spirit",
            "types":["Creature"],
            "colors":["Black"],
            "multiverseid":370811,
            "name":"Accursed Spirit",
            "subtypes":["Spirit"],
            "cmc":4,
            "rarity":"Common",
            "artist":"Kev Walker",
            "power":"3",
            "toughness":"2",
            "manaCost":"{3}{B}",
            "text":"Intimidate (This creature can't be blocked except by artifact creatures and/or creatures that share a color with it.)",
            "flavor":"Many have heard the slither of dragging armor and the soft squelch of its voice. But only its victims ever meet its icy gaze.",
            "number":"83",
            "imageName":"accursed spirit"
        },
        {...},
        {...},
        {...},
    ]
}

The cards data itself I think would be in a single table but I'm not sure how the...

"name":"Magic 2014 Core Set",
"code":"M14",
"releaseDate":"2013-07-19",
"border":"black",
"type":"core",

would be associated with the card data. How should I design the MySQL table(s) for easy and efficient access?

Answer 1

MySQL is a relational database. This means that any solution you come up with will need to include a primary key, a foreign key and normalization. Here is a simple tutorial that will show you what to do. Have fun!

http://www.dreamincode.net/forums/topic/179103-relational-database-design-normalization/

Answer 2

It's hard to say how data should be structured as that can depend on your application. However, as a first cut, some good rules of thumb might be:

1. All non-array data at the same "level" of a single JSON object is a single table. By level I mean how deeply nested the objects are. So, for example, given {"a": 100, "b": "hello", "c": {"x": 100, "y": "foo"}}, a, and b are on the same level, while x and y are on a different level.
2. You have a few options for handling data on different levels:
   1. "flatten" the nesting so that, for the example above, you'd have a single table containing a, b, x, and y.
   2. Create new tables for each nesting level. Given the example above, that's one table containing a and b, and one containing x and y. There's clearly a relationship between these two tables which tells you if you how to construct linking keys. See https://stackoverflow.com/a/7296873/1431244 for details on that.
3. Arrays pretty clearly indicate a one-to-many relationship so those go in their own table as described by the post linked above.

The JSON file above is pretty big, so I'm not going to construct all tables with all fields, but here's a sample that hopefully explains the rough idea:

create table card_pack (
  # Primary key to uniquely identify the pack
  id integer autoincrement primary key,
  name TEXT,
  # foreign key that links to the codes table
  code_id integer,
  # etc, etc...
);

create table codes (
  # This is what the code_id field in the card_pack table refers to
  id integer autoincrement primary key,
  name CHAR(10)
);

create table cards (
  # unique key for each card
  id integer autoincrement primay key,
  # Refers to the card_pack table for the card pack
  # containing this card
  pack_id integer,
  name TEXT,
  # This should probably be a foreign key referring to a layouts table
  # which contains one row per layout
  layout TEXT,
  # etc, etc.
)

# Table with one row for every possible card color
create table colors {
  id integer autoincrement primay key,
  name TEXT,
)

# table that defines a many-to-many relationship
# indicating which cards are which colors, so a row with
# card_id = 7 and color_id = 11 means that card 7 is color 11.
# Note that another row might have card_id 7 and color_id 18
# so that card 7 is two colors, both color 11 and color 18.
create table cards_colors (
  card_id integer,
  color_id integer
)

In the above there's a lot of details missing. For example, you probably don't really want a generic TEXT type for all string fields. Some should probably be CHAR and some VARCHAR depending on field sizes, space vs. performance considerations, etc. Similarly where I have integer you might want bigint, mediumint, etc. depending on the number of values you expect, etc. There's also index considerations, foreign key constraints, and so on, but the above hopefully gives you the right idea and provides enough information to get started.

Answer 3

I think you must have 2 tables to store such data.

create table tbl_card (
card_id int primary key auto_increment,
name varchar(50) not null,
code varchar(10) not null,
release_date datetime not null,
border varchar(20) not null,
type varchar(20) not null
)

create table tbl_card_detail (
card_id int not null,
type varchar not null,
....
primary key (card_id,type)
)

Answer 4

I think you should go with a table cardSet that would contain (name, code, releaseDate, border, type) and another table for cards with a foreign key referring to cardSet

you would also need to make type, color, subtype tables that would have a many to many relationship with the card table since you can have a card with more than one type, color or subtype

CREATE TABLE `card` (
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY  (`id`)
);

CREATE TABLE `type` (
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY  (`id`)
);

CREATE TABLE `cardType` (
  `card` INT,
  `type` INT
);

CREATE TABLE `cardSet` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `` INT,
  PRIMARY KEY  (`id`)
);

CREATE TABLE `color` (
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY  (`id`)
);

CREATE TABLE `cardColor` (
  `card` INT,
  `color` INT
);

CREATE TABLE `subType` (
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY  (`id`)
);

CREATE TABLE `cardSubType` (
  `card` INT,
  `subType` INT
);



ALTER TABLE `cardType` ADD CONSTRAINT `cardType_fk1` FOREIGN KEY (`card`) REFERENCES card(`id`);
ALTER TABLE `cardType` ADD CONSTRAINT `cardType_fk2` FOREIGN KEY (`type`) REFERENCES type(`id`);
ALTER TABLE `cardSet` ADD CONSTRAINT `cardSet_fk1` FOREIGN KEY (``) REFERENCES cardSet(`id`);

ALTER TABLE `cardColor` ADD CONSTRAINT `cardColor_fk1` FOREIGN KEY (`card`) REFERENCES card(`id`);
ALTER TABLE `cardColor` ADD CONSTRAINT `cardColor_fk2` FOREIGN KEY (`color`) REFERENCES color(`id`);

ALTER TABLE `cardSubType` ADD CONSTRAINT `cardSubType_fk1` FOREIGN KEY (`card`) REFERENCES card(`id`);
ALTER TABLE `cardSubType` ADD CONSTRAINT `cardSubType_fk2` FOREIGN KEY (`subType`) REFERENCES subType(`id`);

Answer 5

Here is the Normalized Schema in Raw Form , You can alter it your needs and Update it with Null, Primary Key, Foreign Key attributes , Type with respect to database you are using

Bold (Highlighted) are table Names, PK = Primary key, FK = Foreign Key, u can alter as per your needs

  Template (TABLE)
 1- Name
 2- Code
 3- Release Date
 4- Border
 5- Type 
 6- Id (PK)

 Template Cards (TABLE)
 1- Template Id  (FK) (Template Table )
 2- Card Id (FK) (Cards Table)

 Cards  ( Has M-M relationship with Types, Cards ,Subtypes Table)  (TABLE)
 1- layout
 2- type
 3- mutiverseid
 4- name 
 5- Card Id (PK) 
 6- Card Detail Id

 Cards Detail
 1- Card detail Id
 2- Card Id
 2- Object Type ( 0 = Types , 1 = Color , 2 = Subtypes )
 3- Object Id  ( This id corresponds to Types, Color , Subtypes Table with respect to Object Type )

 Types (TABLE)
 1- type id (PK)
 2- type Detail/Code

 Color (TABLE)
 1- Color id (PK)
 2- Color Detail/Code

 SubTypes (TABLE)
 1- Subtype id (PK)
 2- Subtype Detail/Code