Results 1 to 9 of 9

Thread: Normalization

  1. #1
    Join Date
    Aug 2009
    Posts
    8

    Unanswered: Normalization

    Hi, I am making a database for an airplane mechanic who wants to keep track the information on the planes they work on. I have it centered around the work orders. I would like to hear what everyone thought I have done wrong so far. Fatalistic I know.

    I appreciate any help,
    Joseph
    Attached Thumbnails Attached Thumbnails norm.jpg  

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums

    Well, by only looking at your table design I'd suggest making sure none of your field names are Access Reserved Words for starters (I saw "type" in there e.g.).

    Otherwise, knowing nothing about the project other than how you've setup your tables so far, all I can suggest is to look at database normalization and suggest that you make sure that you are familiar with the rules, your project after all .

    Cheers!
    Me.Geek = True

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yeah normalisation may be a smart move
    ferinstance you have a table holding contacts, the contactid is referred to in the workorders, and it also appears in workdone.
    however fundamentally its down to how you envisage the data model

    have you read the standard on line tomes we normally refer to
    http://www.r937.com/Relational.html
    and
    The Relational Data Model, Normalisation and effective Database Design

    I'd aslo suggest you try to get some consistentcy in yoiur column names so they are all of the same style
    all CaMeLCaSe or underscored or runtogether

    eg
    ID_Aircraft or IDAircraft
    generally I'd expect to see AircraftID or Aircraft_ID as the ID belongs to the aircraft
    you use No and #
    I haven't a scooby what SMOH, SMOHP & STOH are (and Im not to sure I want to know....
    I'd also be wary of using symbols like / or # iin cilumn names.. it may well be fine
    mnany people depreacte the use of the tbl prefix, suggesting instead the table anem should the be plural of the items in the table eg WorkOrders, PartOrders
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2009
    Posts
    8
    Thank you both for your suggestions. I'll read the material and implement it into my database. I appreciate your help, and any help anyone has to offer.

    When I started this, I forgot that the user should not see the entries, so I put the symbols in. I keep thinking they will see the tables, but they shouldn't if the forms and queries are done correctly.

    Hmm, in looking at the layout, the reason I have Contact_ID in the three fields is the contact table holds both the mechanics and clients. I was told that all the people should be in the same table. The work done table is for the mechanics while the contact in the tbl_Work_Orders is the owner of the plane. Or should the WorkDone table be connected between the contact and WorkOrder tables?

    Joseph

    P.S. - SMOH= since major overhaul, SMOHP=since major overhaul propeller, STOH=since top overhaul. Not that you wanted to know, I just thought I would share.
    Last edited by jaldred1; 08-05-09 at 19:26.

  5. #5
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    Normalisation

    I notice that you have a series of lookup tables with the structure ID + Data, which is fine. However, you are copying the data into the work order. In other words, you have looked up an item of data and copied it into the work order after which you have lost contact with the lookup table. If you join the data items, then the data item in the lookup table becomes a de facto primary key, in which case why do you need the ID ? Then again, if you want to change the data item, you would have to go round all of your work orders and update them. True normalisation is where you copy the IDs from the lookup tables (as foreign keys) and execute joins in queries to view the data itself. It is also a good idea to give your foreign keys the same name as the primary key in the lookup table, because Access (and other databases) have lots of underlying stuff that will recognise the links, create relationships and generally handle the data more efficiently.

    I hope this helps.

  6. #6
    Join Date
    Aug 2009
    Posts
    8

    How does this look

    I went back and messed with it again. Hopefully this looks better to someone. If not, and suggestions would help.

    Thanks,
    Joseph
    Attached Thumbnails Attached Thumbnails suggestions.jpg  
    Last edited by jaldred1; 08-09-09 at 15:49.

  7. #7
    Join Date
    Aug 2009
    Posts
    8

    Oops . . .

    Maybe this is better.
    Attached Thumbnails Attached Thumbnails suggestions.jpg  

  8. #8
    Join Date
    Aug 2009
    Posts
    8
    I just had another thought. Would I want to have another table off the Airplane table that keeps track of the different models of planes? So that way there isn't several different spellings of the same model, if someone leaves out a dash or something like that?

    Joseph

  9. #9
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Probably wouldn't hurt - ModelID, Manufacturer, Model, and that way, ref the ModelID in the aircraft table.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •