Do I need help with normalization?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

smitj2 - 04 Jun 2006 15:50 GMT

Hi

I am studying an IT course and thought I was just beginning to master
the basics of normalization. However I came across an example in my
text book that wasnt what I would have arrived at.

It was the structure for a transactional database which allowed several
items to be purchased in a single order.

The text book suggested (Primary Key in capitals):

tblCustomer (CUSTOMER_NUMBER, Title, Surname, Forename, Initials,
Address1, etc)
tblOrder (ORDER_NUMBER, Part_Number, Quantity, Customer_Number, Date)
tblProduct (PART_NUMBER, Description, category, Price, No_in_stock,
Re_order_level, etc)
tblOrderLine (Order_Number, Part_Number, Customer_Number)

By myself I would have thought of:

tblCustomer (CUSTOMER_NUMBER, Title, Surname, Forename, Initials,
Address1, etc)
tblOrder (ORDER_NUMBER, Customer_Number, Date)
tblProduct (PART_NUMBER, Description, category, Price, No_in_stock,
Re_order_level, etc)
tblOrderLine (Order_Number, Part_Number, Quantity)

What am I missing?

Thanks

Reply to this Message

w_a_n_n_a_l_l_ -@-_s_b_c_g_l_o_b_a_l._n_e_t - 04 Jun 2006 16:35 GMT

I think you're missing a text with a good example. Even given the
variability of how different people might do the same task, your arrangement
is clearly the more correct, assuming any normal meaning of terms like
Custome, Order, Product, OrderLine.

Reply to this Message

Bob Quintal - 04 Jun 2006 16:48 GMT

> Hi
>
[quoted text clipped - 27 lines]
>
> Thanks

The fact that the author of the textbook is wrong!

Signature

Bob Quintal

PA is y I've altered my email address.

Reply to this Message

Larry Linson - 05 Jun 2006 02:35 GMT

> Hi
>
[quoted text clipped - 22 lines]
> Re_order_level, etc)
> tblOrderLine (Order_Number, Part_Number, Quantity)

The textbook solution could be correct if they simply forgot to capitalize
Customer_Number as part of a compound key in tblOrder and tblOrderLine. That
would mean that Order_Number is unique only within Customer, which is not an
uncommon approach to numbering orders.

If Order_Number is a unique key without customer, then your solution is
correct.

Larry Linson
Microsoft Access MVP

Reply to this Message

cjb_kjb - 06 Jun 2006 00:23 GMT

The book's just plain wrong.

There can be no reason for having Part_Number in tblOrder.

Even with Larry's suggestion regarding the key to tblOrders being a
compound key of Customer_Number and Order_number (so every customer has
orders 1, 2, 3, 4....etc),
I'd be tempted to introduce a field called ORDER_ID as the primary key
(autonumber in Access) that would not be visible to the application and
Order_Number could be an application maintained field (auto number
wouldn't work for Order_number because the numbers are repeated for
each customer).

> > Hi
> >
[quoted text clipped - 33 lines]
> Larry Linson
> Microsoft Access MVP

Reply to this Message