Skip to content

Instantly share code, notes, and snippets.

@stephepush
Last active July 16, 2022 05:17
Show Gist options
  • Save stephepush/79c188d487fcd3e551085a7d5ee66b9f to your computer and use it in GitHub Desktop.
Save stephepush/79c188d487fcd3e551085a7d5ee66b9f to your computer and use it in GitHub Desktop.
-- 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
);
@stephepush
Copy link
Author

updated 11/13/2021

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment