Note on Shopify and Car DB design 20-Jan-2021

TODO bash crawl shopify variants / products data. 

https://stackoverflow.com/questions/18132144/handle-race-condition-between-2-cron-tasks-what-is-the-best-approach

https://dba.stackexchange.com/questions/235242/database-design-for-cars

The automotive industry has plenty of exceptions, complicating such a project. For example, the Ford Escort ZX2 was marketed as a trimline of the Escort for the first couple of years, then stood alone as just "Ford ZX2" (with bumper emboss to match) starting in MY1999. So was it a submodel, or a standalone model, which happened to be named in reference to another model? Did it go from one to the other in 1998?

CREATE TABLE Manufacturers

(

ManufacturerID  SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,

ParentManufacturerID    SMALLINT REFERENCES Manufacturers (ManufacturerID),

ManufacturerName    VARCHAR(100) NOT NULL UNIQUE

);

CREATE TABLE Models

(

ModelID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

ModelName   VARCHAR(100) NOT NULL,

CombinedName    VARCHAR(100) NOT NULL UNIQUE  -- Generally, the manufacturer name || ' ' || model name

);

CREATE TABLE BodyStyles

(

BodyStyleID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,

BodyStyleName   VARCHAR(100) NOT NULL UNIQUE

);

CREATE TABLE `Lines`

(

LineID  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

-- Foreign keys

ManufacturerID  SMALLINT NOT NULL REFERENCES Manufacturers (ManufacturerID),

ModelID INT NOT NULL REFERENCES Models (ModelID),

BodyStyleID SMALLINT NOT NULL REFERENCES BodyStyles (BodyStyleID),

-- Attributes

ModelYear   DECIMAL(5, 1) NOT NULL,

PowerSystem VARCHAR(30),

Seating TINYINT NOT NULL,

SubModelName    VARCHAR(100),

TrimLineName    VARCHAR(100),

UNIQUE (ManufacturerID, ModelID, BodyStyleID, ModelYear, PowerSystem, Seating, SubModelName, TrimLineName)

);