table structure (1 Viewer)

[linskill]

I need to modify my customer table to cater for multiple names at one address. I was hoping that someone could point me in the right direction on how best to do this.
Should I move all the addresses into their own separate table or leave them as is (name-Address-postcode- email etc in one table) and put the additional addressees into a separate table or is there a better way I have not thought of.
Thanks in advance for any insight.

 

[jzwp22]

If there are multiple people associated with an address than that describes a one-to-many relationship which by normalization rules requires the people to be put into a separate but related table. The next question, is can a person be associated with more than one address?

 

[linskill]

Thanks for the quick reply, the short answer is No a person cannot be associated with more than one address.

 

[jzwp22]

Since you have not provided your current table structure, this is how I would structure the tables

tblAddress
-pkAddressID primary key, autonumber
-txtAddress
-txtCity
-txtState
other fields pertinent to the address

tblPeople
-pkPeopleID primary key, autonumber
-fkAddressID foreign key to tblAddress
-txtFName
-txtLName
other field pertinent to the person

 

[MarkK]

In this case you still might be best off to maintain one person per address and duplicate the addresses as required. If you have many people under one address, and one person moves, and you edit that person's address, then you can inadvertently edit everyone's address. How will you solve that case? So in some cases to edit an address you'll need to add one? That might be confusing.

It doesn't always make sense to link to data even if that data is duplicated in some ways. An address, even if multiple people happen to share one, is still a discrete dimension of an individual, and should be subject to change for just that individual. Maybe you just need to add the capability to copy an address that already exists.

 

[linskill]

Thanks for your input, having given it much thought I think I have to agree with lagbolt's suggestion and leave them how there are and put up with the duplicate data. I think this one of those "if it aint broke dont fix it" moments.

 

[jzwp22]

..the short answer is No a person cannot be associated with more than one address.

Just to clarify, if a person can be associated with more than 1 address over time (but not necessarily simultaneously), then I would alter my earlier structure as follows:

tblAddress
-pkAddressID primary key, autonumber
-txtAddress
-txtCity
-txtState
other fields pertinent to the address

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
other field pertinent to the person

tblPeopleAddresses
-pkPeopleAddrID primary key, autonumber
-fkAddressID foreign key to tblAddresses
-fkPeopleID foreign key to tblPeople
-dteEndEffective (date when the person was no longer associated with the indicated address)

If the dteEndEffective field is null, you know the person is still associated with the indicated address. You can use this field to find out who is at any given address at any point in time. You can also use this to track people as they move around over time.

Thanks for your input, having given it much thought I think I have to agree with lagbolt's suggestion and leave them how there are and put up with the duplicate data. I think this one of those "if it aint broke dont fix it" moments.

Ultimately, the design is up to you and since you are more knowledgeable about your application you would know best, but if you run into problems, I think the structure I presented above would help.