Table Structure for Customers Billing, Company & Delivery Addresses

[BrianCurran]

Hi all,
I've tried looking for a suitable Data Model but I can't find one to fit what I'm aiming to achieve.

I have "Residential" and "Commercial" customers. The Residential customers are nice and easy as they usually have a single address for Billing, Company & Delivery purposes.

However, some of our Commercial customers have several addresses so I would like to record them in a 3-Level format.

"Group" (Usually the Billing address: E.g. Government)
"Company" (Contract address: E.g. County Council)
"Site" (Delivery address: E.g. Anytown Library)

What would be the best way to organise the tables? Ideally, I would like to have a form whereby I can look at an address and tick 1, 2 or all 3 boxes to associate that address to its type. Would the following work?

tblCustomers
CustomerID (PK)
CustomerTypeID (FK)

tblCustomerTypes
CustomerTypeID (PK)

tblAddresses
AddressID (PK)
AddressType (FK)

tblAddressTypes
AddressTypeID (PK)

Any help would be greatly appreciated, as I'm going to attempt rebuilding a 15 year old Access db but with the correct structure and naming conventions etc.

Thanks
Brian.

 

[David Eagar]

Would it not work if you had

tblAddresses
AddressID (PK)
Billing (y/n)
Shipping (y/n)
Company (y/n)

That way, you only need to enter the address once and where the appropriate box is 'true', the correct paperwork can be generated

 

[BrianCurran]

Hi David,
Your suggestion would probably work fine if a customer was only 1 of the 3 options listed. I don't think it would work in my situation, as I would like to mark an address as inactive if it ended but I wouldn't be able to do this if there were other sites and/or services active for that customer.

So, if I had a typical customer it would look like this:

1
Bloggs Group, London (Group (billing address))

2
Bloggs Ltd, North (Company 1 under Group)
Bloggs Inc, South (Company 2 under Group)

3
Bloggs Kitchens, Manchester (Site 1 for Company 1 under Group)
Bloggs, Bedrooms, Newcaslte (Site 2 for Company 1 under Group)
Bloggs Bathrooms, Essex (Site 1 for Company 2 under Group)

Each site has to have an address listed as that is where we will perform the work.
Each company has to have an address listed as we may bundle sites together.
Each group has to have an address too for billing and grouping purposes.

I think I might have to enter each address as a record in tblAddresses but only allow 1 of the checkboxes to be ticked at any one time. So, if I have a residential customer it will be listed 3 times with a different check box for each entry?

This is just wrong though! :-/

 

[David Eagar]

My thoughts were for a Residential customer, you would only one entry (in most occaisions)

eg Napolean Blownapart - 24 Rue d'Awakening Paris: Company, Billing & Shipping all ticked, so no matter what paperwork was required, criteria would be met.

True, it does get more complex when Groups are involved, so table structure would be better if the was a tblGroup that was a 1 to many with tblCustomers. Company address would go in tblGroup and site Shipping and Billing would go in tblCustomers

Residential would be one of the Groups and there would need to be a bit of jiggery pokery to handle Company address

 

[David Eagar]

I remember the ERP system my company used, this was a complex area, we had about 8 different addresses that could be entered to hadle the multiple requirements, but it looked like some double handling to me...

I was thinking along the lines of (attached)

 

[BrianCurran]

Hi David,
Is that an MS Access file? I'm on Office 2003 so can't open that file...

Thanks
Brian.

 

[David Eagar]

Ah, I'm on Office2007
Try this one

 

[BrianCurran]

That's great, I'll have a look at it now...

 

[BrianCurran]

Ok, I've finally worked out what my model is...

A "Client" can have many "Customers"
A "Customer" can have many "Sites"
A "Site" can have many "Services"

Also:
A "Service" can be used by many "Sites"
A "Site" can belong to many "Companies" (rare, although it can happen)

Does the attached image (.jpg) match the above? And, can anyone see any problems with it?

Thanks
Brian.

 

[jdraw]

Brian,

I think your new model is heading in the right direction.
However, I do not see clearly how your model supports some of your original issues.

I have "Residential" and "Commercial" customers. The Residential customers are nice and easy as they usually have a single address for Billing, Company & Delivery purposes.

However, some of our Commercial customers have several addresses so I would like to record them in a 3-Level format.

