I currently have a stores table that contains two full addresses, mailing and physical. I'm redoing the table and thought maybe I'd put the states in their own table as well as the countries. In doing so I noticed I need two state foreign keys in the stores table, one for each address. So my design is flawed because I won't be able to distinguish which state I need, the phyiscal address or mailing address.

I thought maybe two more tables, one for physical addresses and one for mailing addresses but this presents the same problem. If I need to show both addresses I won't be able to specify which state I need.

So all I can think of is two state tables which seems stupid or to just keep the addresses in the store table and get rid of the states table. I would probably still have a states table for combo boxes but it wouldn't be related to any other tables. I don't want to do this either.

Could something be done with aliases?

Does anyone have any suggestions?

On Error Goto Hell

Hell:
    Msgbox("An error occurred, but was sent to Hell. You may continue.")

one address table, and the stores table will have two FKs to it

one state table, and the address table will have a FK to it

countries table if necessary, and the states table will have a FK to it

simple, yes?

How would I distinguish a physical address from a mailing? Would I need a flag in the address table? Envision a form that shows both addresses.

On Error Goto Hell

Hell:
    Msgbox("An error occurred, but was sent to Hell. You may continue.")

yes, that's how i would do it

then a store could have one address, or the other address, or two addresses, or even several physical addresses and a mailing address...