CODEWITHSUNDEEP
mysqlsqldatabase-designdata-modeling
koichirose
koichirose

Reputation: 1825

How would you create a clothing size db structure?

I need to design a structure for clothing sizes on an e-commerce site.
We are using mysql

For now, we have something like this:

product(product_id, name, size_id)  
(12, 'formal shoes', 2)  
(224, 'pants', 16)  

size(size_id, gender, category_id)  
(2, 'm', 5)  
(16, 'm', 8)  
(17, 'm', 8)  
(18, 'm', 8)  

category(category_id, category_name)  
(5, 'shoes'),  
(8, 'pants')

size_chart(size_id, country_code, value)  
(2, 'US', '7')  
(2, 'UK', '5')  
(16, 'US', '33w34l')  
(17, 'US', '33w32l')  
(18, 'US', '33')  
(16, 'EU', '48')  
(17, 'EU', '48')  
(18, 'EU', '48')

Now, as you can see the problem is that EU 48 has 3 US equivalents (and they're different in the US), so it would be a mess to sort/filter based on a EU input size.
I noticed that many online shops got this wrong and they either leave the conversion to the user or simply list every single international size for search/filters.

So, for example, if the user search for US 33w34l pants, I'd like to show him pants where size_id is 16,17 or 18.

How would you structure everything?

Upvotes: 1

Views: 2901

Answers (1)

BugFinder
BugFinder

Reputation: 17868

I would give a physical size an id, and then have a list of sizes and countries which match that actual physical size.. so

(random data bears no resemblance to reality)

SizeID 1 = 
country US  size = 33w 41l
country UK size 16
country uk size 18

SizeID 2 = 
country US size = 34w 41l
country UK size 18
country EU size 14

then which ever country they are in, they pick their size, and then you can search for all items with that sizeID.

Upvotes: 1

Related Questions