Results 1 to 2 of 2
Thread: Design question Access 97 (97)
2001-09-29, 19:15 #1
- Join Date
- May 2003
- London, Gtr London, England
- Thanked 0 Times in 0 Posts
Design question Access 97 (97)
I have completed a database with the help of everyone here, thank you.
I have a question regarding design; I am asking it for future reference.
Here is what I did/designed:
We had 2 addresses because people went to Florida in the winter and we wanted to send their mail there. The design had - Address1, City1, State1, Zip1,ReceiveMail1; and Address2, City2, State2, Zip2, ReceiveMail2. I coded so that if ReceiveMail1 was set to "YES" then ReceiveMail2 would be automatically set to "NO"(and vice versa). Now when I ran labels I had to run 2 queries to get the labels for Address1(ReceiveMail1 = YES) and then do the same for Address2. Then I created a Union Query. Everything worked fine.
Here is my question regarding future design technique.
I would still need 2 addresses. Address1 and Address2(including the city, state, and zip) I would still need the ReciveMail1 and Receive Mail2. But, could/should I now create a 3rd grouping called SendMailAddress, SendMailCity, SendMailState, SendMailZip. AND then if ReceiveMail1 was a "YES" I would automatically fill in these new fields with the appropriate data. If ReceiveMail2 was a "YES", then I would automaticall fill in the fields with the appropriate data.
This would cause a DUPLICATION of data, but eliminate the need for 2 queries.
Just asking for future consideration. I know we should try and avoid duplicating data.
2001-09-30, 02:13 #2
- Join Date
- Dec 2000
- Sacramento, California, USA
- Thanked 1 Time in 1 Post
Re: Design question Access 97 (97)
Don't create a third set of fields. Your design is already denormalized and believe me, you'll have enough trouble keeping that design up to date as it is.
The fully relational way to design this is to have an address table with the necessary fields to hold a single address in a record, along with a foreign key to join it to the record for the person. You could also have a field that identified the type of address, i.e., primary or secondary or whatever made sense. You could have a Yes/No field for "Current" address in that table as well, or date fields to indicate the date period during which the address should be used. This design would allow you to have as many addresses as you needed for an individual, not just two, and you wouldn't need to have a bunch of queries because you would just query for the current address and join those current addresses to the people they belonged to.Charlotte