Results 1 to 4 of 4
  1. #1
    Mozencrath is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    11

    Help with Cascading M to M Data Model

    I am running into a roadblock setting up a relational model...It is for government Contracting. Every item the gov procures uses a National Stock Number (NSN) which uniquely identifies a product/item...however, most NSN's have more than one approved manufacturer/supplier...Example NSN 1670-01-028-8664 has three different approved manufacturers and each manufacturer has its own Part Number.

    So, each product has a unique identifier number (NSN) and each product may be made by one or many different companies. Each companies product has its own unique Part Number for that product. Products are orderedd via NSN and each order calls out a specific manufacturer and their part number. Some manufacturer part numbers can also be listed on multiple products (NSN's) i.e. Their is an NSN for a red chair and different NSN for blue chair but the manufacturer uses the same PN for all colors so 2 different NSNs can have the same mfr/pn.

    I already have the raw data I want to import in excel which my other question being, how best to import bulk data where you already have the "join table" data and are not simply entering from scratch?

    The only solution I've thought of (dunno this works) is:
    Tables
    Table 1: NSNTable (has FIELDS NSNid and NSNDescription)


    Table 2: MfrTable (Field is Manufacturer name)
    Table 3: PN-NSN-MfrJoinTbl (has a foreign key field for NSNid in T1, foreign key field for MFRID in T2 and a third field with no key for PN...

    Something doesn't seem right about this....I eventually also need to add a fourth table for Awarded Contracts (each contract can contain 1 or more NSN's. For arguments sake, most contracts are for one NSN however, every contract ALSO identifies the specific mfr part number that they want to be supplied....I'm tripping up because I don't know how to incorporate and order join table that has more than the usual OrderID FK and ProductID FK fields (need to add not only productID but specifically what manufacturer of that product and that mfr's associated PN.

    THANKS!

    The other idea I came up with is in the picture attached....
    Attached Thumbnails Attached Thumbnails Screenshot_20210703-180038_Database Designer.jpg  

  2. #2
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,439
    To help understanding of this comment

    Their is an NSN for a red chair and different NSN for blue chair but the manufacturer uses the same PN for all colors so 2 different NSNs can have the same mfr/pn.
    When you come to place an order - how do you specify the colour?

    I already have the raw data I want to import in excel which my other question being, how best to import bulk data where you already have the "join table" data and are not simply entering from scratch?
    multiple queries, one for each table. Query linking your source data to the relevant field in another table to pick up the PK/FK

  3. #3
    Mozencrath is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    11
    So this DB isn't for placing orders. It'ds for reviewing historical government contracts. To answer, if the gov wants to buy the red chair, it has a completely different NSN than the blue chair. They'll say "we want to buy NSN 1000-01-111-4321 and we will only accept the chairs made by So and So Furniture company (company CAGE code 71122) and their part number is ABC123.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,908
    Quote Originally Posted by Mozencrath View Post
    So this DB isn't for placing orders. It'ds for reviewing historical government contracts. To answer, if the gov wants to buy the red chair, it has a completely different NSN than the blue chair. They'll say "we want to buy NSN 1000-01-111-4321 and we will only accept the chairs made by So and So Furniture company (company CAGE code 71122) and their part number is ABC123.
    OK, but if the manufacturer uses their own internal identifier for the chair and it is the same number regardless, then what does it matter if you are giving each of those a different part number if the manufacturer does not do that? I think that is the piece that is missing.

    The requirement does not seem unlike what I used to deal with at work. There'd be a table of manufacturers, a table of internal (to us) part numbers called RM's and a junction table of RM's to manufacturer because we could get the same RM from more than one supplier. That's fine for things like fasteners or anything else that is common across the supply chain. However, any aspect of the item that distinguished it from another item meant it had a different part number - in our system AND the supplier system. There's no way a 1/2"x3" grade 5 bolt had the same part number as a 1/2"x3" grade 9 bolt. It just doesn't make sense. Nor should a red chair have the same part number as an otherwise identical blue one; not in anyone's system.

    To answer the second part of your post I'll say that Excel data almost never fits into a normalized database schema because spreadsheet data is column based and db data should be row based. If the db tables are not properly normalized you will forever have trouble. If that is a new concept to you, it's best that you research db normalization before you worry about how to get data in or out.
    Grief o'er the loss of one loved is not a destination, but a journey.
    Oft content upon arrival are we, only to find there are miles yet to travel.

    Micron

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

Similar Threads

  1. Procurement data model
    By pncbiz in forum Database Design
    Replies: 16
    Last Post: 01-28-2021, 07:38 AM
  2. Help with data model/er diagram
    By Beanie_d83 in forum Database Design
    Replies: 4
    Last Post: 06-22-2016, 07:25 AM
  3. Bill of Materials Data Model
    By uaguy3005 in forum Database Design
    Replies: 3
    Last Post: 12-21-2015, 02:56 PM
  4. Importing excel into access data model
    By aisling21 in forum Access
    Replies: 2
    Last Post: 05-12-2015, 07:03 AM
  5. Transform data model from Access to X
    By snoopy in forum Database Design
    Replies: 2
    Last Post: 05-29-2012, 12:37 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