How Bad is this Database for Address?

Question

I'm still a novice to databases, normalization, etc, and I might need some help. Enclosed here is a portion of my database structure, and I'm thinking that my approach is a bad idea. Here, our country can be classified into different provinces, and all cities/towns are under a specific province, and a barangay (the closest Layman's term is District, I guess). So if all places, where ever you may be in our country, you must have that certain barangay, city/town, and province. What I did is I used Foreign Keys to refer to tables barangay, city/town, province. Is this a bad idea?

How different is it if I created a tblCustomer_Address to separate Country_ID: Int FK, Province_ID: Int FK, CityTown_ID: Int FK, Baranggay_ID: Int FK from tblCustomer?

Thanks!

tblCustomer(
  Customer_Id: Int PK
  Customer_FName: String
  Customer_MName: String
  Customer_LName: String
  Country_ID: Int FK
  Province_ID: Int FK
  CityTown_ID: Int FK
  Baranggay_ID: Int FK
  Additional_Address_Details: String
 )
tblCountry(
  Country_Id: Int PK
  Country_Name: String
)
tblProvince(
  Province_Id: Int PK
  Province_Name: String
)
tblCityTown(
  CityTown_Id: Int PK
  CityTown_Name: String
)
tblBarangay(
  Barangay_Id: Int PK
  Barangay_Name: String
)

* EDIT: By the way, I forgot to mention. A part of my project is report generation, so I what I had in mind is to keep track of the locations. So I thought of having separate tables for barangays, city/town, provinces, to make each and everyone unique.

Answer 1

Pretty bad, as you've got to maintain the available options for country, city and districts.

Personally, I just create an addresses database table, which fields for address components as per the hCard microformat:

CREATE TABLE IF NOT EXISTS `addresses` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `extended_address` varchar(128) DEFAULT NULL,
  `street_address` varchar(128) NOT NULL,
  `locality` varchar(128) NOT NULL,
  `region` varchar(128) DEFAULT NULL,
  `postal_code` varchar(128) DEFAULT NULL,
  `country_name` varchar(128) NOT NULL,
  PRIMARY KEY (`id`)
)

Answer 2

Well it seems to me that a barangay will exist in a city or province, a city will exist in a province, and a province will exist in a country. How about a structure where you havea location table with a location type of Barangay, City, Province, Rural or country and a parentID pointing to the parent location in the hierarchy. Then your customer has a location id pointing to anywhere in the hierarchy. New locations can be added as existing in a city, province (for rural) district or country. Table would look like this:

tblLocation(
LocationID int PK,
ParentID int FK references tblLocation LocationID,
LocationType int FK references tbllocationTypes,
LocationName
)

Couldn't add this as a comment, so here is a more full implementation:

CREATE TABLE LocationType
(
    LocationTypeID int not null primary key,
    LocationTypeName varchar(20) not null unique
)
GO
CREATE TABLE Location (
    LocationID int not null primary key,
    ParentId int null references Location (LocationID),
    LocationName varchar(100),
    LocationTypeID int not null references LocationType (LocationTypeID)    
)
GO
CREATE Table Customer (
    CustomerID int not null primary key,
    FirstName varchar(50),
    MiddleName varchar(50),
    LastName varchar(50),
    LocationID int references Location (LocationID)
)
GO
CREATE TABLE City(  
    CityID int not null primary key references Location (LocationID),
    PostCode varchar(20) not null
)
GO
CREATE VIEW DetailedLocation AS 
    SELECT L.*, C.PostCode FROM Location AS L
    LEFT OUTER JOIN City AS C
    ON C.CityID = L.LocationID

Answer 3

The problem is that you are not handling addresses the right way. If you REALLY do not need the full address (or am I just missing this here?) than just having a Baranggay field in the customer table would be "okay". In any other case you should have an address table and just reference the addressID within the customer table. Unless of course a customer should be able to have multiple addresses, in which case you should introduce a CustomerAddressRel table for this m:n relationship.

Anyways, the whole splitting up of the address into multiple fields and/or tables here is pointless. A specific baranngay will forever belong only to one town, that town to one province, and that province to one country (country? if this is about multiple countries you need a different format anyways as baranngay is not an international concept!). So it really is good enough to track the baranggay in the address and have a different table which saves what town and province a baranngay belongs to.

Sorry for the long text but I feel it won't help you at all if I put up a revised scheme. You need to understand the reasons for certain decisions and then make the best decision based on your current dataset and the expected reach. If you ever possibly could go international, make sure the data scheme is ready for it right now.

Edit:

Okay then, I think it is best after all if I put it down. If you want to be future-proof and flexible, at least as long as you keep limite to th Philippins and a Barangay belongs to a town:

tblCustomer(
    Customer_ID: Int PK
    Customer_FName: String
    Customer_MName: String
    Customer_LName: String
)

tblCustomerAddressRel(
    Customer_ID: Int FK
    Address_ID: Int Fk
    Type: (Mailing, Billing, Historic,...)
)

tblAddress(
    Address_ID: Int PK
    Baranggay_ID: Int FK
    Additional_Address_Details: String (<< this looks like a bad idea btw)
)

tblCountry(
    Country_ID: Int PK
    Country_Name: String
)

tblProvince(
    Province_Id: Int PK
    Province_Name: String
    County_ID: Int FK
)
tblCityTown(
    CityTown_ID: Int PK
    CityTown_Name: String
    Province_ID: Int FK
)
tblBarangay(
    Barangay_ID: Int PK
    Barangay_Name: String
    CityTown_ID: Int FK
)