Help with Relationship building and Table Structures

[Jman95]

Hi,

I'm currently working on building out an access database to help my company with basically Inventory tracking. I have taken classes in the past with access so I'm not completely new, but haven't touched it in years so I'm running into some basic issues. Just want to get your opinions on if I'm getting onto the right track and any advise is appreciated. The basic connections I can get to work, It's now getting into the complex and basically a BOM situation.

My company has a Raw Material that is ran off of a tool. This tool can either be 1 part number or a left and a right part (2 part numbers). So, basically 1 sheet can either give you 1 part, or 2 parts depending on the tool.

1 Material = 1 part OR 2 individual parts dependent on the part number.

The Idea is I'll create a unique AlternateID for these combo parts, using PID1 and PID2 (two different part numbers). That Alternate ID will pull from the Raw Sheet (Material ID) which will subtract 1 total sheet, and add to two different part numbers in Inventory.

Here is the screenshot of my current set up. This is my test database so it's bare bones right now.

I believe my connection for Part Information to Material Information is great, but I think there may be an easier or simpler way to do the Alternate ID table. Referential Integrity does not work even though its Number to Number formats. Also, down the road I'm not sure if this set up will also calculate the single part numbers (1 sheet = 1 part) as it is not referenced in the Alt ID table.

Any suggestions?

Thanks,
J

 

[plog]

The biggest issue I see is the same data stored all over the place making you have a spiderweb of relationships. There should only be 1 way to travel between tables in a Relationship. It seems every table of yours is connected to every other one. This is the big issue you have. I see Part Number, PID1 and PID2 all over the place.

I also know that all products will go into the same table, there is no reason to seperate Product 1 from Product 2. In fact, the number seems to represent nothing other than a way to differentiate them. You were a little too generic with your example so I don't have a firm grasp of your data nor (and more importantly) what you hope to do with it. I need some questions answered before I can really help:

1. What do you hope to achieve with this database? What will you use it for to tell you?

2. What is your thinking with each table? Explain what you hope that table is doing in your database. Right now Material Information is the only table that makes sense to me.

3. Am I right about Product 1 & Product 2? If you have material that makes both, there's no real reason one gets labeled Product1 and the other labeled Product2 right? It's not like theres a real difference other than you need a way to differentiate them.

 

[Jman95]

1) So my company is small and old school so we don't have any centralized system for our information. I'm working on building out a database so that we can have reports for our management team on the floor. The buckets I want it to tell me are Raw Material Inv (plastic sheet), Finished Goods Inv, and compare them to our open POs for our parts.

2) - Material information is what you'd expect. Each unique sheet has a material ID and it's own identifiers.
-Part Information is the table to tie in a UID to our customer ordered part numbers. The purpose of this is to tie Materials to Part Numbers to our PO's.

- So 1 sheet of material goes into a tool that is either 1 sheet = 1 part, or split 1/1 (left part, right part). What I'm trying to do is relate that difference when calculating sheet. To do that I created a unique AltID that contains Part 1, and Part 2. Example would be Part1=9504 and Part2=9505, the Alternate part # would be 9504/05.

-The part 1 and part 2 tables are only there because I was trying some things because my query was coming up with results for Part 1 but didn't give results for Part 2 when querying the Part Number, MatID, AltID

-After that you allocate sheet, I have 100 raw sheet in stock, how many sheet do I allocate to this job? Meant to help our production team to dedicate our inventory and plan when we are going to run which part numbers and tell what sheet we need to order next.

3) You are right, my real struggle is finding a way to differentiate them and tie the relationship together. The Part 1 and Part 2 is an attempt to connect the difference between our tools and how it is processed through production. I have a PO for 9505, the tool to run production has to be ran next to the 9504 mating part so it needs to be considered when looking at our parts on order.

Like mentioned, if you have 1 sheet in raw materials, this can make different varieties of parts. Mainly:
-Mat= 1 part
-Mat= 1 left part, 1 right part
-Mat= 2 left part, 2 right part
-Mat= 4 parts

Hope that info helps.

 

[GaP42]

