[RESOLVED] opinion on normalised data structure

mystrymaster

Alright I have a contact list that I need to maintain for a couple of areas. and here is my proposed table structure

BOOTH
id
name
op_center_id
city_table (will contain the name of the city table to pull from 20 possible choices)

OP_CENTER
id
name

CITY_TABLE(remember 20 possible names ex. Atlantic, Mercer, PAB1, etc, will auto append '_city' to the name)
id
name
grid

ORDER_TABLE(remember 20 possible names here, same as for city but will auto append '_order' to the name)
id
station_id (id of a station number in a seperate station table)
city_id
position (number 1-9, this will provide the order they are in the city list)

Does this seem like I am doing this right or would someone else have better proposed structure.

The part that is worrying me is the getting the table name from the database.

laserlight

The part that is worrying me is the getting the table name from the database.

That's a very good hint that your database is not normalised.

Put away the CITY_TABLE and ORDER_TABLE.
What are the relationships between booths, op centers and cities?

mystrymaster

I have 4 op centers

Each op center has 6-10 booths

Each booth has 50-400 cities

Each city has 1-9 stations in priority order 1-9

laserlight

The relationship between op centers and booths is one to many.
The relationship between booths and cities is one to many.
The relationship between cities and stations is one to many.

Based on the above, I think you're looking for something like:

Table: opcenters
opcenter_id
name

Table: booths
booth_id
opcenter_id
name

Table: cities
city_id
booth_id
name
grid

Table: stations
station_id
city_id
name
position

The relationship between stations and station positions is actually many to many, but it probably is alright to ignore that and allow position to be entered directly to the stations table.

mystrymaster

First off thanks for your help on this

Second the issue I see with that is

1 station can be assigned to many cities in varying positions

laserlight

Second the issue I see with that is

1 station can be assigned to many cities in varying positions

Then you should have stated this earlier.
There is a many to many relationship between cities and stations.

Table: cities
city_id
booth_id
name
grid

Table: stations
station_id
name
position

Table: cities_stations
id
city_id
station_id

If you havent already done so, you might want to check out the phpbuilder article on Database Normalization And Design Techniques

mystrymaster

I have read that article and I am just not grasping this portion of it.

with your proposed structure

the stations table holds the position identifier however this position would entirely depend the city we are refereing to would this mean that it would be

Table: stations
station_id
name

Table: cities_stations
id
city_id
station_id
position

so then I would have 1 station record for each station in existance
and
1 cities_station record for each city they have and that record would hold which position they are in correct?

the reason why my logic was to have the dynamic table names and city listings is that with one city table it could grow to well over 9,000 records per op center which woudl be searched roughly about 2,000 a day per op center and I was worried about speed.

Are my speed concerns for naught?

laserlight

so then I would have 1 station record for each station in existance
and
1 cities_station record for each city they have and that record would hold which position they are in correct?

Actually, you would have one record in the cities table for each city, and one cities_stations record for each city-station pairing. Moving the position field into the cities_stations table sounds correct.

Are my speed concerns for naught?

Probably. 36000 records isnt very many.
Still, if it is really is necessary you can always change to a less normalised structure.

mystrymaster

well thank you very much for you assitiance through this problem - I am getting closer on the full normalisation of the db, I get closer with each one.