3 Replies Latest reply on Jan 29, 2014 6:26 AM by Penilorac

    New Database... not sure of the tables




      I am creating a new database which will handle invoicing and production at the same time. I created it but am not sure about the tables I used.


      I first created 4 tables:







      But now I wanted to add the production part.


      A bit of background...


      This is for a printing company. The products will be in fact images to print so almost each products will be different than the others. Once the invoice is paid, we want the order to go on the production side and from then, there are a couple of departements which all work on the same images , at different stages. The products should stay by order but we will also need to be able to make groups of image per material or size, or other options.


      I created another table named "production" which is related to the "Invoice" table , by the "invoice number field" and all the production layouts are from the "production" table since they all work on the same files. The departements are printing, stretching and finishing. I created different layouts for each departements, but all from the "production" table.


      Is this ok or should I have created 1 table per production departement and if so, by what would they be related?


      Thank you very much in advance!

        • 1. Re: New Database... not sure of the tables

          It depends. Data modeling is an imperfect science.

          Sounds like your project can get pretty complex quickly.


          I think that the data arrangement that you specified sounds reasonable. Instead of an entity called "Production", I might name it "Jobs". The job would go through specified stages of production...printing, stretching, and finishing. This can be specified by a Status or Job Stage field in the Jobs table. Sounds like each job might relate to a line item on an invoice (one particular image)? I might create a script that creates multiple jobs from an invoice.


          Hope that helps.


          1 of 1 people found this helpful
          • 2. Re: New Database... not sure of the tables

            I have developed an ERP system for my printing company. Attached is a high level chart of it's basic workflow. While it sounds like your printing companies structure is considerably different than ours ( I sure would like to get paid before production begins), you may find it useful.


            Below is how it somewhat corrosponds to your sytructure,


            Company Items = Products

            Invoices  = Invoices

            Accounts = Customers

            All major modules (Sales Orders, Work Orders, Shipments, Invoices, etc.) have Line Items.



            • 3. Re: New Database... not sure of the tables

              Thank you for your input. I think this could probably work. When I did it the first time (still a beginniner), I forgot the lineitem table and got problems later. Now I am trying to fix it but am a bit lost in the process. The majority of the peoples selling products will sell non custom items.


              In this case, everything about the item is custom (image from the client, material, completely custom size, other options, etc). And the prices changes by quantity of each material on the same order. For example, if I order 5 differents images x 10 copies each on the same material, the price quantity of 50+ will be applied on all prints. So I am a bit lost on which info to put in the product table and which info in the lineitem table.


              I would like to work in this order. The client is selected ----> new invoice ----> add the first file, which will each time create a new product and will after get added on the invoice -----> add the second file and so on.


              Thank you again!