CODEWITHSUNDEEP
mysqldatabasedatabase-design
StackOverflowNewbie
StackOverflowNewbie

Reputation: 40653

MySQL: How to design table for conversion (e.g. dress sizes)?

A size "2" dress size in the US is a size "6" in the UK.

How can I model this in a table (or series of tables) in such a way that I can correlate the US 2 with the UK 6? One possible way is to do a table with columns like this:

The problem with the design is that I couldn't easily get a list of all dress sizes for a given 2 letter country code (e.g. "us") since that information is a column name.

Also, I want to be able to know whether or not one dress size is larger or smaller than another. I can't rely on the numerical values being higher or lower to determine that apparently since, for example, Russia's size "56" is smaller than its size "24". See:

enter image description here

Upvotes: 1

Views: 757

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

Your grid represents one size category (dresses - there exist other size categories like shoes, jeans, bras, whatever.)

Then there are the columns in your grid, which we can call regions a name that matches USA, UK/AU/NZ, etc. perfectly, but also includes S-M-L which is not really a "region".

Then there are the rows in your grid representing each one size of the given category.

At last there are the cells in your grid, we could call regional sizes.

Makes four tables:

categories

id | category
---+------------
C1 | dress sizes
C2 | shoe sizes

regions

id | region
---+--------------
R1 | S, M, L, etc.
R2 | USA
R3 | UK, AU, NZ

sizes

id | id_category | sortkey
---+-------------+--------
S1 | C1          | 1
S2 | C1          | 2
S3 | C1          | 3

regional_sizes

id   | sizes | id_region | size
-----+-------------------+-----
RS1  | S1    | R1        | S
RS2  | S1    | R2        | 2
RS3  | S1    | R3        | 6
RS4  | S2    | R1        | S
RS5  | S2    | R2        | 4
RS5  | S2    | R3        | 8

Upvotes: 1

Related Questions