Hi folks,

I'm after some basic design advice. My own DB background is codasyl
databases rather than relational, and hierarchic design there is much more
obvious.

I have three tables that form a hierarchy and that have evolved and
changed over the last year:

mysql> describe regions;
+-----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| region_id | tinyint(3) unsigned | NO | PRI | 0 | |
| name | varchar(20) | NO | | | |
+-----------+---------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> select * from regions;
+-----------+------------------+
| region_id | name |
+-----------+------------------+
| 1 | Wales |
| 2 | South West |
| 3 | South Central |
| 4 | South East |
| 5 | London |
| 6 | Anglia |
| 7 | Midlands |
| 8 | North East |
| 9 | North West |
| 10 | Scotland |
| 11 | Northern Ireland |
| 12 | Isle of Man |
| 13 | Channel Islands |
| 20 | Overseas |
+-----------+------------------+
14 rows in set (0.00 sec)
mysql> describe postcode;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| postcodeID | varchar(4) | NO | PRI | | |
| region | tinyint(2) | NO | | 0 | |
| town | varchar(30) | NO | | | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from postcode limit 5;
+------------+--------+------------+
| postcodeID | region | town |
+------------+--------+------------+
| AB | 10 | Aberdeen |
| AL | 6 | St Albans |
| B | 7 | Birmingham |
| BA | 2 | Bath |
| BB | 9 | Blackburn |
+------------+--------+------------+
mysql> describe towns;
+-------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | char(31) | NO | | | |
| postcode_id | char(2) | NO | | | |
| main | enum('Y','N') | NO | | N | |
| region | tinyint(3) unsigned | NO | | 1 | |
+-------------+----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> select * from towns limit 5;
+----+-------------+-------------+------+--------+
| id | name | postcode_id | main | region |
+----+-------------+-------------+------+--------+
| 1 | Aberystwyth | SY | N | 1 |
| 2 | Belfast | BT | Y | 11 |
| 3 | Birmingham | B | Y | 7 |
| 4 | Bournemouth | BH | Y | 3 |
| 5 | Bradford | BD | Y | 8 |
+----+-------------+-------------+------+--------+
5 rows in set (0.01 sec)
mysql>

The hierarchic nature (which isn't as obvious as perhaps it should be) is
region, then postcode, then town. (A postcode of NP for example is
Newport, but also contains Caerleon and Cwmbran; HP is Hemel Hempstead,
but also includes High Wycombe!)

As you can see from the description, and samples, there is a bit of
duplicated data, and it's this that's making things a little awkward.

The guy who owns the system wants the postcode table to be the one that
drives everything for the remainder of the database, however as the
programmer it's a bit of a nightmare to maintain. (eg: postcode.town is
the same as town.name, except that there are entries in the town table
that are not in the postcode table).

What /I/ want to do is simply to remove the town name from the postcode
table, and probably the region off the towns table. Nigel is adamant that
the name has to stay on both, and that if necessary we get rid of the
towns table, or possible merge the towns and postcode table.

The problem is that it's the town table that then links to the rest of the
database. For example, we have a models table, and a model_towns table:

mysql> describe model_towns;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| model_no | smallint(4) unsigned | NO | PRI | 0 | |
| town_id | tinyint(3) unsigned | NO | PRI | 0 | |
+----------+----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

which is basically just a resolver table for the many to many
model<->towns relationship.

We also have photographer & photographer_towns; stylist and stylist_towns;
and even studio and studios_towns tables. (Though having literally only
just thought about it, that very last is probably unnecessary as studios
can't move around!)

As I hope you see, it's actually the towns table that is really the
driving table from a purely practical point of view.

What I'm looking for is some advice on the best/simplest design for these
tables.

Many thanks,

Dave

--
Dave Stratford ZFCA
http://daves.orpheusweb.co.uk/
Hexagon Systems Limited - Experts in VME systems development