Mysql Saving a Serialized Array

Question

I have 3 tables:

• A table of regions in which there are 1,500 cities.
• Category table with 1800 categories.
• Table of companies containing enterprises.

What I need to do is capture a city, for example Brimingham and a list in the array, in the categories of which there are companies that use our main table of companies, so we do not have categories stored in the array that do not have companies in Brimingham.

The problem is that the size of the array is stored, when I fill all cities with a serialized array, I can’t even open the table for viewing. Array example below:

a: 9: {s: 8: "Bailiffs"; s: 1: "1"; s: 20: "Business Advisers"; s: 1: "1"; s: 25: "Car garages and mechanics": s: 1: "1"; s: 35: "Agriculture and other animals"; s: 1: "2"; s: 19: Fashion Accessories s: 1: "1"; s: 6: "Hotels", s: 1: "1"; s: 20: "Postal Services", s: 1: "1"; s: 13: "State of the schools"; s: 1: "1"; s: 14: "Wooden Masters"; s: 1: "1";}

Can anyone suggest an alternative solution?

Greetings

Answer 1

I would suggest a completely different approach, completely getting rid of the storage problem, and should make your application more efficient. Storing serialized arrays, full of information that can be extracted from your database, is in any case redundant and extremely inefficient. The best approach here is to normalize your data.

You should create a fourth table, possibly 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 city / region you need to instead populate this table with the categories that are in that city. The data size is very small, since all you store is a pair of identifiers.

When the time comes to get categories for a given region, you just need to run a simple SELECT :

 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 repeat the results and you will have all the categories for this region.