Results 1 to 5 of 5
  1. #1
    pyrotaz is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    6

    Normalization Question

    Hi, I'm very new to access and am trying to teach my self a little before taking a class this spring. I have the following set of data and want to make to first normal form, then second and finally third.
    OrderID Date Products Prices Month
    1 10/2/2017 Nut,Washer, Bolt $1.19,$0.89,$2.19 October
    1 10/19/2017 Bolt, Pin $2.19,$0.87 October
    2 10/19/2017 Screw $1.49 October
    1 10/31/2017 Bolt $2.19 October
    1 11/14/2017 Pin $0.87 October

    If I am correct all I have to do to make it into First Normal is this:
    OrderID Date Products Prices Month
    1 10/2/2017 Bolt $2.19
    1 10/2/2017 Washer $0.89
    1 10/2/2017 Nut $1.19
    1 10/19/2017 Bolt $2.19
    1 10/19/2017 Pin $0.87
    2 10/19/2017 Screw $1.49
    1 10/31/2017 Bolt $2.19
    1 11/14/2017 Pin $0.87


    I set my primary key as OrderID.

    Am I on the right track or am I way off.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Yes, you are on the right track, though you cannot use OrderID as your Primary Key, as it is not a unique value.
    You could just add an Autonumber field, and let that be your Primary Key.

    I am also not sure what the "Month" field is supposed to represent. If it is just derived from the Date field, you don't need it, as it can be calculated "on-the-fly" in a query.

    It may be possible to pull the price fields out of there too, if it doesn't change (or if it does change, but price can be stored in another table and linked to).

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Very close. A PK cannot be repeated, else it isn't primary at all. You could use an autonumber PK as OrderDtlID (order detail id) because that's what this is - details pertaining to an order. Thus you'd need an orders table for the parent data. Some would suggest that your OrderID field be named OrderID_FK or something like that, to denote that this field is a primary key somewhere else. With this sort of setup, you would have one order id to many order detail rows. Can you see why you would not do this for pricing?

    As long as you're learning stuff...
    Normalization is paramount. Diagramming maybe not so much for some people.

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.ca/...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.ca/...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.ca/...cation-in.html

    Important for success:
    One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
    About Auto Numbers
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    About calculated table fields - http://allenbrowne.com/casu-14.html

    There are tons of other sites, some with videos.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It seems you are dealing with a typical Customer Orders Products scenario.

    Where you have

    tblCustomer--->tblOrder-->tblOrderDetails<---tblProduct

    signifying
    A Customer makes 1 or many Orders
    An Order contains 1 or Many Details/LineItems
    A Product may be referenced in 1 or many Details/LineItems

    You may get more design info from links here.

  5. #5
    pyrotaz is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    6
    Thank you Joe,

    I set-up a auto number field like you stated and set it as the primary key I then deleted the month field.

    This is where I am a little confused. I know in order to become 2NF it has to be in 1NF and Every field must be dependent upon the entirety of the primary key.
    I know that I must break the original table up into smaller tables each with its own primary key and foreign key.
    Here's the tables I'm thinking:
    A new table is created with ID and OrderID ( Primary Key =OrderID and foreign key =ID back to ID in main table
    A new table is created with OrderID and date ( Primary Key =date and foreign key =OrderID back to OrderID in ID table
    A new table is created with Date and products ( Primary Key =products and foreign key =date back to date in Order table
    A new table is created with products and price( Primary Key =price and foreign key =products back to products in date table

    Have a feeling I may be way off in this one

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Normalization Question?
    By sprtrmp in forum Database Design
    Replies: 11
    Last Post: 06-17-2016, 05:22 AM
  2. Normalization
    By jzacharias in forum Database Design
    Replies: 9
    Last Post: 05-24-2015, 12:26 AM
  3. Normalization
    By jlt199 in forum Database Design
    Replies: 10
    Last Post: 03-21-2014, 12:22 PM
  4. Replies: 3
    Last Post: 12-13-2013, 11:22 AM
  5. Normalization
    By U810190 in forum Access
    Replies: 1
    Last Post: 03-30-2010, 04:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums