0

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.

  • 1
    If there a particular reason not to simply include color and size columns in the item table, which are left NULL for items in the "Other" category? This seems simple enough to avoid going with an EAV solution. (Entity, Attribute, Value table: stores unique ID, category name, and option name (for example, 123, 'Color', 'Red'; 124, 'Size', 'XL').. – RDFozz Jun 29 '17 at 21:42
  • Because then each difference in color and size would have to be represented as a new item. So I would have (Summer Dress | Red | XL | 13; Summer Dress | Red | L | 4), etc. and they would all appear as different items when in reality they're not. It would also pose a problem when querying and applying the data to the form inputs (size options, etc.). – Nikola Jankovic Jun 29 '17 at 21:52
  • I see your point. I could see a master_item table, which holds all the information that applies to all colors and sizes of a given base item, and the item table having the color, size, and quantity. With color and size left NULL for "Other" type items (so a master-item of type "Other" should only have one row in item). At some point, anything that must be ordered or counted should logically have a unique ID by which it's ordered, and a quantity representing the number on hand.. – RDFozz Jun 29 '17 at 22:38
  • From what I can tell that seems like an EAV pattern which wouldn't work because both size and color are variable; meaning if I based the table with the color/size information on color, the size column would have to be an array (which postgresql I guess allows) or further normalized which I'm trying to avoid. Also, the query would be incredibly expensive and complicated since this data would need to be placed in html options fields. I might be misunderstanding you though? – Nikola Jankovic Jun 29 '17 at 22:51
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.