Solved Physical address & Mailing address (1 Viewer)

[Kyp]

Morning all!
Quick dumb question....

When creating a contact table, and the contact has a physical address that differs from the mailing address, how is that usually handled?
Are both address' in the same table or, are the address separated some king of way?

Cheers!
-Kyp

 

[silentwolf]

Hi,
it all depands how often it is the case. But more flexible is with a seperate table with address.
In fact tblContact, tblAddresses, tblContactAdresses, a n:m relationship would be the most flexible way of handling different addresses.

 

[Kyp]

Gotcha!

Cheers!
-Kyp

 

[silentwolf]

Your welcome!

 

[Gasman]

I'd probably have them in one table with another field to identify the type of address, Shipping, Mailing, Accounts etc

 

[GPGeorge]

My two cents, to quote a pro.

I also PREFER to normalize data whenever possible. Therefore, I would also have three tables: "People", "Addresses" and "PeopleAddresses".
The junction table "PeopleAddresses" allows you to assign one or more addresses to each person, AND two or more people to one address.
As Gasman notes, you should also have a field in "PeopleAddresses" to identify the type of address. That calls for a fourth table, "AddressType"

That said, depending on the business purpose of the database, addresses and phone numbers tend to be an area where one can take a more relaxed attitude about the design, i.e. a lot of times address, email and phone information can be stored in the "People" table in a non-normalized fashion. It partly depends, in other words, on whether searching on addresses or phone numbers is going to be a primary function in the relational database application. Consider a retail organization which needs to locate customer information on the basis of a name, an email address, a phone number or address within a large (thousands of customer records) table. That's going to require more discipline and a normalized approach.

Your family contact list, with a few dozen relatives can tolerate some ambiguity.

 

[Pat Hartman]

One of the reasons for normalizing contact data is to eliminate duplication. So you have three contacts but they all have the same address. Do you store it once or three times? If you go the normalized route, you run into a different problem. Person2 moves location. Now you can't just change his address because that changes the address for the other people so maintenance becomes more complicated.

 

[GPGeorge]

" Now you can't just change his address because that changes the address for the other people so maintenance becomes more complicated."
Does that apply if you use a junction table of people and addresses? I realize that's even more complex in an interface, but it does eliminate that particular problem.

 

[spaLOGICng]

Feel free to add new or additional Columns to facilitate your needs. No need to add a new table. You could also add an Address Type ID Column to identify whether it is a mailing address or a billing address. You can then have filtered query sets one for each for Drop Down Boxes.

 

[GPGeorge]

Feel free to add new or additional Columns to facilitate your needs.

We are discussing an Access relational database application, not Excel.

 

[spaLOGICng]

We are discussing an Access relational database application, not Excel.

Neither am I. I mentioned what alterations could be done to the Table to facilitate the OP needs. I may be new here, but I have been developing in Access since 1991. Please do not underestimate my input. You, GPGeorge, and I are connected on LinkedIn. I am just using my Business Handler here.

 

[spaLOGICng]

Once again, depending on how complex your Access Application will become, there are a couple of approaches available to you. If it is a simple application, keep it simple, you could add several more Fields to your Table to facilitate the Physical Address.

If your Application is going to be a complex Application, where an Entity could have many Addresses, i.e. Physical, Mailing, Billing, Shipping, etc., then I would split the Address Table into a main Entity Table and a child Address Table, and in the Address Table have an ID to represent what Type of Address it is.

There are any number of ways to facilitate your needs, but I would keep it as simple as possible by adding new Fields to your existing Access Table to support your requirement. However, if you envision needing more Addresses available. Let's say a Business has a P.O. Box for a Mailing Address, has many Physical Addresses, has a Billing Department at a different Address, wants products shipped to a different Address, then I would use the second approach by splitting. I do want to point out that your do NOT need a separate Table for each Address Type. They can all be supported in a single Table and then use Queries to segregate them as needed.

 

[Pat Hartman]

" Now you can't just change his address because that changes the address for the other people so maintenance becomes more complicated."
Does that apply if you use a junction table of people and addresses? I realize that's even more complex in an interface, but it does eliminate that particular problem.

Using a junction table is correct if you go the normalized route. But when an employee moves, you need to know whether you can change the address he is linked to or if it is used by others so you have to add a new address and connect him with that OR connect him with a different existing address.

I've never used the shared address concept for contacts (except in my personal contact database) because it is too easy for the user to mess up the addresses if he doesn't do the necessary research.

I use an Address table for multiple addresses for a customer/contact frequently. But multiple contacts never share an address.

 

[GPGeorge]

Using a junction table is correct if you go the normalized route. But when an employee moves, you need to know whether you can change the address he is linked to or if it is used by others so you have to add a new address and connect him with that OR connect him with a different existing address.

I've never used the shared address concept for contacts (except in my personal contact database) because it is too easy for the user to mess up the addresses if he doesn't do the necessary research.

I use an Address table for multiple addresses for a customer/contact frequently. But multiple contacts never share an address.

Good points all. I would expect that a "new" address for a contact, though, would be a new record in the address table and that the previous address in the junction table would become "Inactive as of Date" for that reason. However, you are right that it does depend on a user not taking shortcuts.

 

[Pat Hartman]

But if the addresses are being shared, you first need to see if the "new" address is already being used. Say you have two contacts at address 1 and two at address 2 and one moves from address1 to address 2. Are you going to add a second instance of address 2? Because that defeats the entire purpose of the address sharing. This is why I don't normally do it. EVERYONE needs to understand and handle address changes
with care.

You can prevent addresses from being deleted but what do you do if the group moves? You have to determine that every contact must move or others will be incorrect. And in this case, do you inactivate the address or do you change it?

 

[GPGeorge]

But if the addresses are being shared, you first need to see if the "new" address is already being used. Say you have two contacts at address 1 and two at address 2 and one moves from address1 to address 2. Are you going to add a second instance of address 2? Because that defeats the entire purpose of the address sharing. This is why I don't normally do it. EVERYONE needs to understand and handle address changes
with care.

You can prevent addresses from being deleted but what do you do if the group moves? You have to determine that every contact must move or others will be incorrect. And in this case, do you inactivate the address or do you change it?

Pat, you've obviously thought through the ramifications more than I had. All those people moving around makes it more complex.

But with regard to the deactivation. The address itself never becomes inactive, only people AT that address in the junction table who no longer use it. That record in the junction table becomes inactive for that person. Instead of one address record for a person, in other words, the junction table has two, one marked inactive and one inactive. And if they move again, there are three records in the junction table, two inactive, one active.

That allows you to do things like look back at prior shipments and see that they used to go to one warehouse, but now go to a new one. I don't know if that history matters in all situations, but it is available.

Still, your other workflow issue is enough to convince me that simpler is better in most cases.

 

[Pat Hartman]

George, I understand what you were saying about deactivate being in the junction table.

I thought about the ramifications because I was going to use the technique some years ago but ultimately talked myself out of it because it was way too easy for the users to make a mess of the addresses and it didn't save enough storage. So, the technically "correct" solution was just too dangerous.