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

Dave Stratford wrote:
> Hi folks,

The simple answer to all this "normalisation".

Here's a couple of links to get you on your way:
http://dev.mysql.com/tech-resources/...alization.html
http://www.databasedev.co.uk/databas...n_process.html

In article <5l2dcuF5na1bU1@mid.individual.net>,
Paul Lautman <paul.lautman@btinternet.com> wrote:
> Dave Stratford wrote:
> > Hi folks,

> The simple answer to all this "normalisation".

> Here's a couple of links to get you on your way:
> http://dev.mysql.com/tech-resources/...alization.html
> http://www.databasedev.co.uk/databas...n_process.html

Thanks, but I do understand normalisation. As I said in my post, I come
from a codasyl background, and normalisation is just as important there as
it is in a relational database.

To my mind, the best thing to do is, as I want, just to remove the
postcode.town and towns.region fields; however to have a true hierarchic
key on the towns table, logically the towns table requires the region and
postcode keys as well.

However whilst this is a hierarchic structure in one sense, with what I
want to end up with, the keys themselves won't be.

I think what I'm after is some ideas on the best/ideal ways to implement
hierarchic structures in MySQL.

Dave

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

Dave Stratford wrote:
> In article <5l2dcuF5na1bU1@mid.individual.net>,
> Paul Lautman <paul.lautman@btinternet.com> wrote:
>> Dave Stratford wrote:
>>> Hi folks,
>
>> The simple answer to all this "normalisation".
>
>> Here's a couple of links to get you on your way:
>> http://dev.mysql.com/tech-resources/...alization.html
>> http://www.databasedev.co.uk/databas...n_process.html
>
> Thanks, but I do understand normalisation. As I said in my post, I
> come from a codasyl background, and normalisation is just as
> important there as it is in a relational database.
>
> To my mind, the best thing to do is, as I want, just to remove the
> postcode.town and towns.region fields; however to have a true
> hierarchic key on the towns table, logically the towns table requires
> the region and postcode keys as well.
>
> However whilst this is a hierarchic structure in one sense, with what
> I want to end up with, the keys themselves won't be.
>
> I think what I'm after is some ideas on the best/ideal ways to
> implement hierarchic structures in MySQL.
>
> Dave

IMO Nesting is best:
http://dev.mysql.com/tech-resources/...ical-data.html

Here are a couple of other links:
http://www.sitepoint.com/article/hie...-data-database
http://www.intelligententerprise.com...KH0C JUNN2JVN

In article <5l2lptF5n426U1@mid.individual.net>,
Paul Lautman <paul.lautman@btinternet.com> wrote:
> Dave Stratford wrote:

> > I think what I'm after is some ideas on the best/ideal ways to
> > implement hierarchic structures in MySQL.
> >
> > Dave

> IMO Nesting is best:
> http://dev.mysql.com/tech-resources/...ical-data.html

This one was fascinating. I've already done something a bit like this for
something else, but it was not as extensive as this. It will certainly
merit further reading, but to be honest I'm not totally sure it it
suitable for my current problem.

> Here are a couple of other links:
> http://www.sitepoint.com/article/hie...-data-database
> http://www.intelligententerprise.com...KH0C JUNN2JVN

I'll get around to reading these this week.

Many thanks,

Dave

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

"Paul Lautman" <paul.lautman@btinternet.com> wrote in message
news:5l2dcuF5na1bU1@mid.individual.net...
> Dave Stratford wrote:
> > Hi folks,
>
> The simple answer to all this "normalisation".
>
> Here's a couple of links to get you on your way:
> http://dev.mysql.com/tech-resources/...alization.html
> http://www.databasedev.co.uk/databas...n_process.html
>
>
This is not the simple answer. Your problem is how keys migrate from table
to table, becoming foreign keys. All you need is:

Regions(region_id*, name)

Postcode(postcodeID*)

Towns(id*,name,postcode_id**)

* primary key
** foreign key