I’m having difficulty designing a schema for my postgresql database that relates to the items a store can create.
There are three possible categories: (1) Clothing (2) Footwear (3) Other.
The problem is that each clothing item can vary in the colours available, the sizes available, and number of images. Meaning a user can click to create an item, and then continue adding more available colours with their respect size and quantity. Something similar to:
[ITEM TITLE input] [COLOR input] - [SIZE input] - [QUANTITY input] [IMAGES input] [+ to add more colors]
If the only variable value was color, I could just “normalize” the model, and create an
available colours table with a relationship back to the item. I’m not sure how to handle the additional variable of available sizes. What’s more, the
Other category wouldn’t have colours or sizes available to it, only quantity.
Does anyone know if there’s a solution to this type of problem, or a design pattern that I could read up on that might apply? I’m trying to wrap my head around how I could design this model and I’m coming up blank. Ideally I’d be working with a nosql database in this case but that’s not possible.
Something like this,
CREATE SCHEMA catalog; CREATE TABLE catalog.item ( item_id serial PRIMARY KEY, item_name text ); CREATE TABLE catalog.color ( color_id serial PRIMARY KEY, color_name text ); CREATE TABLE catalog.size ( size_id serial PRIMARY KEY, size_name text ); CREATE TABLE catalog.item_color ( item_id int REFERENCES item, color_id int REFERENCES color ); CREATE TABLE catalog.item_size ( item_id int REFERENCES item, color_id int REFERENCES size );
Now, we need orders
CREATE SCHEMA orders; CREATE TABLE orders.shipment ( shipment_id serial PRIMARY KEY, customer_name text, customer_address text ); CREATE TABLE orders.item ( item_id serial PRIMARY KEY item_id int REFERENCES catalog.item, color_id int REFERENCES catalog.color, size_id int REFERENCES catalog.size, ); CREATE TABLE orders.shipment_item ( shipment_id int REFERENCES orders.shipment, item_id int REFERENCES orders.item );
to query the catalog,
SELECT item_name, color_name, size_name FROM catalog.item LEFT OUTER JOIN catalog.item_size USING (item_id) LEFT OUTER JOIN catalog.item_color USING (item_id)
Then you just have a
shipment schema which includes different order items.
Now for your categories, you can either create a category table that has (1) Clothing (2) Footwear (3) Other. in the same style, or you can just add a tags column to
catalog.item and tag them with clothing and footwear or whatever. Whatever you want to do..