I am in the process of developing a database with the following headings:
Region , City , Suburb
Each heading will have around 4000 rows so I am guessing it would be better if I use a separate table instead of one table with many columns.
What would be the best way to set the Region id City id and Suburb id? for when I want to select the specific regions information?
While there are several ways that you could model these tables, here is one way to set it up:
Region
id int, -- PK
name varchar(50)
City
id int, -- PK
region_id int, -- FK
name varchar(50)
Suburb
id int, -- PK
city_id int, -- FK
name varchar(50)
Then if you needed data you would JOIN the tables
SELECT r.name as RegionName
, c.name as CityName
, s.name as SuburbName
FROM Region r
INNER JOIN City c
ON r.id = c.region_id
INNER JOIN Suburb s
ON c.id = s.city_id
I assume cities are part of regions and suburbs are part of cities. If so, you may need more tables.
One table for your regions, which has all the information for them with a column ID (auto-incrementing).
One table for your cities, which has all the information for them with a column ID (auto-incrementing).
One table for your suburbs, which has all the information for them with a column ID (auto-incrementing).
And then, one table relating your cities to your regions. One column is ID (auto-increment), one is city (where your city ID's from your city table go), and one is region (where you region ID's from your region table go). This lets you know which cities belong to which region.
Next, one table relating your suburbs to your cities. One column is ID (auto-increment), one is region (once again from your region table), and one is suburb (from your suburbs table). This lets you know which suburbs belong to which cities.
With this setup, you can use INNER JOIN to get your cities or suburbs and you will have both the data for the suburb and the data for the more-general categories.
Hope this helps, I've been developing relational web applications for a long time, and have found that my aforementioned method works very well.
