Database table structure design

Himanshoo · May 23, 2024, 7:03am

Hello everyone, Greetings…

I am designing database for a ecommerce website. Below is the tables structure

Master Table Attributes contains:
ID
AttributeName
eg. Size, Color, Material, etc

Master Table AttributeValues contains:
ID
AttributeName
AttributeValue

eg. Size-M, Size-L, Color-Red, Color-White, etc

Product Table contains:
ID
Category
Title
ShortDesc
LongDesc
Brand
Vendor
Image1
Image2
Image3
Image4
Image5

Product Attributes Table contains:
ID
ProductID (from Product Table)
AttributeName
AttributeValue
Quantity
PurchasePrice
SellingPrice
AgeGroup

Product SKUs Table contains:
ID
ProductID (from Product Table)
ProductAttributeID (from Product Attributes Table)
SKU
SaleStatus

Is my tables structure are defined fine or not?
if not , please suggest.

Secondly, for every attribute there must not be any problem,
like for size attribute it IS M,L,S,XL
like for material attribute it is Silk,Cotton, etc
But in the case of color attribute how could i store color name with Hex Code so that actual color be displayed for selection

example, color attribute selected and then attribute value to be written it could be red, white, blue i.e., text value only

Thanks
Himanshoo

Himanshoo · May 23, 2024, 10:30am

I think add one more field in my AttributeValues table as attributehexcode.
and through code i must add hexcode in table only in the case of color attribute.

example:
size-M-
size-L-
color-red-#FF0000
color-pink-#FFC0CB

Thanks
Himanshoo

Himanshoo · May 28, 2024, 5:24am

have u asked something wrong or my question is not valid / not understandable.
for fix 2 product variants like size n color,
it can be stored in one table:
id
productid
size
color
qty
price
sprice

but where product has 3 or 4 or 5 variants then thius structure cannot be used. i could use like below:
id
productid
variant_type
variant_value

but how price and qty be stored

Thanks
Himanshoo

asshleyymariei · June 24, 2024, 9:39am

Can you explain what are you trying to say?

Zwerver · July 11, 2024, 9:43am

TABLE ATTRIBUTES
ID
NAME

// Which values to use for which attribute
// f.e. <OPTION value="[STORED]" > [DISPLAY]</OPTION>
TABLE ATTRIBUTE_VALUES
ID
ATTRIBUTE_ID
DISPLAY f.e. color-red
STORED f.e. #FF0000

TABLE PRODUCTS
ID
CATEGORY
TITLE
…

//Allow zero or more images for a product
TABLE PRODUCT_IMAGES
ID
PRODUCT_ID
IMAGE_FILE_NAME

// Which attributes can be set for which product
TABLE PRODUCT_ATTRIBUTES
PRODUCT_ID
ATTRIBUTE_ID

// Order/purchase by user
TABLE ORDER
ID
USER_ID
TIMESTAMP

//Ordered products in this order
TABLE ORDERED_PRODUCT
ID
ORDER_ID
PRODUCT_ID
QUANTITY
…

//Product attributes for specific ordered product
TABLE ORDERED_PRODUCT_VALUES
ORDERED_PRODUCT_ID
ATTRIBUTE_ID
ATTRIBUTE_VALUES_STORED