Setup

Database configuration

• URL: localhost
• Username: root
• Password: none
• Database: genshin_card_game

Database objects

CREATE TABLE `additional_info` (
  `id` int(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
  `character_id` int(11) NOT NULL,
  `info` varchar(4000) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `characters` (
  `id` int(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
  `name` varchar(64) NOT NULL,
  `scraped_from` varchar(255) DEFAULT NULL,
  `hp` int(4) NOT NULL,
  `weapon_id` int(11) NOT NULL,
  `element_id` int(11) NOT NULL,
  `img_url` varchar(255) NOT NULL,
  `lower_skill_fontsize` tinyint(1) NOT NULL DEFAULT 0,
  `lower_skill_title_fontsize` tinyint(1) NOT NULL DEFAULT 0,
  `lower_skill_margins` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `elements` (
  `id` int(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
  `name` varchar(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `elements` (`id`, `name`) VALUES
(1, 'anemo'),
(2, 'cryo'),
(3, 'dendro'),
(4, 'electro'),
(5, 'energy'),
(6, 'geo'),
(7, 'hydro'),
(8, 'pyro'),
(9, 'matching'),
(10, 'unaligned');

CREATE TABLE `items` (
  `id` int(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
  `name` varchar(128) NOT NULL,
  `type` varchar(64) NOT NULL,
  `effect` varchar(2000) NOT NULL,
  `usages` int(11) DEFAULT NULL,
  `img_url` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `item_costs` (
  `id` int(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
  `element_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `value` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `skills` (
  `id` int(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
  `name` varchar(64) NOT NULL,
  `effect` varchar(2000) NOT NULL,
  `character_id` int(11) NOT NULL,
  `img_url` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `skill_costs` (
  `id` int(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
  `skill_id` int(11) NOT NULL,
  `element_id` int(11) NOT NULL,
  `value` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `weapons` (
  `id` int(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
  `name` varchar(64) NOT NULL,
  `img_url` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `weapons` (`id`, `name`, `img_url`) VALUES
(1, 'bow', 'bow.png'),
(2, 'catalyst', 'catalyst.png'),
(3, 'claymore', 'claymore.png'),
(4, 'polearm', 'polearm.png'),
(5, 'sword', 'sword.png');

ALTER TABLE `additional_info`
  ADD CONSTRAINT `fk_additional_info_character_id` FOREIGN KEY (`character_id`) REFERENCES `characters` (`id`);

ALTER TABLE `characters`
  ADD CONSTRAINT `fk_characters_element_id` FOREIGN KEY (`element_id`) REFERENCES `elements` (`id`),
  ADD CONSTRAINT `fk_characters_weapon_id` FOREIGN KEY (`weapon_id`) REFERENCES `weapons` (`id`);

ALTER TABLE `item_costs`
  ADD CONSTRAINT `fk_item_costs_element_id` FOREIGN KEY (`element_id`) REFERENCES `elements` (`id`),
  ADD CONSTRAINT `fk_item_costs_item_id` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `skills`
  ADD CONSTRAINT `fk_skills_character_id` FOREIGN KEY (`character_id`) REFERENCES `characters` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `skill_costs`
  ADD CONSTRAINT `fk_skill_costs_element_id` FOREIGN KEY (`element_id`) REFERENCES `elements` (`id`),
  ADD CONSTRAINT `fk_skill_costs_skill_id` FOREIGN KEY (`skill_id`) REFERENCES `skills` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;