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)
);
Comments
Post a Comment