Results 1 to 9 of 9
  1. #1
    Bagels is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2023
    Posts
    22

    Help with establishing relationships

    For some reason i'm having difficulty thinking through in my head how I should set this database up. I'm looking at the relationships and I know something isn't right. I only just started so I want to make sure it's setup correctly before I start dumping tons of data in it.

    It's a database to determine if parts we sell comply with various different regulations (5 to be exact)
    The parts each contain many components which we source from various vendors. Those components could be used in many different parts.

    I'm verifying that each component complies - and if doesn't- what chemical(s) it contains that causes it not to.
    Ultimately i want to be able to say ok, this part has 30 components, all comply, it complies. Or it has 2 components that are causing it not to and these are the restricted substances it contains.

    I have the following tables:
    *ComponentValidation: ComponentPartNumber is the pk (our internal part number), VendorPartNumber, a yes/no checkbox for each of the 5 regulations to say yes/no it complies or not.
    *Chemicals: a list of all restricted substances (pk) , and which regulation they correspond to (some belong to more than 1 regulation)



    here's where i'm getting unsure
    *PartBOM: FinishedPartNumber, ComponentPartNumber - here i'm unsure because neither is a pk. Each FinishedPartNumber will only use a component once, but again the component itself could be used in multiple finished parts.
    *NonCompliantComponents: ComponentPartNumber, Restricted substance(s). Again, not sure here i don't think this is correct . It could be that a component has more than 1 restricted substance

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What are you building that components are composed of chemicals?

    Manufacturing/Assembly type db is one of most difficult to design.

    Possible tables:

    Parts

    Components

    Chemicals

    Regulations

    PartsComponents

    ComponentsChemicals

    ChemicalsRegulations
    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
    Join Date
    Apr 2017
    Posts
    1,673
    I see you have ta table for chemicals with restrictions for them, but you don't use it at all (all regulations are entered manually for components). I'll assume this isn't intentional!

    My advice (on fly) is:
    You need:
    tblChemicals: ChemicalID, ChemicalName;
    tblRestrictions: RestrictionID, RestrictionType, RestrictionSeverity, PercentageLimit, ... (RestrictionType is for your 5 different regulations, RestrictionSverity is optional parameter in case some restrictions are limiting only, percentageLimit determines, when the restriction is applied);
    tblChemicalRestrictions: ChemicalRestrictionID, ChemicalID, RestrictionID (A chemical can have here several rows, even for same restriction type but for different severity);
    tblComponents: ComponentPartNo, ComponentDescription, ...;
    tblVendors: VendorID, VendorName, ...;
    tblComponentVendors: ComponentVendorID, ComponentPartNo, VendorID, [VendorPartNo], ...;
    tblComponentVendorChemicals: ComponentVendorID, ChemicalID, ChemicalPercentage (I added this table, as from your post I understood, that same component from some vendors may be restricted, and from other vendors not);
    tblProducts: ProductPartNo, ProductName, ...;
    tblProductComponents: ProductPartNo, ComponentPartNo (In case you need a composition of product only for deciding about applied restrictions you don't need a full BOM table for product - a simple list of components is all you need. When calculating restrictions for given product, you also need somehow to generate a list of components vendors specifically for this calculation):

  4. #4
    Bagels is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2023
    Posts
    22
    Quote Originally Posted by June7 View Post
    What are you building that components are composed of chemicals?

    Manufacturing/Assembly type db is one of most difficult to design.

    Possible tables:

    Parts

    Components

    Chemicals

    Regulations

    PartsComponents

    ComponentsChemicals

    ChemicalsRegulations
    We make electronic controls for appliances
    It's basically a PCB board with many components
    A lot of the components are gold, tin plated, thus there are some "restricted" substances per various standards
    It's not even "bad" if those substances are present, i just need to report if they are
    So i'm just trying to work toward a database that will tell me that, based on it's BOM

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What is current status? Did you get some insight from the responses by June and Arvi?
    Can you post what you have?
    Perhaps you could describe the creation of a few components so readers get some appreciation of the process(es) involved and where the "restricted chemicals" get into the picture.

  6. #6
    Bagels is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2023
    Posts
    22
    Yes orange, i definitely gained some insight and I appreciate the thoughts. What I am learning is this might be more complex than I thought.

    I looked and we around 4,000 components in total that we purchase. Granted, a lot of them are in groups, i.e. many resistors just different values or tolerances for each. And BOM's usually have 50 or so components.

    So to gather all this information and arrange it in a meaningful way is daunting.

    I would say 95% of the components DO comply to all the regulations, from my research so far.
    I'm wondering if I should just sift out the 5% that don't , then just query the BOM to tell me if any of those components are present

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm not sure I'm following the sequences of processes, but if you know that a part X contains some "forbidden/restricted chemical", then record that fact, and for reporting, any component that contains part X will be a on the "prohibited chemical" list.

    In general, a scenario such as below may be useful. I realize you still have to deal with BOM, but this is meant as a guideline to consider.

    Parts
    Part a
    Part b
    ..
    Part xyz

    Parts with restricted chemical

    Part b
    Part q

    Components
    Component1 made of Parts a,c,f,t,h,j,w
    Component2 made of Parts a,b,c,d,k,p
    .....
    Component99 made of Parts c,r,t,y,q,x


    Components with "prohibited chemicals"

    Select component from components where component-part in ("b","q)

  8. #8
    Bagels is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2023
    Posts
    22
    I think you have part and component reversed, but otherwise it makes sense to me

    Component A, B, C, D, E, F = ok / complies
    Component G, H = not ok, contains restricted substance

    Part 1 = made of component A, B, C, D
    Part is ok

    Part 2 = made of component A, B, C, D, G
    Part is not ok
    Why?
    Has component G, which contains [whatever substance]

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Bagels,

    You are correct--I have the terms mixed. Sorry about that. It was the approach to a pattern I was hoping to convey,

    If one of your Components can have multiple "prohibited chemicals", then you may want to record that in the

    ComponentContainsProhibiedChemicalTable

    ComponentG contains Phthalate
    ComponentG contains Tellurium
    ....
    ComponentZ contains tetrachlorvinphos
    Last edited by orange; 04-20-2023 at 05:45 AM.

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

Similar Threads

  1. Replies: 1
    Last Post: 06-05-2019, 09:26 AM
  2. Establishing a simple relationship?
    By Harry2 in forum Access
    Replies: 7
    Last Post: 06-16-2014, 02:41 PM
  3. Replies: 1
    Last Post: 03-23-2014, 11:27 PM
  4. Access Tables establishing PK and RI
    By Spanky2012 in forum Access
    Replies: 1
    Last Post: 11-08-2012, 02:53 AM
  5. Establishing Limits
    By Niki in forum Access
    Replies: 4
    Last Post: 04-07-2011, 12:01 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