Results 1 to 6 of 6
  1. #1
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91

    Is this method of primary key usage just "Spoofing" second normal form?

    I have this many-to-one relationship; one RMA can have multiple parts returned associated with it. Second normal form would have me create a relationship table with two fields: RMA and Part Returned, which together comprise a superkey, rather than this current method where I just have a meaningless Autonumber as the ID for the parts returned table and relate it on the RMA field to the RMA table.



    Click image for larger version. 

Name:	relationships.PNG 
Views:	19 
Size:	54.5 KB 
ID:	46293

    Is this technically, therefore, not 2NF? Does it matter?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I don't really understand the question. ID from tblPartsReturned is not used as foreign key anywhere, nor is any combination of its fields used as a key. tblPartsReturned is a junction table. This is perfectly normal structure. Can set RMA_nb and partnumber as compound index to prevent duplicate entries.

    tblFailureCatConvert and tblFailureCategory are not linking with an ID PK.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    tblFailureCatConvert was a functional table for converting some imported data to the proper form for final import using the failure category lookup table (the original excel file had text in this field, I needed it converted to the matching ID number of the failure category table), so can be ignored as it is no longer used and exists only for posterity.

    To the main question at hand, I suppose I am wondering if it is still normalized properly when tblPartsReturned not only establishes the one to many relationship, but also contains further data about each of those parts. I'm wondering if to be fully normalized, it would have to be ONLY a junction table, and the fields related to each part by the part's ID field would be separated to a different table. I don't think there would be any benefit to doing this, I'm just wondering about how normalization is actually applied so I can better understand database design.

    tblPartsReturned holds information about each of the parts on a return, and one of those pieces of information is what RMA it belongs to. From what I understand, a junction table would contain just two fields, [RMA] and [partNumber], and either the unique combination of the two (not applicable here; multiple of the same part can be returned on one RMA) or an AutoNumber PK (applicable here).

    I think its difficult for me to explain my question here effectively, as the terminology is a little unwieldy to me and to communicate it effectively requires a lot of precise technical diction.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    No, junction table can have other data as well. Your table looks reasonable to me.

    Think of those records as 'transactions'. The data is about that transaction, describing: Who, What, When, Where, Why.

    However, if each 'return' transaction can have multiple parts, perhaps another table is valid. So a table tblRMA_Returns would have ReturnID, RMA_ID, ReturnDate and related child table tblReturnParts would have ReturnID_FK, PartID, Reason, Disposition, etc.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    I will say, the current structure does work very well. The main mode of display is to search for a specific RMA and then view the related parts and their disposition data. So, maybe this is an example where the use case of the application itself determines the best structure, rather than database theory alone.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    It is a balancing act between normalization and ease of data entry/output. "Normalize until it hurts, denormalize until it works."
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 2
    Last Post: 12-06-2019, 02:53 PM
  2. Replies: 2
    Last Post: 07-30-2018, 12:19 PM
  3. Replies: 17
    Last Post: 04-28-2017, 09:18 PM
  4. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  5. Replies: 4
    Last Post: 01-03-2013, 03:50 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