tblAddresses OR address fields in tblCustomers

[123seven]

HI - newly registered with the first of many questions - thanks in advance for your time. I'm working on a new db for an existing business with currently 1500 customers (all client detail available in quickbooks) and 700 new sign ups, introduced at consumer shows, currently stored in the first draft of db.



The business supplies garden soil products directly to homeowners but also to designers & landscapers. For the most part - say 90% - the deal is 1 customer - maybe many orders, but usually same address (the odd "deliver to my mom's house" etc, occurs) with the exception of the biz clients whose orders will routinly require a different ship address (but origianl customer address for billing).

My issue is should i keep the customer address info (currently in tblCustomers) where it is and use a yes/no field in frmOrders, to indicate where an alternate ship address is required (stored in say tblAlternateShipAddress), or should i do like northwinds and include ship address fields directly in tblOrders (that usu is just copying customer address info over from 'customer', on the orders form) - OR... one table for all addresses.... (shying from this approach ...and northwinds!!) Any solid direction will be seriously appreciated .. Cheers

[jzwp11]

Welcome to the forum.

If a customer has many addresses (of whatever type), you have a one-to-many relationship which requires 2 tables. Now if you want to further identify the type of address, a billing address, a shipping address or whatever, you can add a field that distinguishes the type

tblCustomers
-pkCustomerID primary key, autonumber
-txtCustomerName


tblCustomerAddress
-pkCustomerAddressID primary key, autonumber
-fkCustomerID foreign key to tblCustomers
-txtAddress
-txtCity
-fkAddressTypeID foreign key to tblAddressTypes

tblAddressTypes (would include Billing, Shipping etc.)
-pkAddressTypeID primary key, autonumber
-txtAddressType

[123seven]

mmmmm - no shortcuts for me today ... thank you jz ..just needed to be told!

[jzwp11]

You're welcome