| Author |
Topic |
|
hasanali00
Posting Yak Master
207 Posts |
 Posted - 2004-12-20 : 07:12:43
|
| I want to design 2 tables, one to hold customer info, second to hold customer addresses. I can think of two ways:(1) Design Address table with Customer ID as foreign key(2) Design a 3rd table which holds Customer IDs and Address IDs.Which is the better approach?regards |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-20 : 07:26:55
|
| (2) is only needed if two customers can be based at the same address, but even then I think its a bit of an overkill.(1) is only useful [as distinct from storing the address in the customer table] if a customer will have multiple addresses - delivery address + invoice address, or to keep a history of previous addresses (but you could use an Archive Table for that instead ...).Kristen |
 |
|
|
hasanali00
Posting Yak Master
207 Posts |
Posted - 2004-12-20 : 07:35:14
|
| Hi,thanks for the response."Archive Table" is this a special database term. I am not sure what u mean here, could u explain please. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-20 : 07:41:29
|
| Nah, just another table to store "old" data - easiest way to do this is to have a DELETE TRIGGER which inserts the [old] data into the, errrmmmm, "Archive" table.Kristen |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-12-20 : 07:47:01
|
i'd go for (2). what if you have a whole building full of your clients, they all have the same address...wouldn't we all like to have a whole buildnig full of our clients  Go with the flow & have fun! Else fight the flow  |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-12-20 : 08:02:12
|
I always like (2), becuase they're are different types of addresses. You could have billing addresses, mailing addresses, shipping addresses... etc.It just leaves the flexibility in there, in case you need it Corey |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-12-20 : 08:57:11
|
| I think the key thing to do here is: if a customer will have more than 1 address, don't let the addresses have an identity or some random key that is meaningless (i.e., address #2). create a table of AddressTypes, and your address tables has a PK of CustomerID/AddressType. This forces each address to have a meaningful and consistent definition. (of course, make sure your AddressType values are meaningful, too! This is no good if your address types are "Address #1" and "Address #2" ...)- Jeff |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-12-20 : 10:45:40
|
I too would back the option (2).Customer-----<CustomerAddress>-----AddressCustId FK_CustId AddressId FK_AddressIdThis can be taken further to:Customer-----<CustomerAddress>-----Address>---------AddressTypeCustId FK_CustId AddressId AddrType FK_AddressId FK_AddrType And possible also:Customer-----<CustomerAddress>-----Address>---------AddressTypeCustId FK_CustId AddressId AddrType FK_AddressId FK_AddrType PrimaryIndWhere you can determine which is the primary (preferred) address. That can (if you really have the need) be extended to map PrimaryAddress for Delivery, versus PrimaryAddress for Post (statements) *##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-12-20 : 11:01:35
|
I would not have an Addresses table with some random AddressID PK -- I don't see the point. Why not put the actual address data in the CustomerAddresses table?With that schema, also there is no constraint on address types -- a cusomter can have two "billing" addresses. This doesn't make things too deterministic . which do you send invoices to?all you need is: Customers <-----CustomerAddresses ----> AddressTypes CustID CustID AddressTypeCustName AddressType Description.... Street etc City State ...etc... bold = primary key- Jeff |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-12-20 : 11:06:57
|
| I would also implement some sort of quality-control process on the data so that the addresses can be as accurate as possible.This can be done with a hierarchy based structure.ie pick USA as the top level address, and the software should automatically display at the next level the state, next the county, next township, next street, etc.Given that 'new addresses/streets' don't get invented each day of the week, this sort of solution would add a level of quality to the data that would lie-behind any design.A pre-built master list of address for most countries is available at usually affordable rates, and it should be convertable into a hierarchy relatively simply. Any new addresses, could be added by local administrator in an on-demand, but controlled manner.It's one of the issues that is a general weakness in information gathering application....we provide the correct data structures to record information, but take no steps/provide no tools to ensure "quality business-useful" information is actually input. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-20 : 12:46:01
|
| Sounds awfully primative Andrew! Just type in the PostCode and then provide a picklist of the dozen, or so, houses matching that PostCode ...Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-12-20 : 13:29:10
|
| I've used the postal code....I would also say to keep the "primary" address in the Customer table...and store all others in an address tableThe drawback is that you have to maintain business rule in access the data from the access method that would be a little more complicated than something like = 'Billing'Make sure you put a unique contraint on the street number, street and zip in the address table so they can't add duplicates...Brett8-) |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-12-21 : 02:35:24
|
Hi Jeff,Perhaps I'm taking it too far, but consider a situation where a customer has more than one physical location- for example a banking system homeloan where one customer may have many physical address (granted that there is a LOT more to the model that what we are looking at here). The point is that I might have 3 physical houses. Given your model, I would have to use "artificial" address types like "Primary house","First house","etc" (I'm sure there would be better names). You could alter your to have sequence number's, I guess, and connect the "primary billing" to a specific types, and non-billing addresses to a seperate type.Whereas with the model I proposed you would be able to cater for the various home addresses. you would need to extend it, to cater for the usage of the address, depending on requirements.Also, I think this model allows you to have Bob Own House1, Peter Rent House1, Pam PrimaryPostalAddress House1, Bob PrimaryPostalAddress House2, Bob BillingAddress POBox1Customer--------<CustomerAddress>-----Address>---------AddressTypeCustId FK_CustId AddressId AddrTypeCustomerDet... FK_AddressId FK_AddrType AddrTypeDesc FK_PrimaryUsageInd AddressDet... \/ | | PrimaryUsageType ---------------- PrimaryUsageInd PrimaryUsageDesc Of course, as always, there are many ways to define models, depending on actual requirements.Your thoughts?*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
hasanali00
Posting Yak Master
207 Posts |
Posted - 2004-12-21 : 06:21:01
|
| Thanks everyone, I found this discussion very useful.Just to further show off my ignorance: Why create a new table for AddressType... why not just include an additional 'AddressType' field in Address table that can tell me whether the address in invoice address or delivery address????regards |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-12-21 : 09:10:21
|
| Wanderer -- It all depends on your needs, I suppose. I personally like to force as much structure into my data as possible. one address of each type does a good job with that. if you need to handle multiple addresses because you are tracking someone's many summer homes or properties they own then you certainly have a different model than trackinga customer's shipping versus billing address.as for address types, these are your options as I see it:1) allow this to be freeform, no constraints (maybe the app controls/validates?)2) use a CHECK constraint on the table to only allow a list of values3) refernce a table of AddressTypesI prefer #3 personally, because I like storing extra info with my lookup values to make my code more self-documenting and flexible. makes it easy to store variations of display values for each address type; i.e., to show "BL" on 1 report or form with little space for the billing address, while on another form or report you can display "Billing Address". That's just my preference.- Jeff |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-12-21 : 09:18:38
|
| point taken....however 'postcodes' only work in area's where postcodes exist......which is not the case in Ireland (amongst others???).....here "Andrew Murphy, Dublin" can sometimes work as an address...in a city of 1.2m people!The principle I was suggesting also can be applied to other data freeform input data items...The number of times I see "A.Murphy, Andrew Murphy, Mr. A.Murphy, Murphy Andrew, Andew Murphy, Andrew Murrphy, etc" as unique customers appalls me....with the consequences of mis-identification of customers....some smart application of knowledge about the structure and acceptable spelling of typical 1st + 2nd names would cleanup 95% of freeform customer name issues.....i'm suggesting that similiar could apply to addresses (as outlined). |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-21 : 12:39:03
|
| "Why create a new table for AddressType"To put a foreign key on the value in the Address table, thus constraining it to valid values in the "lookup" tableKristen |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-12-22 : 05:35:51
|
| The reason for the AdressType table is as Kristen and Jeff stated. I like structures that try to ensure and enforce valid data. So often you see systems that "will enforce in the application", and then find that data has come via some other route (be it a user with too much authority, a piece of code that doesn't enforce a rule, etc. etc.), and you end up with data in the database that is "bad".Andrew - I agree - all too often I see systems that use "unique" business dat, only to find out that it isn't, or that as the business case grows, it is no longer unique. But let's NOT open the artificial key discussion - that was done in DEPTH somewhere here, and isn't pertinent to this requirement.Have a great festive season all ...CiaO*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-12-23 : 08:35:44
|
| I quite agree...."But let's NOT open the artificial key discussion ".....it's not the artificial key syndrome i'm talking about, but even a good design can have cr*p data!However....Happy Christmas to one and all. |
|
|
|
|
Customers... Address: the best way to design them