"Group" (Usually the Billing address: E.g. Government)
"Company" (Contract address: E.g. County Council)
"Site" (Delivery address: E.g. Anytown Library)

How do you resolve Residential vs Commercial?

Have you considered AddressTypes? (Physical Location, Mailing, Billing, Shipping...)

Is there a difference between Customer and Client?

To test the model, you can create some sample data (test conditions).

Then play - what we used to call - "Stump the model". This term arose in order that the humans were exercising/testing the model, and not putting the modeler or database personnel against each other.

Run your test conditions and see if the model supports the data.
If so , Great.
If not, make sure you clearly understand "your business" - either the data is wrong, OR the model is wrong. Adjust/refine the model if that is what's wrong. Then run your tests again.

 

[BrianCurran]

Hi jdraw, thanks for your thoughts.

I thought about including a tblClientType later with two entries 'Commercial' and 'Residential'. Then use a Lookup field in the "Clients" table to identify which type applies to each Client?

My initial idea was to use a tblAddressType but I couldn't think it through clearly if I'm being honest. I suppose validation rules might have prevented any errors in data entry.

No difference between Customer and Client. I believe the dictionary definition is that a customer buys now and again but a client is somebody who buys repeatedly, e.g. in a contract for monthly services.

The final db is far beyond my current ability but I would like it to do the following at some stage in the future:

From a dashboard, click the 'New Site' button.
A form opens to enter the details in and I tick whether this site is also the 'Company' and the 'Client'. If it is then the data is duplicated.
If the 'Company' option is left unchecked, then a 'New Company' form pops up, followed by a 'New Client' form. Is this even possible?

Thanks
Brian.

 

[jdraw]

I don't think you need a Client and Customer Table. If the difference in definition is how often they buy, then 1 table will do. The issue then is that some fields may be required for the Commercial client, that are not mandatory for Residential.

Did you sort out the Groups?

 

[David Eagar]

No difference between Customer and Client. I believe the dictionary definition is that a customer buys now and again but a client is somebody who buys repeatedly, e.g. in a contract for monthly services.

If I might buy back into the debate...The above is correct! whether your client buys 3 times a day to the value of £000,000,000 or your customer buys once every three years to the value of £1.50, the DATA you need to process the order is the same.

You are building a DATA BASE - it belongs in 1 table

 

[BrianCurran]

I agree completely, a Customer/Client is the same thing when designing database tables. In my situation, I still believe I need the following hierarchy:

1st Level - "Client"
This client/customer address is the top level of the tree, a large 'group' business or maybe a business that is sub-contracting some work to us.

2nd Level - "Company"
This will be a subsidiary business of the group. The Client could be a large PLC business that owns several businesses, all independent of each other. This would be one of those

3rd Level - "Site"
This is the delivery address, where the actual work is carried out.

What I'm not sure about is whether I should have 3 separate tables as above or maybe store all of the addresses in one table and use an Address Type to identify which address is which.

Any one of the above could be the billing address and/or the mailing address.

Thanks
Brian.

 

[jdraw]

Your thoughts on hierarchy can be tested against your model. I think it's time for some working definitions and some sample data.

Here's a link for an approach that you may wish to consider.
http://www.databaseanswers.org/approach2db_design.htm

If you define the "things" that you will be dealing with, you get to clarify those "uncertainties" and refine your model or possibly some processes. Such definitions also serve as 'documentation and reference materials' during development and throughout the life of the application.

You may also want to put the purpose of your database/application into a 2-3 line paragraph to help focus your efforts.

Also, put some sample data together and test the model.

It will help when you talk to users, and it will help if/when you ask more specific questions on this or other forums.

 

[David Eagar]

What I'm not sure about is whether I should have 3 separate tables as above or maybe store all of the addresses in one table and use an Address Type to identify which address is which.

Any one of the above could be the billing address and/or the mailing address.

Don't go there, our ERP system operated this way and it was nightmare to manage.

"I still believe I need the following hierarchy" - Well in a global market, you will probably need to add Countries as your 1st level.. and if you REALLY want to to have fun, manage exchange rates !!!!

 

[BrianCurran]

Hi David,
Which way did the ERP system operate, separate tables or the address type method?

 

[David Eagar]

Seperate table for addresses - it was very difficult to keep track of it, but if you have more than 1 address, i guess there is no other option but seperate tables.

My biggest problem was when we had to migrate in new company data, it was very easy to get the Customer & addresses out of sync