Brands & Clothes, PostgreSQL

Language: Layout:

CREATE TABLE Brands
(
BrandId int NOT NULL PRIMARY KEY,
BrandName varchar(100) NOT NULL 
);
 
CREATE TABLE Clothes
(
ClothesId int NOT NULL PRIMARY KEY,
ClothesName varchar(100) NOT NULL 
);

CREATE TABLE Attrib
(
AttrId int NOT NULL PRIMARY KEY,
AttrName varchar(100) NOT NULL 
);

CREATE TABLE BrandsClothes
(
BrandId int NOT NULL REFERENCES Brands(BrandId),
ClothesId int NOT NULL REFERENCES Clothes(ClothesId),
VievingUrl varchar(300) NOT NULL,
Price money NOT NULL,
PRIMARY KEY (BrandId, ClothesId)
);

CREATE INDEX ix_brandsclothes on BrandsClothes(ClothesId, BrandId);

CREATE TABLE BCAttributes
(
BrandId int NOT NULL REFERENCES Brands(BrandId),
ClothesId int NOT NULL REFERENCES Clothes(ClothesId),
AttrId int NOT NULL REFERENCES Attrib(AttrId),
AttrValue text[],
PRIMARY KEY (BrandId, ClothesId, AttrId)
);

CREATE INDEX ix_attributes on BCAttributes(ClothesId, BrandId, AttrId);
CREATE INDEX ix_gin_attributes on BCAttributes using GIN (AttrValue);

INSERT INTO Brands VALUES 
(1, 'Brand1'), 
(2, 'Brand2');

INSERT INTO Clothes VALUES 
(1, 'Pants'), 
(2, 'T-Shirt');

INSERT INTO Attrib VALUES
(1, 'Color'),
(2, 'Size'),
(3, 'Shape'),
(4, 'Provider'),
(0, 'Custom');

INSERT INTO BrandsClothes VALUES
(1, 1, 'http://mysite.com?B=1&C=1', 123.99),
(1, 2, 'http://mysite.com?B=1&C=2', 110.99),
(2, 1, 'http://mysite.com?B=2&C=1', 75.99),
(2, 2, 'http://mysite.com?B=2&C=2', 85.99);

INSERT INTO BCAttributes VALUES
(1, 1, 1, '{Blue, Red, White}'),
(1, 1, 2, '{32, 33, 34}'),
(1, 2, 1, '{Pearl, Black widow}'),
(1, 2, 2, '{M, L, XL}'),
(2, 1, 4, '{Levis, G-Star, Armani}'),
(2, 1, 3, '{Slim fit, Regular fit, Custom fit}'),
(2, 2, 4, '{G-Star, Armani}'),
(2, 2, 3, '{Slim fit, Regular fit}'),
(2, 2, 0, '{15% Discount}');

SELECT     c.ClothesName, b.BrandName, a.AttrName, array_to_string(bca.AttrValue, ', ')
FROM       BCAttributes bca
INNER JOIN BrandsClothes bc
ON         bc.BrandId = bca.BrandId
AND        bc.ClothesId = bca.ClothesId
INNER JOIN Brands b
ON         b.BrandId = bc.BrandId
INNER JOIN Clothes c
ON         c.ClothesId = bc.ClothesId
INNER JOIN Attrib a
ON         a.AttrId = bca.AttrId
;

drop table BCAttributes;
drop table BrandsClothes;
drop table Attrib;
drop table Clothes;
drop table Brands;


absolute service time: 0,59 sec