Hi Jman95 - as plog indicates the model of the data is confusing.
Your basic entities and relationships could be:
Material (as the stock of what is used /consumed in the production process). Are each of the items consumed individually recognised eg with a serial no, and not by a stock type. You indicate the former, I think.
Product (as what is produced and added to inventory) There are a number of types, and they may be described with attributes such as left and right (and perhaps some are produced as pairs or other combinations). Each product is of a type and identifiable by ProductID. This could be handled through a self-referential relationship (attribute PairedID or ComponentID holding a Product ID for the Parent Product). Avoid making specific tables for different products, use product type to describe the details in a generic form to accommodate change most easily.
Material-Product Allocation - Linking sheet used (Material ID, Product ID) - for one material id 1 or more product IDs may be associated) . This represents what material was used to produce what particular products. (It is not a reference table of what product types may be produced from a type of material). It resolves the m:n relationship between materials and products. Be wary of how you populate this table when you have Products that are Parents for child Products - participate in self-referential relationships.
Your quantity on hand of materials will be based on Material Type not material. Same for your Product Inventory. Both inventories are handled by looking at transactions - records of the amount consumed or produced (and purchased/dispatched for material and product. Your consumption and production are reflected in the Material-Product Allocation - counting of unique Material Type associated with each Material ID with a time period, or ProductID/ProductType. (Your inventory is not based off for eg a Material ID as these are unique to each item - so there is only a quantity of 1)
Build your Material Type and Product Type tables off the Material and Product tables respectively. These will reflect the attributes of those items to the level that enables you to describe any item as necessary for your inventory management.

 

[Jman95]

Hi Jman95 - as plog indicates the model of the data is confusing.
Your basic entities and relationships could be:
Material (as the stock of what is used /consumed in the production process). Are each of the items consumed individually recognised eg with a serial no, and not by a stock type. You indicate the former, I think.
Product (as what is produced and added to inventory) There are a number of types, and they may be described with attributes such as left and right (and perhaps some are produced as pairs or other combinations). Each product is of a type and identifiable by ProductID. This could be handled through a self-referential relationship (attribute PairedID or ComponentID holding a Product ID for the Parent Product). Avoid making specific tables for different products, use product type to describe the details in a generic form to accommodate change most easily.
Material-Product Allocation - Linking sheet used (Material ID, Product ID) - for one material id 1 or more product IDs may be associated) . This represents what material was used to produce what particular products. (It is not a reference table of what product types may be produced from a type of material). It resolves the m:n relationship between materials and products. Be wary of how you populate this table when you have Products that are Parents for child Products - participate in self-referential relationships.
Your quantity on hand of materials will be based on Material Type not material. Same for your Product Inventory. Both inventories are handled by looking at transactions - records of the amount consumed or produced (and purchased/dispatched for material and product. Your consumption and production are reflected in the Material-Product Allocation - counting of unique Material Type associated with each Material ID with a time period, or ProductID/ProductType. (Your inventory is not based off for eg a Material ID as these are unique to each item - so there is only a quantity of 1)
Build your Material Type and Product Type tables off the Material and Product tables respectively. These will reflect the attributes of those items to the level that enables you to describe any item as necessary for your inventory management.

Hi Gap,

I figured as much, still learning how to create these relationships lol.

- Material: Each Item will be consumed individually and tied to the Material ID. Descriptors that differentiate them are the Color, Size, and Material Type which defines things like grain and what kind of plastic the sheet is. They aren't defined by a stock type, but the Material allocation is what's meant to define the stock type (allocated, or just basic stock) down the road once I have the Part relationship figured out.

- Product: So my Alternate Id is the attempt to make a junction table that connects product 1 to product 2 as one entity from the view of the material sheet. Product 1 and Product 2 would be the left/right combo, and AltID would be the AltID (PairedID). From my understanding, are you recommending to add something like a tool/product type to each product on the product information table to differentiate the products (Left, Right, Center--only 1 part to one sheet) instead of using the ALtID table to make that difference?

-Material-Product Allocation: So this table should link Material ID to Product ID, and define the Material type? Right now I have the quantities of the material within the Material Information pages, an example is material ID 1 = 100 quantity. Should this be recorded in the Allocation table instead and then have a separate table for allocation transactions?

It seems the biggest issue for me is creating a self-referential relationship or junction table that works. Do you have any recommendations on how to go about these in a clean way, specifically for the Product left/right split?

Thanks,
J

 

[GaP42]

Hi Jman

Thanks for your reply - raises more questions - as it always does.
1. You were not explicit about whether material received is identified uniquely and can be tracked individually -- eg a productID often is used to identify the product type, whereas the serial number on the unit of production identifies the instance.
2. Stock type is not defined, but Material Allocation (as allocated or just basic) - does not seem to be a stock type. Separate concepts/ details about the type of stock (std, special defined by sheet/colour/material ..) and the status of the stock item (allocated, unassigned)
3. Products: You produce one or more products from a "sheet" (material). Where 2 products are produced form a sheet, you indicated that (often L and R versions are made. A Product table will have records (PID) for both L (PID = 1001) and R (PID 1002), however if a single "parent" product record is created as a Product (PID = 2000) - describing the product as the L and R units, AND the records for the Left and Right units have the parent productID recorded in their record as the PPID (Parent Product ID = 2000) then these records are linked and a query involving PPID can show that the Product PID = 2000, is composed of PID 1001 and 1002. This will also enable you to combine any products that may be produced from your material to be combined as a standard combination that may be produced from a single sheet (unit of material). It can also allow you to have subcomponents - but I don't that you need to go further than one level.
4. Material - Product Allocation: The information here leads me to reflect back on point 1 above. Material ID = 1 - Quantity 100 - the materials (sheets are recognised as a type and not individually as sheets. Material Information Papers - is this another table of the Material table - I see it has material qty. This is how you keep track of the levels of stock of any particular material type. It is a materialType table. Your use of the material will be in the combination of Material and Product : the material - product allocation: When you create the association (Materialtype 1 and Product PID), you can record the amount of product to be produced. This can be PID = 500 (1 unit of product/ sheet) Qty 10 -, so 10 sheets of MaterialType 1 consumed. PID 2000 (1 unit of product consisting of 2 components (Left and Right 1001, 1002) and 100 units - so 100 sheets used).
Hope you can see how you can now use this to determine your level of stock and the level of production.
5. For tracking inventory see advice from experts: eg Allen Browne - Inventory Control: Quantity on Hand

 

[Cotswold]

Jman95, I think your starting point is a book on database design.

Also look at the Access World forums below in particular "Theory and Practice of Database Design" and "Access Tutorial" as well as numerous posts similar to yours. You can be assured that over the last 30 years the application you are developing will have been created many times by different developers. By now there is little that is original in database applications.

Look at the Templates in your Access and the new Northwind for ideas. There is also other information on the internet. In practical terms I don't think that you can expect the forum to actually design your application.

 

[LarryE]

A few questions if you don't mind:

1. What is a "sheet"?
2. Do you have customers who issue purchase orders or how are orders taken?
3. Do you have a purchasing function that issues purchase orders to vendors and are these items used in the manufacturing process?
4. What does the company manufacture and sell?
5. Is it your desire to track materials through the various stages of manufature?

The design of tables and fields and their relationship to one another always follows the operations workflow. So when you are thinking about what tables to create and what fileds to include, start at the beginning of the workflow process and follow it. For example, in your case (and I am just guessing here trying to understand what you have said):

1. You have vendors that fill Purchase Orders from your company, so you will need a vendor table
2. The PO items are added to your parts inventory, so you will need a VendorPO or Purchases table
3. Each part type, color, size etc. may be used in multiple material builds (is this a "sheet"?), so you will need a Parts Inventory table
4. Each material build uses one or more tools and results in a finished inventory product for sale, so you will need a Materials table and a Finished Inventory table.

Is this even close?

 

[Jman95]

Hi Jman

Thanks for your reply - raises more questions - as it always does.
1. You were not explicit about whether material received is identified uniquely and can be tracked individually -- eg a productID often is used to identify the product type, whereas the serial number on the unit of production identifies the instance.
2. Stock type is not defined, but Material Allocation (as allocated or just basic) - does not seem to be a stock type. Separate concepts/ details about the type of stock (std, special defined by sheet/colour/material ..) and the status of the stock item (allocated, unassigned)
3. Products: You produce one or more products from a "sheet" (material). Where 2 products are produced form a sheet, you indicated that (often L and R versions are made. A Product table will have records (PID) for both L (PID = 1001) and R (PID 1002), however if a single "parent" product record is created as a Product (PID = 2000) - describing the product as the L and R units, AND the records for the Left and Right units have the parent productID recorded in their record as the PPID (Parent Product ID = 2000) then these records are linked and a query involving PPID can show that the Product PID = 2000, is composed of PID 1001 and 1002. This will also enable you to combine any products that may be produced from your material to be combined as a standard combination that may be produced from a single sheet (unit of material). It can also allow you to have subcomponents - but I don't that you need to go further than one level.
4. Material - Product Allocation: The information here leads me to reflect back on point 1 above. Material ID = 1 - Quantity 100 - the materials (sheets are recognised as a type and not individually as sheets. Material Information Papers - is this another table of the Material table - I see it has material qty. This is how you keep track of the levels of stock of any particular material type. It is a materialType table. Your use of the material will be in the combination of Material and Product : the material - product allocation: When you create the association (Materialtype 1 and Product PID), you can record the amount of product to be produced. This can be PID = 500 (1 unit of product/ sheet) Qty 10 -, so 10 sheets of MaterialType 1 consumed. PID 2000 (1 unit of product consisting of 2 components (Left and Right 1001, 1002) and 100 units - so 100 sheets used).
Hope you can see how you can now use this to determine your level of stock and the level of production.
5. For tracking inventory see advice from experts:

Hi GaP,

1. The material ID in my table is the individual combinations of size, type, and color that we order for our products and what uniquely differentiates the material from each other. It has a one to many relationship with our products since once size and color is used for a variety of different part numbers. We do received them in from the supplier as a Lot# which would tie the unique instance of receiving that product if that would be a better criteria to define them. Once received, we allocate these parts to a certain job based off of open orders.

2. Thank you for clarifying. The size, color and material type is defined in my material information table. Would it be better to separate those out into a separate table as a Stock Type table?

3. That is useful information I'll try that out. You are correct, I'm not building out a full BOM for subcomponents. We do have some assembly jobs like that, but these are only a few parts so we can handle the inventory level of those components without having it in the database.

Thank you for this information, it gives me a lot to work with and try out. I'll let you know how this goes.

 

[Jman95]

Jman95, I think your starting point is a book on database design.

Also look at the Access World forums below in particular "Theory and Practice of Database Design" and "Access Tutorial" as well as numerous posts similar to yours. You can be assured that over the last 30 years the application you are developing will have been created many times by different developers. By now there is little that is original in database applications.

Look at the Templates in your Access and the new Northwind for ideas. There is also other information on the internet. In practical terms I don't think that you can expect the forum to actually design your application.

Hi Cotsworld,

Thank you for the recommendations to learn more, I'll check these out.

My intention is not to have the forum actually design my application. I'm the only one in my company that has experience with information systems but I haven't touched it much in over 5 years, so I'm hoping for a resource on any questions from experts on the forum and advice for best practices. (or even if I'm on the right track)

I have been using the Northwind template for a lot of ideas, I just couldn't find a solution for this specifically since the products in the template seem to have a 1-1 relationship when building out the final product.

 

[Jman95]

A few questions if you don't mind:

1. What is a "sheet"?
2. Do you have customers who issue purchase orders or how are orders taken?
3. Do you have a purchasing function that issues purchase orders to vendors and are these items used in the manufacturing process?
4. What does the company manufacture and sell?
5. Is it your desire to track materials through the various stages of manufature?

The design of tables and fields and their relationship to one another always follows the operations workflow. So when you are thinking about what tables to create and what fileds to include, start at the beginning of the workflow process and follow it. For example, in your case (and I am just guessing here trying to understand what you have said):

1. You have vendors that fill Purchase Orders from your company, so you will need a vendor table
2. The PO items are added to your parts inventory, so you will need a VendorPO or Purchases table
3. Each part type, color, size etc. may be used in multiple material builds (is this a "sheet"?), so you will need a Parts Inventory table
4. Each material build uses one or more tools and results in a finished inventory product for sale, so you will need a Materials table and a Finished Inventory table.

Is this even close?

Hi Larry,

Not a problem!
1) We are a thermoforming company, so 1 "sheet" is a plastic sheet that we put into a production tool to form our parts. 1 sheet could produce a variety of part combinations based off of how the tool is designed.

2) Orders are taken based off of a Purchase Order from our customer. From my understanding, we receive the PO for the parts and when they want them. Then, we order our material based off of what we owe. Example: they want 180 parts of Product A by 12/1/2023. Right now, we have 80 sheet in stock, so we would have to order 100 sheet of material to fulfill the order.

3) Yes we do. We have QuickBooks for all of the accounting and vendor/customer management. The problem we are facing is this information is isolated to only 1 user (our office manager) and I want to build out a custom database to provide our management team on the floor with all of the related information they need.

4) Mainly, we manufacture interior and external parts for vehicles. There is three fazes of a typical part lifespan.
1. Raw sheet
2. Formed part or W.I.P (part is formed and ready to run through the CNC and be processed)
3. Finished Good (ready to ship)
5) Eventually, yes. I'm not including WIP into the original development of the database due to the complexity of how it is processed at the moment. So right now I'm only focusing on the first and last stage of the cycle.

