I need guidance on creating branch address database

[thepreacher]

hello all this is my first post. I am working on a site where i need to allow customers to choose where they will like to receive a service. They need to select the Country, then the Region then the address. The relationship between the three is : One country can have Many Regions then One Region can have Many Addresses.

My solution is to design 2 tables. But since the number of regions a country can have is not the same for all countries i am stuck with how to design such a database. Hope i make sense.

Thnks

[rdivilbiss]

It doesn't matter how many regions a county has.

Countries table
Country_Code -> primary key
Country_Name
etc.

Regions Table
Region_Code -> primary key
Country_Code -> foreign key to Countries table
Region_Name
etc.

Address table
Address_Code -> primary key
Region_Code -> foreign key to regions table
Address
etc.

If you wanted, you could add the Country_Code to the address table as well, but it isn't necessary.

That's three tables, not two.  You could come up with some convoluted way to combine the countries and regions tables, but I think it will make overall maintenance more complicated.

Try to enumerate all the data access you'll need and abstract those.

You should have something like this:

MySQL
db abstraction layer (support for security and parameterized queries)
data access routines  <- the access functions you enumerated
business logic (consumer of the data access routines with input/output filtering-security)
presentation layer (forms to pass users choices to the business logic.)

Regards,
Rod

[VGR]

yes. Your problem is the perfect example of the usefulness of relational database systems ;-) (RDBMS)

In fact, denormalizing the conceptual analysis above leads you to implement the relationship "belongs to" (region in country and address in region) in two ways :
- either you use foreign keys, ie "automatic" indices held by the RDBMS
- either you implement "relationship tables" on which you've full control.

the second solutions would mean you need to write the countr, regions and addresses as :

countries
id or code - primary key
rest of country's attributes (name, comment, whatever)
creationDT DATETIME
modificationDT datetime (those are nice to have)
last_modif_user_id (even better)
creator_user_id

regions
id or code - primary key
rest of region's attributes (name, comment, whatever)
idem as above for "control data"

+ relationship table country_regions :
id integer unsigned unique auto_increment (or PRIMARY KEY, it's the same)
id[or code]_country integer
id[or code]_region integer

you will thus have only ONE entry in regions for each region, ONE entry in countries for each country, and MULTIPLE (and fast & small ;-) entries in country_regions representing each occurence of a region in a country

afterwards, getting the regions of a country (let(s say "id1") is simply writing this :
select a.* from regions AS a LEFT JOIN country_regions AS b ON (b.id_country=a.id_region) WHERE b.id_country=id1;