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

[w_a_n_n_a_l_l_ -]

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.

[Bob Quintal]

"smitj2" <sm****@tiscali.co.uk> wrote in
news:11**********************@i40g2000cwc.googlegr oups.com:

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

The fact that the author of the textbook is wrong!
--
Bob Quintal

PA is y I've altered my email address.

[Larry Linson]

"smitj2" <sm****@tiscali.co.uk> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...

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)

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

[cjb_kjb]

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).

Larry Linson wrote:

"smitj2" <sm****@tiscali.co.uk> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...

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)

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