You are right on the money. Where I'm at so far:

- PO Information table that lists the PO#, Part Ordered, Quantity, Due Date. Eventually, I'd like to pull this information from our Quickbooks server.

- Material Information table that includes the different combination we order for our tools, defined by a material ID.

- Part Information Table intending to tie the Material ID to our parts that are ordered from the PO.

- Finished Goods Inventory that is our current inventory of stock ready to ship out. This is just a Quantity In and Quantity Out table, and I'll make the calculations within the Query for Ending Inventory Balance. These are split up to make form submissions easier to process (ex. We shipped something out, the form would add to Quantity Out. We finished producing the parts, add to Quantity In)

I think the main issue I think I'm having is the logic of communication to set these up and how they all relate to each other. I think the problem lies in the junction table I created for an Alternate ID.


I'm going to try out what Cots and Gap recommended as well to keep picking at this. Any other recommendations are always appreciated.

 

[LarryE]

I think you ar making it much more complicated than it needs to be and you should design and build your material status table from the beginning because it is related to your materials table and your finished goods inventory. So you can follow the progress of each build from Raw sheet to formed part to finished product.

This is what I was thinking:

This assumes customer orders are made to order and then shipped. If you receive orders that simply ship out of Finished Inventory without having to be manufactured first, then the relationship between TblSalesOrders and TblJobMaterial goes away and a new relationship needs to be formed between TblSalesOrders and TblFinishedInventory using the OrderID in the Sales Order table as a Foreign key in the Finished Inventory table. I was unclear as to whether each customer is manufactured each time or not.

 

