Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
madlo
Starting Member
41 Posts |
 Posted - 2011-05-04 : 11:01:32
|
| What is the best practice design for address postal code storage for MISpurposes?I have 10 client databases e.g. each has a Person table but some client dbs have extra tables and are slightly different (extra columns).Now in the person table in all databases it is linked to a home and postal address details each with postal code. Now the clients have the requirement to capture for person table a list of cities, municipalities to all addresses as well so they can use those to filter for reporting purposes.So instead of letting the user capture postal code, City, Municipality I want them to only capture postal code and in the frontend it will autopopulate city and municipality on screen so there will be no need for a seperate column to store it per person address. Obviously this will rely on me always updating the postal codes for my country in databases i.e. 10000+ codes that will be linked to city and municipality tables.Now my question is.1. must I go with this strict rules of the user not capturing city & municpality but autopopulating from postal code table links and me managing it in the database?2. There will be an extra Query overhead for my reports to link to a a big postal code table which in turn links to cities and municipality table. E.g. If I need to show all users in Municaplity A I would have to link Municipality table to City Table and in turn link city table to to postal code table and then link to person address table which the links to person table.3. Is such a strict normalized nest practice and practical or should I copy a internet store such as amazon which fields such as postal code, city and province are not linked but just free text fields.4. If I do go ahead with storing postal codes details in a table, becuase I have 10 clients should I put this in one central table in a seperate database anduse linked database queries to join these queries to client databases (save space) or should I have this table in each database (seperation)? 5.Will a seperate db have any performance impact (seperate data files ie. hd head has to move further, extra db authentication etc)? 6.If I do go central db what what is the best practice from front end population. Is it via sql queries or is their another method I can use e.g. web service to populate the details on screen since that data does not belong to an individual client to change. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-04 : 11:13:20
|
| 1. No but you will find things a lot easier if you enforce a standard format.You have the problem with addresses that are not in your database though so you will always have to allow for that.2. You can restructure how you wish.3. Not free text but you can do what's best for you.4. Have a central source but you can enhance per system5. SHouldn't be an issueFor the UK I start off with the paf database (at whatever level of detail the client can afford) in a flattened structure.This theoretically has all postal addresses in the uk - but doesn't and clients usually don't update regularly.Link to this for addresses. If there is an address that doesn't exist then add it to my paf data but mark it as unverified and deal with it later.In this way I have satndard format addresses and a few which may be suspect but can be looked at manually.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Design Best practice-Postal Code in Adresses