2

Supposing I have this database design which I have researched.

Table: Products
ProductId | Name     | BaseUnitId
1         | Lab gown | 1
2         | Gloves   | 1
FK: BaseUnitId references Units.UnitId

Table: Units
UnitId | Name
1      | Each / Pieces
2      | Dozen
3      | Box

Table: Unit Conversion
ProdID | BaseUnitID | Factor | ConvertToUnitID
1      | 1          |  12    | 2
2      | 1          | 100    | 3
FK: BaseUnitId references Units.UnitID
FK: ConvertToUnitId references Units.UnitID

Table: Product Attribute
AttribId | Prod_ID | Attribute | Value
1        | 1       | Color     | Blue
2        | 1       | Size      | Large
3        | 2       | Color     | Violet
4        | 2       | Size      | Small
5        | 2       | Size      | Medium
6        | 2       | Size      | Large
7        | 2       | Color     | White
FK: Prod_ID references Product.ProductID

Table: Inventory
Prod_ID  | Base Unit Qty | Expiry
1        | 12            | n/a
2        | 100           | 2020-01-01 
2        | 100           | 2021-12-31
FK: Prod_ID references Product.ProductID

How can I breakdown the inventory per unit per attribute?

e.g How can I get the inventory of SMALL VIOLET GLOVES? LARGE WHITE GLOVES?

Any suggestions? My idea is to create another table which will link product unit, product attribute and quantity.

But I dont know how to link the size attribute and color attribute to a unit.

Lastly, is there something wrong with this design?

Bigboss
  • 355
  • 1
  • 3
  • 17
  • 1
    Product Attribute is a classic use of [EAV-design](https://stackoverflow.com/a/23950836/3404097) to encode data & metadata of a straightforward-design table with nullable columns with names that are values from column Attribute & values that are values from column Value. Write a view for that table & your queries become straightforward. That table is a left join of straightforward no-null supertype & subtype tables. [How can you represent inheritance in a database?](https://stackoverflow.com/q/3579079/3404097). Moving a value to (part of) a column name & vice versa is called pivoting. – philipxy Sep 30 '19 at 08:36
  • A [mre] please. Include parts you can do. "Any suggestions?" is not an on-topic question. If you phrased what you wanted clearly, you could google this & find it is a faq. – philipxy Oct 04 '19 at 03:48

1 Answers1

1

I think it is quite wrong to split off the attributes of a product into a different table. I understand the desire to normalize, but it should be done differently.

I'd handle a product and its attributes like this:

CREATE TABLE product (
   id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   name text NOT NULL,
   baseunit_id bigint NOT NULL REFERENCES unit
);

CREATE TABLE inventory (
   id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   product_id bigint NOT NULL REFERENCES product,
   color integer REFERENCES product_color,
   size integer REFERENCES product_size,
   other_attributes jsonb
);

That also makes sense if you think about in natural language terms: “How many dozens of large blue gloves do we have on store?”

Attributes that do not apply to a certain product can be left NULL.

I make a distinction between common and rare attributes. Common attributes have their own column. Rare attributes are bunched together in a jsonb column. I know that the latter is not normalized nor pretty, but varying attributes are not very suited for a relational model. A GIN index on the column will allow searches to be efficient.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • What you said makes a lot of sense. I get what you are saying but the problem I can see with this design is how about if I want to add new attribute to gloves? E.g. I want to add new attribute such as madefrom: rubber or fabric. How do I handle that? – Bigboss Sep 27 '19 at 03:49
  • By addind a new column to the table. If you think that will happen regularly, then the assumption that I stated in the last sentence does not hold, and a different design is necessary. – Laurenz Albe Sep 27 '19 at 04:03
  • Given the circumstances. Can you give me a design which will handle that scenario? – Bigboss Sep 27 '19 at 04:35