MySQL database design question.

sijis

I have a question about my db design. I want to know if what I've done is the 'best' way to do it. All the db is doing is keeping track of an item (mostly dvd's), its price and what country the item was created in.

I have 2 tables with the following fields.

tblItems

ItemID bigint(20) (auto_increment)
ItemSKU varchar(25)
ItemName varchar(100)
country set('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13') ItemCost decimal(7,2)

tblCountryList

country_id tinyint(4)
country_name varchar(35)

Now in the table tblItems the 'country' field correlates to tblCountryList.country_id's id. I know that field is of 'set' type, which the highest number it can have it 65, I believe. I really don't think the tblCountryList will have 65 values.

I have read that there is an alternative to what I have. I could create another tables which basically correlates country to country_id. So every item can be of multiple countries. The way I have it, I could also have several countries for each item.

I guess it would be something like this

tblItemToCountry

ItemID
Country_id

My question basically is, should I stick to what I have or should I implement that correlation table? Maybe there is another option.

I hope I make some sense.

Thanks.

ahundiak

Ask the question: Can an item be created in several countries? If the answer is yes then you have a many to many relation between items and countries and you should use the correlation table.

barand

Either way, don't use 'set' type for country. Just use tinyint as in country table.

sfullman

Or, avoid a second table altogether. Do you really need a second table just for a country? If you're going to have unique data on specific shipping policies for that country, legal notes, yes, but...

There's a 2-digit code for every country (we're us, britain is uk, mexico is mx, etc.), just have Country CHAR(2). Find the list I'm referring to on the interent, and have PHP do the work to translate in and out based on an array.

PHP is lightning fast on arrays; you start adding other tables and doing complex joins (a lot) and you'll be doing more typing, and it can't be beneficial.

Sincerely,
Sam Fullman
Compass Point Media

sijis

Thanks for all the replies.

My real concern was that in the future I might be adding a country that wouldn't be in the tblCountryList and as time passed it would pass the SET type max of 65 (from what i recall).

Based on ahundiak's question, i think i will use the correlation table.

In response to sfullman, the data about the country is not for shipping, tax nor legal stuff, its just to have 'extra' information about the item (dvd). I'm not sure if you are suggesting to not use a table for the countries.

I must also add that i'm creating a list (using the tblCountryList) to see what dvd's are created in a certain country. That's the reason I've created that table also. If not, i would have just put user input data into the db (but you should never trust a user input). That table basically has a dual purpose.

sfullman

I AM suggesting you not use another table -- unless you have a "record" that is necessary just for the country.

Control the user input by a dropdown list. You are the webmaster, use HTML tools to control the environment. If you have the Label be United States, but the value be "us", etc., you've got two bytes of storage you can index easily, and it should be very fast.

Anyway, that's my opinion.
Sam