Forum Moderators: coopster
I am writing a database where regional information is stored for each of the entries. e.g. Each model of widget is sold in a number of countries. Some are sold only in USA, some only in Australia. Some are sold in South Africa, Australia, UK and Spain. As the widget empire expands, more regions are added to the distribution network.
My question is, what is the most effective way of storing this in a database? At the moment, I have a table for regions. Each region is an entry in the table, with an ID and text for the name.
Then I have a column for each region in the widgets table. This is a binary value, representing whether the widget is sold in that particular region. When a new region is added, a new entry is made in the regions table and a new column is added to the widgets table. This does mean that there are quite a few columns in the table (about 50), but I presume MySQL can handle that?
This is working fine, as I can display which regions each widget is sold in. The problem comes in with the search function.
Say a user wants to search for widgets that are available in the US. I would like them to be able to enter 'United States' into the search box, then search the widget table and display results. As the design stands at the moment, it would not return any results, as only 1's and 0's are stored in the US column of the widget database.
One possible solution would be, instead of entering a binary value, to store the full name of the region in the widget table instead. i.e. if a widget is available in Australia, I would put 'Australia' into the Australia column, instead of just a 1. This would, however, use quite a bit more resources than the binary digit.
Any thoughts on this? Am I on the right track? Should I be worried about filling the table with so much extra data, just so that I can get the search function working? There will probably only ever be a few hundred widgets for sale, so the table isn't ever going to run into thousands of entries.
and then process the information with php. Also give the user to search all regions.
The other way would be table: id, id_region, widget... and create an entry for each region (just put region's id there, or even name). However I don't know which solution is better.
best regards
Michal Cibor
Each region is an entry in the table, with an ID and text for the name.
Rather than a 1 or a 0 for each region in the 'data' table, you could store the row ID of the table you mentioned above.
So, if the US was in row #3 instead of 1 for the US store 3. This way you could have the best of both worlds... short queries, and unique at the same time. This is how I do most of mine for states. I give them a number to select by and then store the text information for display purposes only.
You could also cut down on the col's in the 'data' table this way, becuase you would not have to worry about a unique col for each region, all you would have to do is create the region row in the 'region name' table first, then store the ID in the region col of the 'data' DB.
The key savings here would be you are only comparing text 1 time compared to comparing for every row... Which is more efficient?
3 letter word 26 + 26 + 26 : 3 digit number 10 + 10 + 10
Hope this makes a little sense.
Justin
The widget entry still wouldn't contain the actual text 'Australia'. I'm looking to use the simple SQL search string, to look for the text 'Australia' in the widget table. See what I mean?
Another problem comes in when a widget is sold in more than one region. Then I would need to have more than one number in the regions column. The regions need to be able to be changed easily as well. Which is why I was thinking that using a column for each region would be easiest.
I might use Michal's idea of selecting the regions to search and then having an extra little box where the user can type the name of the widget. Thus, they are able to search with text and refine their search region with select boxes. Then I would be able to use Justin's idea of just storing the row id instead of text. Not quite what I was imagining, but probably much easier to implement and would almost definitely use much fewer resources.
Thanks again chaps, WW to the rescue!
