PostgreSQL – Organizing Relational Database Logical Model with Varying Categories

database-designpostgresql

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.

Best Answer

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..

Related Question