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 sche开发者_JAVA百科ma design about this? Thank you.
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.
Here are some suggestions:
- Make sure that
Style.styleNumber
has a unique index - Make sure that
Garment.style
has a proper index for joins/searches - Consider giving
Garment
its own unique id (auto_increment) - Consider making
colors
,sizes
either enum types or separate tables with relationship to foreign key inGarment
andStyle
精彩评论