Pizza Database SQL

schema.sql

DROP TABLE IF EXISTS `topping`;
		
CREATE TABLE `topping` (
  `id` INTEGER AUTO_INCREMENT,
  `Name` TEXT,
  PRIMARY KEY (`id`)
);

-- ---
-- Table 'customers'
-- 
-- ---

DROP TABLE IF EXISTS `customers`;
		
CREATE TABLE `customers` (
  `id` INTEGER NULL AUTO_INCREMENT,
  `Name` TEXT,
  PRIMARY KEY (`id`)
);

-- ---
-- Table 'drink'
-- 
-- ---

DROP TABLE IF EXISTS `drink`;
		
CREATE TABLE `drink` (
  `id` SERIAL,
  `Name` TEXT,
  PRIMARY KEY (`id`)
);

-- ---
-- Table 'credit_cards'
-- 
-- ---

DROP TABLE IF EXISTS `credit_cards`;
		
CREATE TABLE `credit_cards` (
  `id` SERIAL,
  `name` VARCHAR(80),
  PRIMARY KEY (`id`)
);

-- ---
-- Table 'customer_transactions'
-- 
-- ---

DROP TABLE IF EXISTS `customer_transactions`;
		
CREATE TABLE `customer_transactions` (
  `customer_id` SERIAL,
  `transaction_id` SERIAL,
  PRIMARY KEY (`customer_id`)
);

-- ---
-- Table 'transaction'
-- 
-- ---

DROP TABLE IF EXISTS `transaction`;
		
CREATE TABLE `transaction` (
  `id` SERIAL,
  `date` TIMESTAMP,
  `delivery_address` VARCHAR(255),
  `order_data_id` INTEGER,
  PRIMARY KEY (`id`)
);

-- ---
-- Table 'order_data'
-- 
-- ---

DROP TABLE IF EXISTS `order_data`;
		
CREATE TABLE `order_data` (
  `id` SERIAL,
  `price` INTEGER,
  PRIMARY KEY (`id`)
);

-- ---
-- Table 'ordered_custom_pizzas'
-- 
-- ---

DROP TABLE IF EXISTS `ordered_custom_pizzas`;
		
CREATE TABLE `ordered_custom_pizzas` (
  `order_data_id` SERIAL,
  `pizza_id` INTEGER,
  PRIMARY KEY (`order_data_id`)
);

-- ---
-- Table 'ordered_drink'
-- 
-- ---

DROP TABLE IF EXISTS `ordered_drink`;
		
CREATE TABLE `ordered_drink` (
  `order_data_id` SERIAL,
  `drink_id` INTEGER,
  PRIMARY KEY (`order_data_id`)
);

-- ---
-- Table 'custom_pizza'
-- 
-- ---

DROP TABLE IF EXISTS `custom_pizza`;
		
CREATE TABLE `custom_pizza` (
  `id` INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
  `price` INTEGER NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
);

-- ---
-- Table 'pizza_toppings'
-- 
-- ---

DROP TABLE IF EXISTS `pizza_toppings`;
		
CREATE TABLE `pizza_toppings` (
  `pizza_id` INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
  `topping_id` INTEGER NULL DEFAULT NULL,
  PRIMARY KEY (`pizza_id`)
);

-- ---
-- Foreign Keys 
-- ---

ALTER TABLE `credit_cards` ADD FOREIGN KEY (new field) REFERENCES `customers` (`id`);
ALTER TABLE `customer_transactions` ADD FOREIGN KEY (customer_id) REFERENCES `customers` (`id`);
ALTER TABLE `customer_transactions` ADD FOREIGN KEY (transaction_id) REFERENCES `transaction` (`id`);
ALTER TABLE `transaction` ADD FOREIGN KEY (order_data_id) REFERENCES `order_data` (`id`);
ALTER TABLE `order_data` ADD FOREIGN KEY (id) REFERENCES `ordered_custom_pizzas` (`order_data_id`);
ALTER TABLE `order_data` ADD FOREIGN KEY (id) REFERENCES `ordered_drink` (`order_data_id`);
ALTER TABLE `ordered_custom_pizzas` ADD FOREIGN KEY (pizza_id) REFERENCES `custom_pizza` (`id`);
ALTER TABLE `ordered_drink` ADD FOREIGN KEY (drink_id) REFERENCES `drink` (`id`);
ALTER TABLE `custom_pizza` ADD FOREIGN KEY (id) REFERENCES `pizza_toppings` (`pizza_id`);
ALTER TABLE `pizza_toppings` ADD FOREIGN KEY (topping_id) REFERENCES `topping` (`id`);