Food Traceability Database Help!!!

[swmalone]

I am bumbling through an attempt to use base to put together a food traceability database for a company I am working for. The problem is I really have no idea what I'm doing.

I have started by making two tables. The first is for incoming ingredients and packaging. It includes the following fields: Date Received, Supplier, Item Name, Item Number, Item Lot Code, and Amount Received.

The second table is for finished product and includes the following fields: Production Date, Customer, Finished Good Name, Finished Good Item Number, Quantity Produced, Ingredient or Packaging Item Name, Ingredient or Packaging Item Number, Ingredient or Packaging Lot Code, Ingredient or Packaging Amount Used, Ingredient or Packaging Amount Waste, Ingredient or Packaging Amount Returned

What I am working on is figuring out a way to perform forwards and backwards traceability. So for example if a customer complains about a specific finished product with a given item number and lot code we can quickly identify all of the ingredients and packaging materials used in that finished product. Or if we are contacted by a supplier saying they found an issue in an ingredient or packaging material that we can then determine all of the finished products that the item was used in as well as determine how much went to waste, returned and is still stored in the warehouse.

I know this may be a bit much for an introductory e-mail but if anyone can point me to some tutorials or website that can help me figure this out I would greatly appreciate it.

Thank you in advance.

[Villeroy]

Find a database developer and pay him well.

[swmalone]

In the long run an inventory management system will be put into place that should automate most of this, but that is at minimum several months out and we need something in place in the next several weeks that can bridge the gap.

[UnklDonald418]

a way to perform forwards and backwards traceability. So for example if a customer complains about a specific finished product with a given item number and lot code we can quickly identify all of the ingredients and packaging materials used in that finished product.

This is a classic example of a problem that can be solved with a database. I hate to discourage you but this is not a beginners project, it is an advanced problem that requires a solid understanding of relational database design. Unfortunately you're probably not going to find that in a simple tutorial.

There is a tutorial on database design that can be downloaded from:
https://wiki.documentfoundation.org/ima ... torial.pdf
This tutorial uses a case study to give a good overview of the database design process. But you won't find much there that you can copy and paste into your database.

The tables you describe are more suited to a spreadsheet, than a database.
For instance it sounds like you have a product made from one or more ingredients. This requires what is known as a one to many (1 to n) relationship, so you need a table for the product and another table that has a row for each ingredient, and probably the lot number plus a foreign key linking it back to the product ID and date of mfg. But wait! Date of mfg. implies that there is more that one possible product that can contain these ingredients, so now we have a many to many relationship (n to n) which requires another intermediate table to link products to ingredients. I suspect to get this working you may need half a dozen tables, and they won't look much like what you have proposed.

[swmalone]

Thanks for the explanation UnklDonald. I didn't realize it would be that involved. I have created the tables using spreadsheets and I think that we may just have to use those until I become much more educated in database management or they decide to go with the inventory management system that Fishbowl offers. For now even the spreadsheets are better than what they have now which is digging through filing cabinets for hard copies of records.

Thanks again.

[eremmel]

In the long run an inventory management system will be put into place that should automate most of this, but that is at minimum several months out and we need something in place in the next several weeks that can bridge the gap.

Is a inventory mgmt sys enough? You might need an ERP for that with feedback from production.

I hope that you do not receive that many complains. When it happens once a week or less, you might just reconstruct the data with help of Calc and utilize filters on the right columns as a labor work.

In many cases I notice that the effort of getting something useful is more than time spend with applying a workaround, esp when the effort involves a learning curve. Maybe you can find an IT / Computer Science student that wants to do it for a few $100,-

[swmalone]

They don't have many complaints so I'm thinking of using the spreadsheets with filters until they decide to move to the Inv. Management System or ERP. I haven't had a chance to look into what exactly it is that Fishbowl offers. Finding a computer science student is a good idea and very doable. They are located in an area with a high number of universities and people trying to get into local tech jobs.