database - how should I design tables to store a data in a specific format based on user nationality? -

let's have data regarding size differs based on user nationality, example trousers size. example italy uses different sizing scheme usa. want ask user insert trouser size based on sizing scheme of nationality. let's user selects italy, user presented list of possible italian trousers sizes. how do based on stored nationality in user_data table?

example:

user table

user_name user_nationality user_trouser_size

size table

italian_sizes american_sizes

when registering user sets nationality, example "italian" when asked chose size trousers system return automatically italian_sizes size table , store user account.

what tables need , how reference between them?

thanks

here 1 simple way store them. it's no means way, nor best, think it's reasonably sound , understandable. code sql server other database systems should similar.

create table sizes (     countrycode nvarchar(3),     size int,     constraint px_sizes primary key(countrycode,size)     //other fields go here )  create table users (     id int,     countrycode nvarchar(3),     size int,     //other fields go here     constraint pk_users primary key (id),     constraint fk_users_sizes foreign key         (countrycode,size) references sizes(countrycode,size)  )  //sample data     insert sizes select 'usa',42     insert sizes select 'usa',43     insert sizes select 'ita',18     insert sizes select 'ita',19      insert users select 1,'ita',19     insert users select 2,'usa',43 

now @ first glance looks duplication of data, it's doing using sizes table data source list of sizes particular nationality (countrycode) also referential constraint size data in users.

so it's not possible end invalid data in users table because constraints prevent entering invalid countrycode/size combination. can enforce further referential integrity making sizeusers.countrycode foreign key reference countries table, example.

the advantage of using structure opposed having individual columns sizes in each country becomes easy extend when need add more countries - don't need change sizes table, add more entries appropriate country code.

to sizes nationality like

select * sizes countrycode='usa' order size asc 

hope helps.