Mysql Storing Serialized Array

Question

I have 3 Tables:

1. Regions Table which has 1500 Towns in it.
2. Categories Table which has 1800 categories.
3. Companies Table containing businesses.

What I need to do is grab a town, for example Brimingham and list in an Array which categories have businesses using our main Companies Table, so we don't have any categories stored in the array which don't have businesses in Brimingham.

The problem I have is the size of the array being stored, when I populate all the towns with the serialized array I cant even open the table to browse. See below array example:

a:9:{s:8:"Bailiffs";s:1:"1";s:20:"Business Consultants";s:1:"1";s:25:"Car Garages and Mechanics";s:1:"1";s:35:"Farming Livestock and Other Animals";s:1:"2";s:19:"Fashion Accessories";s:1:"1";s:6:"Hotels";s:1:"1";s:20:"Post Office Services";s:1:"1";s:13:"Schools State";s:1:"1";s:14:"Wood Craftsmen";s:1:"1";}

Can anyone suggest an alternative solution?

Cheers

Answer 1

I'd suggest a totally different approach that gets rid of the storage problem entirely, and should make your app more efficient. Storing serialized arrays full of information that can be retrieved from your database anyway is redundant and highly inefficient. The best approach here would be to normalize your data.

You should create a fourth table, perhaps called 'region_categories', which will be a simple lookup table:

CREATE TABLE region_categories (
  regionId int unsigned not null,
  categoryId int unsigned not null,
  PRIMARY KEY(regionId,categoryId)
);

Now, instead of saving everything to an array, for each town/region you should instead populate this table with the categories that are in that town. Your data size is very small, as all you are storing is a pair of ids.

When it comes time to retrieve the categories for a given region, you just have to run a simple SELECT statement:

SELECT category.*
FROM region_categories AS rc LEFT JOIN categories AS c ON rc.categoryId=c.categoryId
WHERE rc.regionId=[whatever region you're dealing with]

Now you can iterate through your results, and you'll have all the categories for that region.