Last active
July 16, 2022 05:17
-
-
Save stephepush/79c188d487fcd3e551085a7d5ee66b9f to your computer and use it in GitHub Desktop.
This file contains hidden or 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 TABLE car_photos; | |
-- DROP TABLE cars; | |
CREATE TABLE cars ( | |
car_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, | |
model_year SMALLINT NOT NULL, | |
make VARCHAR(80) NOT NULL, | |
model VARCHAR(90) NOT NULL, | |
miles INTEGER NOT NULL, | |
color VARCHAR(25) NOT NULL, | |
transmission VARCHAR(80) NOT NULL, | |
layout VARCHAR(10) NOT NULL, | |
engine_type VARCHAR(20) NOT NULL, | |
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, | |
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP | |
); | |
CREATE TABLE car_photos ( | |
car_photo_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, | |
car_id INT NOT NULL, | |
CONSTRAINT fk_photos_car FOREIGN KEY (car_id) | |
REFERENCES cars (car_id) | |
ON UPDATE CASCADE ON DELETE CASCADE, | |
car_photo_url VARCHAR(200) NOT NULL | |
); | |
CREATE TABLE car_prices ( | |
price_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, | |
car_id INT NOT NULL, | |
CONSTRAINT fk_prices_car FOREIGN KEY (car_id) | |
REFERENCES cars (car_id) | |
ON UPDATE CASCADE ON DELETE CASCADE, | |
car_price DECIMAL(9 , 2 ) NOT NULL | |
); | |
CREATE TABLE features ( | |
feature_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, | |
feature VARCHAR(100) NOT NULL | |
); | |
CREATE TABLE car_features ( | |
car_feature_join_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, | |
feature_id INT NOT NULL, | |
CONSTRAINT fk_feature FOREIGN KEY (feature_id) | |
REFERENCES features (feature_id) | |
ON UPDATE CASCADE ON DELETE CASCADE, | |
car_id INT NOT NULL, | |
CONSTRAINT fk_feature_car FOREIGN KEY (car_id) | |
REFERENCES cars (car_id) | |
ON UPDATE CASCADE ON DELETE CASCADE | |
); | |
CREATE TABLE sales_status ( | |
sales_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, | |
car_id INT NOT NULL, | |
CONSTRAINT fk_sale_status_car | |
FOREIGN KEY (car_id) | |
REFERENCES cars(car_id) | |
ON UPDATE CASCADE | |
ON DELETE CASCADE, | |
sold BOOLEAN NOT NULL DEFAULT FALSE, | |
sale_status VARCHAR(90) NOT NULL DEFAULT 'acquired by dealership', | |
for_sale BOOLEAN DEFAULT TRUE, | |
dateTime_sold TIMESTAMP, | |
sales_discount_percent DECIMAL(5 , 2 ), | |
final_price DECIMAL(9,2), | |
notes VARCHAR(3000) | |
); | |
CREATE TABLE persons ( | |
person_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, | |
person_img VARCHAR(200) DEFAULT 'https://res.cloudinary.com/dmkct6wfu/image/upload/v1635382183/blank-profile-picture-973460_cxq7zx.svg', | |
dob DATE, | |
-- gender VARCHAR(20) NOT NULL, | |
newsletter BOOLEAN NOT NULL DEFAULT 0, | |
is_active BOOLEAN NOT NULL DEFAULT TRUE | |
); | |
CREATE TABLE users ( | |
user_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, | |
email VARCHAR(130) UNIQUE, | |
username VARCHAR(25) NOT NULL, | |
hash VARCHAR(1024) NOT NULL, | |
salt VARCHAR(1024) NOT NULL, | |
user_created TIMESTAMP, | |
person_id INT, | |
CONSTRAINT fk_user_person FOREIGN KEY (person_id) | |
REFERENCES persons(person_id) | |
ON UPDATE CASCADE | |
ON DELETE CASCADE | |
); | |
-- users _> wishlist <-> wishlist_car <-> car | |
CREATE TABLE wishlists ( | |
wishlist_id INT AUTO_INCREMENT PRIMARY KEY, | |
wishlist_name varChar(120), | |
wishlist_created TIMESTAMP, | |
wistlist_deleted TIMESTAMP, | |
is_deleted BOOLEAN NOT NULL DEFAULT FALSE, | |
user_id INT, | |
CONSTRAINT fk_user_id FOREIGN KEY (user_id) | |
REFERENCES users(user_id) | |
ON UPDATE CASCADE | |
ON DELETE CASCADE | |
); | |
CREATE TABLE wishlist_cars ( | |
car_id INT, | |
wishlist_id INT, | |
CONSTRAINT fk_car_id FOREIGN KEY (car_id) | |
REFERENCES cars(car_id) | |
ON UPDATE CASCADE | |
ON DELETE CASCADE, | |
CONSTRAINT fk_wishlist_id FOREIGN KEY (wishlist_id) | |
REFERENCES wishlists(wishlist_id) | |
ON UPDATE CASCADE | |
ON DELETE CASCADE | |
); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
updated 11/13/2021