Organizational Question

[srstewart]

I have the following headings and I need to set up a database for them:

Order Number
Order Month
Customer ID
Customer Name
Customer City
Customer Discount
Associate ID
Associate Name
Associate City
Associate Commission
Product ID
Product Description
Product City
Product Price
Quantity Sold
Order Amount

I have basically organized them into three separate tables:

CUSTOMER INFORMATION
Customer ID
Customer Name
Customer Discount

ASSOCIATE INFORMATION
Assoicate ID
Associate Name
Associate City
Associate Commission

ORDER INFORMATIO
Customer ID
Order Number
Order Month
Product ID
Product Description
Product City
Prodct Price
Quantity Sold

Is this the best way to set it up, am I missing anything in an
particular table, what is the best way to keep track of the customer'
purchase history, and the best way to keep track of the associate'
commission? Note: this is just a small class assignment, not eve
being graded and no great detail is necessary.

Thank

 

[Duane Hookom]

Isn't it possible for one Order to contain multiple Product IDs? This would
suggest a change in your structure. Also, I would use an OrderDate rather
than Order Month. If these are your actual field names, I suggest you read
up on naming conventions at Tony Toew's site
http://www.granite.ab.ca/access/tablefieldnaming.htm.

 

[Mike Sherrill]

I have the following headings and I need to set up a database for them:

Order Number
Order Month
Customer ID
Customer Name
Customer City
Customer Discount
Associate ID
Associate Name
Associate City
Associate Commission
Product ID
Product Description
Product City
Product Price
Quantity Sold
Order Amount

I have basically organized them into three separate tables:

CUSTOMER INFORMATION:
Customer ID
Customer Name
Customer Discount

ASSOCIATE INFORMATION:
Assoicate ID
Associate Name
Associate City
Associate Commission

ORDER INFORMATION
Customer ID
Order Number
Order Month
Product ID
Product Description
Product City
Prodct Price
Quantity Sold

Is this the best way to set it up,

First, you did a better job than many, but this isn't the best way to
set it up. You're doing normalization, but you seem to be basing your
decisions on the names of the attributes. Normalization doesn't work
that way; it's based on functional dependencies. Your teacher
apparently didn't tell you what the functional dependencies are. So,
in your case, going by the names of the attributes is the best you can
do, I suppose.

Second, in the general case, any person could be both a customer and
an associate. Your design would require such a person to have two
different ID numbers (primary keys, right?), one in the table of
customers, and one in the table of associates. I hope the problem
with that is obvious.

Third, in the general case, a customer could be either a person or an
organization.

Google database newsgroups for "supertype" and "subtype".

am I missing anything in any
particular table,

You're missing something about associates, but I don't know what. An
associate might have something to do with a customer (sales rep,
responsible for billing, responsible for following up on deadbeat
customers). Or an associate might have something to do with an order,
or even something to do with other associates.

And there are four columns that start with the word "Product". That's
a clue.

what is the best way to keep track of the customer's
purchase history,

A table of orders.

and the best way to keep track of the associate's
commission?

Current commission or commission over time?