Do I need help with normalization?

[smitj2]

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

 

[Duane Hookom]

It seems to me there would be no need to store the Customer_Number in
tblOrderLine if it was the same value as Customer_Number from tblCustomer.
This would be redundant since the Order_Number can lookup the
Customer_Number in tblOrder.

There may be very rare cases where this might be necessary but I haven't
found them.

 

[Douglas J. Steele]

I'd say the book is wrong.

Like Duane, I see no need to have Customer_Number in tblOrderLine (you can
derive it from tblOrder).

As well, it would appear that order quantities are missing from their
suggestion.

What book is it, so that we can know to avoid it?

 

[Tim Ferguson]

tblCustomer (CUSTOMER_NUMBER, Title, Surname, Forename, Initials,
Address1, etc)

Okay, I don't see much wrong with that.

tblOrder (ORDER_NUMBER, Part_Number, Quantity, Customer_Number, Date)

What is the PartNumber doing here? Assuming that an order can be for more
than one product at a time, then PartNumber and Quantity are attributes
of the order line, not the Order itself.

tblProduct (PART_NUMBER, Description, category, Price, No_in_stock,
Re_order_level, etc)

Okay, kind of.

tblOrderLine (Order_Number, Part_Number, Customer_Number)

As pointed out elsewhere, this is a load of rubbish again. The
CustomerNumber is an attribute of the Order not the OrderLine (and there
it is, too) but the quantity is missing -- does anyone ever buy more than
one of something?

I don't think you are missing anything. Let's be generous and say tbe
book's proof reader fell asleep. Can't be easy wading through page of R
theory looking for mistakes!

B Wishes

Tim F

 

[Tom Lake]

It seems to me there would be no need to store the Customer_Number in
tblOrderLine if it was the same value as Customer_Number from tblCustomer.
This would be redundant since the Order_Number can lookup the
Customer_Number in tblOrder.

There may be very rare cases where this might be necessary but I haven't
found them.

Given an order record, how would you know what customer was associated with
that order if it didn't have a customer_number in it?

Tom Lake

 

[Douglas J. Steele]

Given an order record, how would you know what customer was associated
with
that order if it didn't have a customer_number in it?

tblOrder has the customer number. Unless different lines on the order can
apply to different customers (an extremely unusual design), tblOrderLine
doesn't require it.