Home » Developer & Programmer » Designer » design country,province,city, address tables in database
design country,province,city, address tables in database [message #668470] Mon, 26 February 2018 23:05 Go to next message
fabi88
Messages: 111
Registered: November 2011
Senior Member
Hi,

What is the best data model design for saving Addresses in a database, one design is as follows:

1- Table Countries
2- Table Provinces (fk to countries)
3- Table cities (fk to Provinces)
4- Table Addresses (fk to cities), Main street column
5- Table Costumer
6- Table CostumerAddresses (fk to Costumer and fk to Addresses )

But the problem of this design is that addresses of some costumers have not city and just country is specified, or for one customer city is specified but province and country is not specified and ...

what is the best design for this issue?
Re: design country,province,city, address tables in database [message #668472 is a reply to message #668470] Mon, 26 February 2018 23:18 Go to previous messageGo to next message
BlackSwan
Messages: 26595
Registered: January 2009
Location: SoCal
Senior Member
fabi88 wrote on Mon, 26 February 2018 21:05
Hi,

What is the best data model design for saving Addresses in a database, one design is as follows:

1- Table Countries
2- Table Provinces (fk to countries)
3- Table cities (fk to Provinces)
4- Table Addresses (fk to cities), Main street column
5- Table Costumer
6- Table CostumerAddresses (fk to Costumer and fk to Addresses )

But the problem of this design is that addresses of some costumers have not city and just country is specified, or for one customer city is specified but province and country is not specified and ...

what is the best design for this issue?
Which metric measures best?
Quantify how you will decide which response best.
Re: design country,province,city, address tables in database [message #668475 is a reply to message #668470] Tue, 27 February 2018 01:30 Go to previous messageGo to next message
John Watson
Messages: 8012
Registered: January 2010
Location: Global Village
Senior Member
It is not unusual to denormalize addresses. There is an example in the sh.customers demonstration table.
Re: design country,province,city, address tables in database [message #668476 is a reply to message #668475] Tue, 27 February 2018 02:14 Go to previous messageGo to next message
fabi88
Messages: 111
Registered: November 2011
Senior Member
Thank you so much for your responses.

Another design came to my mind:

1- Location_Type Table (type_Id, type)

			 1 	Unknown
			 2 	Country
			 3 	Province
			 4 	City


2-Location_Info Table (location_id, type_id ,parent_id,name)
			   1		1	1     Unknown
			   2		2	2     Country1
			   3		2	3     Country2
			   4		3	2     Province1
			   5		3	3     Province2
			   6		4	4     City1
			   7		4	5     City2
			   8		4	1     City3

3- Addresses Table (Address_Id, Location_id, street ...)



What is your idea about this design?
Re: design country,province,city, address tables in database [message #668478 is a reply to message #668476] Tue, 27 February 2018 05:42 Go to previous messageGo to next message
John Watson
Messages: 8012
Registered: January 2010
Location: Global Village
Senior Member
Quote:
What is your idea about this design?
My idea is that you need to consider why you are doing this. Third normal form (or higher forms of normalization) may be fine in theory, but you need to consider what structures are suitable for your application. How are you going to get to certain rows? What join and filter predicates are used? For example, if you only ever get to a table with a primary key retrieved from another table, perhaps you should be denormalizing the two tables into a cluster. Or having just one table, not two.
Re: design country,province,city, address tables in database [message #668479 is a reply to message #668478] Tue, 27 February 2018 06:18 Go to previous message
fabi88
Messages: 111
Registered: November 2011
Senior Member
Quote:

My idea is that you need to consider why you are doing this. Third normal form (or higher forms of normalization) may be fine in theory, but you need to consider what structures are suitable for your application. How are you going to get to certain rows? What join and filter predicates are used? For example, if you only ever get to a table with a primary key retrieved from another table, perhaps you should be denormalizing the two tables into a cluster. Or having just one table, not two.

Thank you so much, You are right and I think that the key point is your sentence "you need to consider what structures are suitable for your application".Thank you again for resolving my ambiguity and not should worry about denormalizing.
Previous Topic: generate problem
Next Topic: position of windows and help menu items in mmb
Goto Forum:
  


Current Time: Mon Sep 23 06:01:15 CDT 2019