Cant get my head around a simple database! Lookup Customer Lookup

[James]

I have studied Northwind, but just cant get me head around what I am
attempting to do! Can anyone help with the structure and basic setup.

I want to create a database that can track widgets, each widget has a serial
number, and a customer can have more than one widget per account.

I have a table that lists the billing address (CustomerID)

AccountNo:
Name:
Address:

and a table that records widget (WidgetID)

WidgetSerial
AccountNo (Links to customer details in CustomerID table)
Date of Install.
WidgetInstallLocationAddress

What I need to do, is create a form where I can enter the Account Number,
and the customers details (Name & Address fields) are prefilled with the
data from CustomerID table (this is where I struggle). However the install
address of the widget, may not be the same as the billing address, and
therefore the opportunity to insert a installlocationaddress is required.

How can I acheive this?

 

[Tom Ellison]

Dear James:

Starting from the ground up.

"a customer can have more than one widget per account"

Now, is a "customer" the same thing as an "account". In some situations, a
customer could have several accounts. You may need separate, related tables
for this.

"I have a table that lists the billing address (CustomerID)"

If the table has an AccountNo (as shown) why have a CustomerID as well? Why
not drop the ID and make AccountNo the PK?

"a table that records widget (WidgetID)"

Same thing. There is a WidgetSerial. Why have an ID as well? The
WidgetSerial is unique, right?

Maybe I have the wrong idea. Is CustomerID a column, or is it the name of
the table. In reading of other people's databases, CustomerID has always
meant a column, not the name of a table. Do I have the wrong idea?

In some cases, the "ship to" address, not unlike the
WidgetInstallLocationAddress, is an attribute of the Account or Customer,
not the widget. If Joe orders a widget today and another tomorrow, will
they be for the same location? Maybe, probably, certainly? Will Joe have a
short list of locations at which he installs widgets, and which are
frequently reused? Just some possibilities for usefull exploitation in
modeling the data.

Now about building the form. On the main form, or a separate subform
according to your preference, select an existing customer or add a new one.
How do you envision finding an existing customer? The best answer is this:
What do you know when you need to find the customer? Is it a person on the
phone? If it is an existing customer, what does he say: "This is customer
14072." I doubt it. He says, "This is Joe Wilson", right? So you need a
Joe Wilson type of combo box lookup. So, you type in "Joe Wi" in the combo
box, and it auto fills to say Joe Wilson. You open the combo box list, but
there are 7 Joe Wilsons there. How do you identify one from another? Well,
there are a lot of possibilities.

Here's my suggestion. Let Joe decide how to make himself unique. Why?
Because, when you're on the phone with Joe, the thing that makes him unique
in your computer needs to be something he can remember. Maybe it's the fact
that he's the Joe Wilson from Tampa. Maybe it's that he's Joe W Wilson.
Let him be the one who decides what his distinguishing characteristic is.
After all, he's the one on the phone who has to remember what that
characteristic is. When he chooses that "characteristic" at first, it will
be entered in the computer and tested for uniqueness. If you don't already
have a Joe Wilson with the characteristic "Tampa" then he gets that
assignment. If you do, have him choose another.

Contrast this with the "account number" alternative. Do you think he's
going to remember his account number? If he calls in an order from his job
site and doesn't have his filing cabinet with him, he won't have the account
number with him. Now what are you going to do? Refuse to take his order?

Here's another aspect. If you go into your boss's office to discuss the Joe
Wilson account, how do you tell the boss what account you're talking about?
Do you say, "I have a question about account 94131." Is that how your boss
knows the customers? Or does he know this as the Joe Wilson Tampa account?

This is what I call a unique human key. It's like a "natural" key
(something we discuss in database technology) but it goes just a bit
farther. The account number is technically a natural key, but not the one
humans use. "Tampa" is the kind humans use. We have the need for
uniqueness, too. We cannot talk about the Joe Wilson account accurately
without saying "Tampa" and we know that, and why that is so.

All this discussion is to suggest you add a column to the customer table for
HumanKey and make the CustomerName / HumanKey combination a unique index.

There have been many debates on this. The above is my take.

Well, I'm nothing if not long winded. Don't know if you like the
discussion, if it helps move you toward the product you'd like to have, or
what. I'll stop for now. Need you encouragement if I'm to continue.

Sorry if it seems complex. In my opinion, a database should be only as
complex as the thing it is modeling. No more, but no less.

Everybody else: It's time to pile on Tom again. He's gone off the deep end
on natural keys again!

Tom Ellison

 

[James]

Now, is a "customer" the same thing as an "account".

For this example, yes.

Maybe I have the wrong idea. Is CustomerID a column, or is it the name of
the table. In reading of other people's databases, CustomerID has always
meant a column, not the name of a table. Do I have the wrong idea?

I was refering to tables for WidgetID and CustomerID. This appears to have
caused some confusion.

In some cases, the "ship to" address, not unlike the
WidgetInstallLocationAddress, is an attribute of the Account or Customer,
not the widget. If Joe orders a widget today and another tomorrow, will
they be for the same location? Maybe, probably, certainly? Will Joe have
a short list of locations at which he installs widgets, and which are
frequently reused? Just some possibilities for usefull exploitation in
modeling the data.

This is similar to ShipTo, as you state. Usually this will be the same
address as from the customer/billing record, but not nessesarily so. There
may therefore be a short list of locations where the are installed, but all
billed to the same customer record.

We do however need to be able to list the installlocation in the future. i.e
produce a query, that lists the Widget Serials, and WidgetInstallLocation,
or alternativly be able to query by WidgetSerial and display the
Widgetinstalladdress.

How do you envision finding an existing customer? The best answer is
this: What do you know when you need to find the customer? Is it a person
on the phone? If it is an existing customer, what does he say: "This is
customer 14072." I doubt it. He says, "This is Joe Wilson", right? So
you need a Joe Wilson type of combo box lookup. So, you type in "Joe Wi"
in the combo box, and it auto fills to say Joe Wilson. You open the combo
box list, but there are 7 Joe Wilsons there. How do you identify one from
another? Well, there are a lot of possibilities.

Here's my suggestion.

All this discussion is to suggest you add a column to the customer table
for

HumanKey and make the CustomerName / HumanKey combination a unique index.

There have been many debates on this. The above is my take.

I agree in principal. However at some stage we have to store a AccountNumber
(as this links to another invoicing system)

Ideally, the record could be searched for by Name (as you suggest), and the
select the possible matchs from a combo box list. However I am unsure how
this can be acheived.

 

[Tom Ellison]

Dear James:

So, would you care to record the different locations in the database, not
in each Widget delivery record, but in their own table? It would be keyed
by Customer + some other key, perhaps just a serial number, so each Customer
has locations 1, 2, 3, etc.

If the locations are properly qualified this way, it would give you the
capacity to aggregate all the installations by that factor. If you have
users type in the location address each time, they'll almost certainly not
type it twice the same way, and you cannot aggregage on it. This is just
one difference such a decision makes. Another is this: if the address of a
location changes (perhaps due to having been mis-typed in the first place,
then you could fix it for all the installations in one step, instead of
having to fix the address in several individual Widget rows.

My proposal about having a unique AccountName + HumanKey does not in any way
rule out having a unique AccountNumber as well.

What are your concerns or uncertainties about the "account lookup" process
for the form?

Tom Ellison