Created
September 19, 2022 00:08
-
-
Save beisong7/9ab5c1f29a42ca35304fe70793abe9cc to your computer and use it in GitHub Desktop.
sample database schema
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DROP DATABASE IF EXISTS mydatabase; | |
| CREATE DATABASE mydatabase; | |
| DROP TABLE IF EXISTS mydatabase.customers; | |
| DROP TABLE IF EXISTS mydatabase.payments; | |
| CREATE TABLE mydatabase.customers ( | |
| customerId INT NOT NULL AUTO_INCREMENT, | |
| email VARCHAR(255) NOT NULL, | |
| createTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| firstName VARCHAR(255) NOT NULL, | |
| middleName VARCHAR(255), | |
| lastName VARCHAR(255), | |
| city VARCHAR(255) NOT NULL, | |
| address VARCHAR(255) NOT NULL, | |
| state VARCHAR(255) NOT NULL, | |
| country VARCHAR(32) NOT NULL, | |
| zipcode INT NOT NULL, | |
| UNIQUE (email), | |
| PRIMARY KEY(customerId) | |
| ); | |
| CREATE TABLE mydatabase.payments ( | |
| paymentId INT NOT NULL AUTO_INCREMENT, | |
| customerId INT NOT NULL, | |
| createTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| cardLast4Digits INT NOT NULL, | |
| currency VARCHAR(32) NOT NULL, | |
| amount FLOAT(0) NOT NULL, | |
| ref VARCHAR(255) NOT NULL, | |
| PRIMARY KEY(paymentId), | |
| FOREIGN KEY (customerId) REFERENCES mydatabase.customers(customerId) | |
| ); | |
| -- TEST DATABASE AND TABLES | |
| DROP DATABASE IF EXISTS mydatabase_test; | |
| CREATE DATABASE mydatabase_test; | |
| DROP TABLE IF EXISTS mydatabase_test.customers; | |
| DROP TABLE IF EXISTS mydatabase_test.payments; | |
| CREATE TABLE mydatabase_test.customers ( | |
| customerId INT NOT NULL AUTO_INCREMENT, | |
| email VARCHAR(255) NOT NULL, | |
| createTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| firstName VARCHAR(255) NOT NULL, | |
| middleName VARCHAR(255), | |
| lastName VARCHAR(255), | |
| city VARCHAR(255) NOT NULL, | |
| address VARCHAR(255) NOT NULL, | |
| state VARCHAR(255) NOT NULL, | |
| country VARCHAR(32) NOT NULL, | |
| zipcode INT NOT NULL, | |
| UNIQUE (email), | |
| PRIMARY KEY(customerId) | |
| ); | |
| CREATE TABLE mydatabase_test.payments ( | |
| paymentId INT NOT NULL AUTO_INCREMENT, | |
| customerId INT NOT NULL, | |
| createTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| cardLast4Digits INT NOT NULL, | |
| currency VARCHAR(32) NOT NULL, | |
| amount FLOAT(0) NOT NULL, | |
| ref VARCHAR(255) NOT NULL, | |
| PRIMARY KEY(paymentId), | |
| FOREIGN KEY (customerId) REFERENCES mydatabase_test.customers(customerId) | |
| ); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment