Table/Form design; Record/user has Multiple addresses, selected the address that is the contact

views:

answers:

Table/Form design; Record/user has Multiple addresses, selected the address that is the contact

Hi there, Looking for advice as to the correct method of solving this design issue

I have a table for a profile / record (tblProfile), each profile can have more than one address / property (tblProp)

I have a form for adding a profile, and a seperate form to add an address.

What would be the correct method for allowing the selection of one an address as a contact address? Would it be advisable to use a third table to store the user ID and contact address ID, or use a field in tblProp. What type of form design would be needed?

I imagine this is pretty basic database design / theory, but im having a bit of difficulty figuring this out.

Thanks in advance for any help any one may be able to provide

Cheers Noel

+1 A:

Assuming User_ID is the primary key for tblProfile and Address_ID is the primary key for tblProp, create a junction table which stores each unique pair of User_ID and Address_ID.

You can build a form based on tblProfile with a subform based on the junction table to assign addresses for each user.

HansUp 2010-09-13 15:09:28

Cheers HansUp. I cant quite get my head around the subform. I would need to show all addresses assigned to the user and an option/radio button to select one as the contact address, would that be correct?

glinch 2010-09-13 15:12:23

I didn't consider making a distinction between the addresses associated with each user. You could add a field as Remou suggests to designate one of the user's addresses as the contact address. The subform would be based on the junction table, with the link master/child field set to User_ID. To select an address in the subform, add a combo box to the subform which uses tblProp as its rowsource.

HansUp 2010-09-13 15:27:52

@HansUp is a junction table really necessary? Won't the address table itself hold the person id?

Remou 2010-09-13 15:40:38

@Remou Consider whether two or more users can have the same address. If that can never happen, he can store the User_ID in tblProp. He told us a user can have more than one address, but not whether the same address can be associated with more than one user.

HansUp 2010-09-13 15:57:26

I have considered it and it gets very difficult quite quickly, unfortunately, unless addresses cannot be edited once they are created, because otherwise the user edits an address in a way that suits person #1, but not person #2. It was the bane of my life at one stage.

Remou 2010-09-13 16:16:29

Sounds like you're suggesting he store separate copies of the same address for multiple users. OK, if he needs to. The whole concept of "contact info" can get surprisingly complicated. The one which bit me was when my boss' boss demanded all contact go through her assistant. Oops!

HansUp 2010-09-13 16:39:53

@HansUp: you are certainly right that a "simple mailing list" can be extremely complicated if you actually try to really model reality. The fact is that hardly anybody does that. I would suggest that shared addresses are almost always derived from some other attribute, such as the employer, so I have never actually implemented my addresses table to share an address between more than one person.

David-W-Fenton 2010-09-13 21:03:38

+1 A:

Having gone through this, I think that an address status field is the best bet. This will allow you to choose whether the address is the main address, a former address, etc. In fact, depending on your set-up, it can be useful to have two classification fields, the one just described and address type, which would be home, work etc.

Another point - I have found it useful in some circumstances to write the key for the main address to the persons table.

Remou 2010-09-13 15:17:10

Cheers Remou. What would be the correct way, using your method, to ensure that only one address can be selected as a contact address? So when changing an address status to contact, the previous address used as contact details would have its status changed automatically?

glinch 2010-09-13 15:41:27

If each person can only have one contact address, store it in the main table. If the address key matches the main table key, then it is a contact address.

Remou 2010-09-13 15:59:19

@Remou By main table you mean the table with the users details, tblProfile?

glinch 2010-09-13 16:29:08

Yes. If a field (column) can occur only once, tblProfile is a good place to keep it.

Remou 2010-09-13 16:34:02

ansaurus

tags:

views:

answers:

Table/Form design; Record/user has Multiple addresses, selected the address that is the contact

related questions