[Jman95]

I think you ar making it much more complicated than it needs to be and you should design and build your material status table from the beginning because it is related to your materials table and your finished goods inventory. So you can follow the progress of each build from Raw sheet to formed part to finished product.

This is what I was thinking:
View attachment 108334
This assumes customer orders are made to order and then shipped. If you receive orders that simply ship out of Finished Inventory without having to be manufactured first, then the relationship between TblSalesOrders and TblJobMaterial goes away and a new relationship needs to be formed between TblSalesOrders and TblFinishedInventory using the OrderID in the Sales Order table as a Foreign key in the Finished Inventory table. I was unclear as to whether each customer is manufactured each time or not.

To better understand, here's how I'm reading these tables. Let me know if I'm missing the mark:

- TblVendorPO: The transaction of material from vendor.
- TblPartsInv: This will be where the inventory for the vendor purchased part would reside, tied to a PartID. Which I believe would be similar to my Material Information table?
-TblJobMaterial: Assigns what is owed off of the PO, and Material purchased to a "job" which would have the JobMaterialID
-TblMaterial Status: Defines where it is at in process, like Allocated, Stock, or Finished
-TblFinishedInventory: Basically a finished goods inventory, calculating the amount finished to report quantity in and out

 

[LarryE]

To better understand, here's how I'm reading these tables. Let me know if I'm missing the mark:

