Table with Country, ShipCountry, BillToCountry linked to one Coutries table

[Dave Taylor]

I would like suggestions on how to "properly" set up the tables and relationships required to do the following.
We have a customers table that can contain a Country, ShipCountry, and BillToCountry. All 3 countries could in theory be different. I want all 3 to store the CountryID from the Country lookup. I am trying top avoid a circular reference or any data contention issues. What is the proper method to do this?
Thanks very much in advance!

 

[Simon_MT]

The issue here seems to be more than just the Country. There are could be three totally different addresses. Save the Customers table, I would suggest you look at your invoicing system. I would also consider looking at creating a Customers or Invoice Address table with the capacity to handle two or more records "D" Delivery to Address and "B" to address.

Simon

 

[Dave Taylor]

Thanks Simon,
It is correct that I have 3 distinct addresses as a whole within the one Customers table. I can deal with the selection of which address to use depending on which function I am using in the database.
The problem I am having is the 3 different countries (per record), or regions (per record) relating to the same Country table or Region table.

Customer A
______________
LocationCountry=Canada
ShipCountry=US
BillToCountry=Germany

All three of these Country fields lookup the CountryID from the one Country table and that is what I realize is wrong but need to know how to resolve the overall approach.

Are you suggesting that I create a CustomersShipTo table and a CustomersBillTo table over and above the basic Customer's table? Then, within each of those tables, lookup the countries from the same Countries table. The problem with that approach would be that we have similar scenarios for Suppliers and OEMs where they may have different locations we need to track and that could mean a lot of extra tables.

Is my only option to have a linked table(s) to deal with the 3 countries in one record being linked to 3 different countries in the countries table?

Thanks again.

 

[Simon_MT]

One way to deal with your problem is to create a combiboxes two fields:

CountryID, CountryName
The first column lenght = zero. Bound column is the CountryID which is invisible. Onlt the CountryName. There would be no need to reference the CountryTable in your Customers Form.

Better still is to have separate table for Addresses.

Simon

 

[GaryPanic]

you should start with billing addess
where am i getting paid from ..

so
name
add1
add2
add3
add4
postcode/zip
country * required field (from list)

now you need a shipping from which can either be the billing address or another - so you need a button to say if selected then the billing address and the shipping from address (or country are the same ) and the same with the shipped to .
your button could look at country * and populate the correct field


other than this you will need a couple of drop downs to have your shiped to /shiped from

I would captor (?) full address details of ship from and to