Results 1 to 10 of 10
  1. #1
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253

    Normalization Question


    I currently have a table and i am wondering if it needs some normalizing. I am thinking the testing info needs to be separated from the shipment information. Currently the table tracks all the information for a shipment that is received and if it was tested and when. Here is the table currently. Please advise on the best normalization of this. Thank you. --Walker

    Click image for larger version. 

Name:	table.JPG 
Views:	38 
Size:	149.2 KB 
ID:	41651

    EDIT: Also if splitting this table is proper normalization, what is the best way to make new tables and keep all the data intact. I am trying to figure out if it is a one to many or a many to many situation. That is where i am needing the help.

  2. #2
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    I have been going through the normalization links that were given to me before in a previous post. Here is what i broke it into. Is this proper normalization? Did i break it down too far or am i just not getting the concept?
    Click image for larger version. 

Name:	newrelate.JPG 
Views:	37 
Size:	41.8 KB 
ID:	41652

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Nobody can evaluate your model without some knowledge of the "business" that this model represents. It certainly appears that you have broken your original single table into meaningful related tables - but as they say-the devil is in the details. Your field descriptions seems well done and good to see --especially if someone else will maintain the application.

    Is there a tblTestType? tblContract?

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    In addition to the details comment, I'll advise to make field names intuitive. You will find ID is not, especially when you use it everywhere. You might also want to consider naming related fields with related names. ID > EmailID implies a mistake to me; LotIDpk > LotIDfk tells me a lot more than what you have. You will find this helpful when trouble shooting sql, expressions and code.

    Last, be careful of joining everything in a chain like that. If one piece of info in between the beginning and end of related fields is missing, you will likely find you cannot get info out and in. You might be able to get info out, but you may not be able to edit because you will have to use an outer join. As Orange said, we don't know everything about the process but I don't see why you split tests and test info, and is that a table about user email info or is it about testers who test? Names should reflect as close as possible what the entity is because the details get lost in the fog of time.

    I advocate queries before forms/reports and that these things should be based on queries, not tables. You can sort, filter or even lock a query, plus if you find your relationship prevents you from editing/appending/deleting from a query in testing (and it needs to do that) you haven't wasted a lot of time building a form that won't work because of relationships or whatever.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    I am writing up a narrative to post on the daily business process. Please give me a little bit to get this to you. I'm not a very quick typist but all this coding is making me better.

    I'll advise to make field names intuitive. You will find ID is not, especially when you use it everywhere
    I was thinking about this and because of what you said. It was getting confusing already. Thank you for pointing me in a better direction.

    I don't see why you split tests and test info
    I thought i should split this because some parts only have one test or any combination of 3 test types. I thought there might be too many blank spaces in the table.

    is that a table about user email info or is it about testers who test?
    It is about the user and their information but all of the testers that test are in the users email table.

    Last, be careful of joining everything in a chain like that. If one piece of info in between the beginning and end of related fields is missing, you will likely find you cannot get info out and in. You might be able to get info out, but you may not be able to edit because you will have to use an outer join.
    I dont completely understand what you mean by this yet.

    I advocate queries before forms/reports and that these things should be based on queries, not tables. You can sort, filter or even lock a query, plus if you find your relationship prevents you from editing/appending/deleting from a query in testing (and it needs to do that) you haven't wasted a lot of time building a form that won't work because of relationships or whatever.
    I am not sure what you mean by this part either.

  6. #6
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Orange,

    Is there a tblTestType? tblContract?
    Yes there is a test type table but no contract table

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I am not sure what you mean by this part either.
    Some will base their forms on tables, then find out that they cannot enter or edit data. Sometimes the reason for that is the relationships they've mapped out. That's pretty much it.

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    You need a shipments table (like tblShipments: ShipmentID, ShipmentDate, VendorID, ...);
    You need a parts table (like tblParts: PartID, PartNum, PartName, ...);
    You need a shipment lots (essentially shipment rows) table (like tblShipmenttLots: ShipmentLotID, ShipmentID, LotNum,...)
    You need a shipment lot parts (essentially shipment lot rows) table instead your current tbl_LotInfo (like tblShipmentLotParts: ShipmentLotPartID, ShipmentLotID, PartID, PartQty, ...). In case any lot always contain a single part, you can consolidate it with tblShipmentLots;
    You need a shipment lot part tests table (like tblShipmentLotPartTests: ShipmentLotPartTstID, ShipmentLotPartID, TestID.

  9. #9
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Here is the narrative for the database that i am trying to build.

    We are a quality control shop. We receive shipments of parts from vendors. Some vendors make multiple parts. Some parts are made by multiple vendors. Once the shipment arrives we need to track the vendor information. We need to track the vendor, whether or not the vendor is currently an active supplier, the vendor lot number, the vendor container shipment number (CSN) (if applicable), the contract number the shipment is under, the date it was received, the quantity received, if the parts are new or used, and the starting and ending serial number of the shipment (If they are a serialized part. More are not serialized than those that are). This information is entered into the database before the shipments are given to the inspectors. We also need to know the part number and the part name. We need to track which drawing the part belongs to and which procedure is used to do the testing.


    After the shipments are given to the inspectors, there are currently 3 different types of inspections that could be done; electrical, mechanical and chemical. The parts can have one or more inspections. We need to know the date each test type starts and ends. We need to know how many of each shipment are actually tested and how many failed. We have a chart that tells the inspectors how many to inspect based on the quantity that were received. We need to know which test type failed and why.


    If there is a problem with the shipment, it can be put on hold pending the results of a MRB (material review board). We need to know when it was put on hold and who put it on hold. We then need to know when it was removed from the on hold status and who approved the shipment to be removed from on hold status. Also need to know what the results of the MRB were.


    After the parts have passed all inspections, they have to be accepted by the inventory controllers so the parts may be used in production. An email will be sent to let the inventory controllers know the inspections are complete. We also need track if the email was sent.

  10. #10
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Nobody can evaluate your model without some knowledge of the "business" that this model represents.
    Orange,
    I posted the business model I hope it makes sense.

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

Similar Threads

  1. Normalization Question
    By pyrotaz in forum Database Design
    Replies: 4
    Last Post: 11-21-2017, 04:28 PM
  2. Normalization Question?
    By sprtrmp in forum Database Design
    Replies: 11
    Last Post: 06-17-2016, 05:22 AM
  3. Normalization
    By jzacharias in forum Database Design
    Replies: 9
    Last Post: 05-24-2015, 12:26 AM
  4. Replies: 3
    Last Post: 12-13-2013, 11:22 AM
  5. Need Help about Normalization
    By Book3s in forum Access
    Replies: 2
    Last Post: 10-24-2013, 02:03 AM

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