By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 2,596 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Do I need help with normalization?

P: n/a
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

Jun 4 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.
Jun 4 '06 #2

P: n/a
"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.
Jun 4 '06 #3

P: n/a
"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
Jun 5 '06 #4

P: n/a
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


Jun 5 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.