Addresses

[HansBades]

I have a database I am setting up that will track multiple addresses. Home Address, Business Address, Account Address, Billing Address, Prior Account Address. I am thinking that the best way to track this is to have one field for address and then create another table with address types that include home, business, account, etc. as it's value and then link the proper type to the particular instance of an address. Does anyone have any thoughts if this is the best way to do this?

[alansidman]

No need for a separate table.

Create a separate field for the type of address

With this set up (normalized data), you can then query your table for the type(s) of addresses you wish to see.

[Micron]

There is something to be said for splitting such data up. If you have 3 address types, the main data would be repeated for each record, such as first and last name, initial and anything else pertinent to the person but not part of the address data. Maybe phone numbers too, in which case IMO the data is not properly normalized. To that add that each record has a different PK value yet all records pertain to the same person. I'd say lots of people wouldn't think twice about a separate table.

[Bob Fitz]

I would certainly use a table for the addresses

[orange]

I think something like the following is where I'd start. Other factors--how many accounts/account types could/do exist in your data? Again depending on volume and volatility you may want to identify AddrType in a separate table. This would allow you to add more AddrTypes to your database, if needed.

[ArviLaanemets]

I think OP has the case where same (or almost same) address is used for different address types. E.g. private address of enterpreneur and the address of his/her enterprise are same (except name part of address). When this is the case, such build-up is valid.

[CJ_London]

would be better if the OP provided some example data and how it links together since we are all guessing from the description what the actual requirement is.

Separate table for addresses? yes - a given. How you link it to the 'parent record' is guesswork at the moment. Could be you include the parent PK in the address table together with an address type field. Could be you need a separate many to many joining table to indicate type where same address could be applied to different types. Could be there are multiple accounts to the PK and you need to link instead to that.

[Micron]

Some might remember the columbarium thread here. I researched address table concepts for that and in an effort to make something work for anywhere in the world and the result is that there are not 2 but 5 tables to support address details. I agree with Ajax - 2 is the bare minimum but the number, as always, depends on the need.

[HansBades]

Thank you everyone for the thoughtful responses. Very helpful.