database schema design about clothes

Question

I'm working on a fashion store's inventory control, but stuck at the clothing model.

There should be a Style class,

public class Style{
   String styleNumber;
   String[] colors;
   String[] sizes;
   int quantity;

}

And also a detail Garment:

public class Garment{
       Style style;
       String color;
       String size;
       int quantity;

    }

For example, one style has two colors, and each color might has four sizes, therefore, the garments might have 2*4 . We need to query inventory of specific color, or size.

Could you guys give me some hints about the database schema design about this? Thank you.

Answer 1

This is that you need, I suspect.

TABLE: Colour

ID    Colour
1     Green
2     Red

TABLE: Size

ID    Size
1     Small
2     Medium

TABLE: Garment
ID    ID_COLOUR  ID_SIZE  INVENTORY
1     1          1        3
1     1          2        1

With this approach you can choose whether you keep rows with an inventory of 0 or not.

The issue with this approach is that you do not trac kdistinct styles, you just create them as you have stock in the GARMENT table.

If you do want to track styles, use this:

TABLE: Colour

ID    Colour
1     Green
2     Red

TABLE: Size

ID    Size
1     Small
2     Medium

TABLE: Style
ID   ID_COLOUR ID_SIZE
1    1         1
2    1         2

TABLE: Garment
ID   ID_STYLE  INVENTORY
1    1         10
2    2         3

Real design decisions depend on real requirements. When you identify the requirements for this database, then that should guide you on the correct approach.

Answer 2

Here are some suggestions:

1. Make sure that Style.styleNumber has a unique index
2. Make sure that Garment.style has a proper index for joins/searches
3. Consider giving Garment its own unique id (auto_increment)
4. Consider making colors, sizes either enum types or separate tables with relationship to foreign key in Garment and Style