- TblVendorPO: The transaction of material from vendor.
- TblPartsInv: This will be where the inventory for the vendor purchased part would reside, tied to a PartID. Which I believe would be similar to my Material Information table?
-TblJobMaterial: Assigns what is owed off of the PO, and Material purchased to a "job" which would have the JobMaterialID
-TblMaterial Status: Defines where it is at in process, like Allocated, Stock, or Finished
-TblFinishedInventory: Basically a finished goods inventory, calculating the amount finished to report quantity in and out

Yes.

1. Purchases increase the parts inventory table
2. Parts inventory items are used in the manufacturing process in the JobMaterials table (decreasing Parts Inventory)
3. Each vehicle part manufactured goes through the 3 phases
4. And are entered into Finished inventory

BUT if customers orders are shipped directly out of finished inventory instead of being manufactured each time, then the design looks like this:

With a relationship between Sales Orders and Finished Inventory directly because you already have the ordered parts in finished inventory. As I said, I was not sure if each customer order is manufactured to order or if you ship out of finished inventory if you have it in stock already.

 

[LarryE]

Two questions:

1. Does each customer order need to be manufactured from scratch or do you check current finished inventory levels and ship directly from stock?
2. Do you wish to know how much time (hours and minutes) it takes to make the part once it enters the manufaturing process?

 

