db design question

[elektra12]

i'm somewhat new at access and have been using a particular db for awhile. the basic design is three tables:

tblCustomers (includes contact info, etc)

tblTransactions (links Customers to info on homes bought or sold by customer...a one-to-many relationship)

tblAddresses (links the transactions to this table of addresses...so that if a person buys or sells the same property, the property isn't duplicated but merely linked to a new transaction...therefore another one-to-many relationship)

...this has all been working well for some time until a problem arose. I also use the TblAddresses for customer contact information...i have a query to find a customer's current address. the NEW problem is that I have TWO customers who live at the SAME address, but only ONE customer actually was involved in the TRANSACTION of buying the house. So I need to find a way to link Customer #2 to this same address. At first I was thinking of making a simple table to "link" between tblCustomers and tblAddresses. this link table would include only the CustomerID and the AddressID (since duplicate AddressIDs would be possible). This doesn't seem like a good way, though. IT seems if I relate tblCustomers to TblAddress, then I will end up with some kind of infinite loop. Any ideas of how I can solve this problem?

 

[Steve101]

Why not just add an extra AddressId field to the Customer table, which points to the Customer's current address details. If two customers happen to have the same address, this will not be a problem.

The above method is quick, simple and effective. It will not however support retaining a "history" of a customer's addresses. To do this you would have to add another table (call it tblCustomerAddress say). Whether you need this added complexity is dependent on your business requirements; in any event though, you will not run into a problem of an "infinite loop". This problem occurs when you are writing procedural computer programs, not with SQL, although you can get back more rows than you want if your SQL query criteria or table JOIN criteria are not comprehensive enough.

Hope this helps,
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)