[SOLVED] MySQL Table for postcodes... what is best?

[-Mike-]

Essentially I am working on a web project. At present there is a database table which will hold post codes. These post codes will represent areas that their service covers. So if a postcode appears in the table, it's somewhere that they can deliver their product to.

 

Obviously, if someone wants a service - they'd like to know whether it can be provided to them - so we will search the table for their postcode, and provide an answer as to whether they are covered or not (then they may sign up).

Whether it gets us anywhere remains to be seen. However, their previous coder created the database as seen below. Currently it holds very little info (testing only) - about 25 entries.

 

 

CREATE TABLE postcodes (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
start VARCHAR(4),
end CHAR(3),
);

 

As it stands, a postcode (for example, SW1 4EN) would have SW1 in the start, and 4EN in the end column. If we have SW1 2UV - we then have another row with SW1 entered in the start, and 2UV in the end.

If another SW1 was added, we have a third row with SW1 and then the end value of whatever. So the "start" column will hold many duplicates.

 

SW1  4EN

SW1  2UV

SW1  6JH

 

Personally this seemed wasteful. However, linking SW1 to another table which then held all the ending parts would result in a huge amount of database tables as far as I know - around 2840 or so (for all post codes in the UK). Naturally chances of it being that large are slim, but certainly several hundred tables would be a possibility!

 

That seems a bit "mad" when I thought of it like that... but is that the best design?

 

Also, considering it's current structure - there is no advantage that I can see beyond having (for example) just a single column of "SW1 4EN" or "SW14EN" instead.

 

I assume the "start" and "end" are useful because you may search for SW1 to see all areas covered within that post code (ie return all end values for matching start value), so therefore its better than having a single column which sticks teh two together (eg column postcode, varchar( - entry value like "SW12 4EN" - which means you cannot search for SW12 post codes, as you'd need to go through each entry and "split" it to match the first four characters.

 

I'm not very experienced with designing database tables. Usually I have no issue whatsoever in creating tables for people, and linking them to records etc - but this table I am a bit baffled about how best it should be structured. Bearing in mind that currently the table could have 2840 different "start" entries - and that each start entry may be repeated several times to different end entries, I foresaw some searches requiring a lot of iterations - eg, someone types in the first part of a post code, we then have to run a query where start = "SW1" and return all the "end" values, to then get the unique ID.

 

I hope this isn't too jumbled or muddled, I'm confused as to what's best in this instance! Any suggestions from experienced designers would be gladly accepted, as I wish to make this table the best it can be (primary use will be for searches, which may be carried out fairly frequently. However, new postcodes may be added as well).

 

Thanks to anyone who can offer some advice, and sorry that this may seem trivial to some.

[biohazardep]

You can search for partial postcodes even if you combine the start and end columns.

 

For example, to search for postcodes that start with SW1 you can do this query:

SELECT * FROM postcodes WHERE postcode LIKE 'SW1%'

 

You can use % as a wildcard, so %4EN means postcodes that end with 4EN.

Or SW%EN means postcodes that start with SW and end with EN.

 

Hope this helps.

[bubblegum.anarchy]

SELECT * FROM postcodes WHERE start = 'SW1';

 

is much faster than

 

SELECT * FROM postcodes WHERE postcode LIKE 'SW1%'

 

I suggest you stick with the original system.

[fenway]

SELECT * FROM postcodes WHERE start = 'SW1';

 

is much faster than

 

SELECT * FROM postcodes WHERE postcode LIKE 'SW1%'

 

I suggest you stick with the original system.

Agreed... the table will never grow beyond a fixed size.

[-Mike-]

Many thanks for the input guys, reassuring to know that it's basically as good as it can be.