How to Incorporate New Tables for Existing Database

[PinkDuster]

I have built a single-user sales and inventory database for which the Customer Information table houses Billing Address and Shipping Address data (street, city, state, etc.). Because of the nature of the business, customers may use more than one Shipping Address over multiple orders. Additionally they may use more than one Payment Method over multiple orders.



I think the best way to implement is to create new tables for Shipping Addresses and Payment Methods but am stuck on how to link them to the Customer Information table. Don't want to create a Many-to-Many relationship anywhere, and would like to eventually have a drop-down box for a given customer to select Shipping Address (default would be either Billing Address or last address used), and for Payment Method (like PayPal, Credit Card, etc.). Additionally, in the Order Form I would need to update the new tables.

Does this make sense? And are there other intermediate tables I need to make this work?

[June7]

Each order can have only 1 shipping address and 1 payment type? Save the address ID and payment type ID in order record.

Address table will probably need field for CustomerID so you can limit choices for user selection.

[CJ_London]

Does this make sense?

yes. With regards linking just include a foreign key in each address record

tblCustomers
CustomerPK
CustomerName
…
….

tblAddresses
AddressPK
CustomerFK
AddrLine1
AddrLine2
…
…

tblOrders
OrderPK
CustomerFK
ShipAddressFK
OrderDate

With regards billing address, if there is only one per customer you can either leave in the customer table or include in the address table, but would require an additional flag to indicate this is a billing address