[CarlettoFed]

To actually understand how to handle it you should show the one seen in post #11 for a single order so you can see the progress of the process starting from the order to the sale.
This is because it seems that there are some inconsistencies to see
in the Material Information table->Material Type->ABS 2055MT
in the Part Information Table->Material Type->Allen 2055MT
which would not allow the Materials to be correctly related to the Order to be processed.
Is the Part Information Table provided by the Customer issuing the Order?

 

[Jman95]

Two questions:

1. Does each customer order need to be manufactured from scratch or do you check current finished inventory levels and ship directly from stock?
2. Do you wish to know how much time (hours and minutes) it takes to make the part once it enters the manufaturing process?

1. The process starts with a PO for what the customer needs that can go up to a year from now. After that, their purchasing department sends us "releases" on what parts they want 1 month/2 months from now. To answer your question, when we get that release we look at finished inventory first, then form if needed.
- Lets say that the customer wants 100 parts this month, but the PO has 1,000 parts on order. We would have 1,000 sheet on hand to make that order, but we would probably use around 200 sheet for that order to produce 200 parts total which would cover us for this order, and the next.

2. That is one thing I'll be adding in once I have the basics down pat. There is no official, documented time on how long it takes for each part and I'll be adding that in to help with production planning down the road. Scheduling is a whole other animal to tackle

 

[Jman95]

To actually understand how to handle it you should show the one seen in post #11 for a single order so you can see the progress of the process starting from the order to the sale.
This is because it seems that there are some inconsistencies to see
in the Material Information table->Material Type->ABS 2055MT
in the Part Information Table->Material Type->Allen 2055MT
which would not allow the Materials to be correctly related to the Order to be processed.
Is the Part Information Table provided by the Customer issuing the Order?

Hi Carletto,

While going through my data yesterday I did see those discrepancies you mentioned. I'm using excel data from our office managers to grab all of the relevant data, but they didn't have a clean way of processing it so I'm going to start from scratch and normalize their naming conventions.

The Part Information table is the connection of our part numbers to sheet, the intention for this table is to be the master connection to have every individual part number tied to a specific sheet (Mat ID) and then I'm attempting to make a junction table to include a Parent Part (PPID) to the Left/Right combo of our tools when processing these parts.

In the example I provided, I did not have my PO Information Table which would be the Customer issuing the Order. The PO Information and Finished Goods Inventory connection is working great, this is just a sandbox database to work out the kinks from the material side of the process

 

[CarlettoFed]

What you should see is the data in the mode exposed in post #11 but concerning a single Order with all the other data linked to it in order to see a real situation from start to finish and therefore be able to understand better.

 

[GaP42]

Jman, we don't know what your sandbox database design looks like. I hope you may be able to share it with us - if only to ensure any further advice allows us to use the terms you are adopting. As CarlettoFed suggested, step through your model structure with a sample customer order, vendor purchase order, or other entry point to verify and check its capacity to manage the workflow needed. eg for a vendor purchase order (using the LarryE sample structure), do these purchase orders have line items? The filling of such a purchase order would mean the "Parts" inventory would relate to the purchase order line items rather than the purchase order itself.