| design country,province,city, address tables in database [message #668470] |
Mon, 26 February 2018 23:05  |
 |
fabi88
Messages: 112
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 #668476 is a reply to message #668475] |
Tue, 27 February 2018 02:14  |
|
fabi88
Messages: 112
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 |
John Watson
Messages: 8960
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 |
|
fabi88
Messages: 112
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
