3 Replies Latest reply on Apr 14, 2014 3:18 PM by philmodjunk

    Establishing the right ER Diagram for Unique Projects

    johnfc1267@gmail.com

      Title

      Establishing the right ER Diagram for Unique Projects

      Post

      Good Afternoon:

      I am relatively new to Filemaker and I am having difficulty trying to figure out how to set up my tables as it pertains to the unique projects that I perform in the print brokerage space.   As a print broker, I have nearly 65 categories of print that I produce for my clients.  Each category maintains many attributes (specifications) that are unique to itself.  For instance, quoting envelopes is completely different than quoting a catalog that mails.  Each one has specific specifications that the customer would like to see on the quotation along with the pricing broken out.   

      Once the Quotation is accepted by the client, then I need to produce Purchase Orders for the supplier(s), and then invoice the projects.

      I am trying to enter all of this into filemaker to capture the information and make it a one button data entry for my workload.  Where I am having difficulty is in my ER Diagram.    Since I have such unique projects with so many attributes / specifications how do I break them out without creating too many tables?

      Do i perform the following?

      Customers ---<Invoices----Line ITems----Pricing---Envelopes, Catalogs, DirectMail, Posters, Postcards (all different tables?)

      Any inputs would be great.

       

      Thanks,

      JFC

        • 1. Re: NoFields
          philmodjunk

               There are two basic approaches, both do the job.

               Option 1:

               Define your print jobs in a single table. Define a large number of fields for each record and only use the fields relevant to that category, leaving the fields not relevant to that category blank. This is often easiest for someone new to database design to set up but it does have a large number of unused fields in every record.

               Option 2:

               Set up a single table where you have one record for each type of print job as in Option 1. But only include those fields common to all print jobs. The define "detail records" in tables designed for the different types of print jobs. This breaks up that one table with a very large number of fields into a series of related tables with a smaller set of fields defined in each. But your relationship map in Manage | Database | relationships is now more complex as well.

               In both cases, you have a single table where you have one record for each print job of all categories. This is important for invoicing and reporting purposes. The difference is in how you manage recording the details for each print job category "under the hood".

               To the user, you can still setup different layouts customized for each print job category when implementing either option 1 or 2 so to them, there will be no obvious difference either way.

          • 2. Re: NoFields
            johnfc1267@gmail.com

                 I think Option 2 makes more sense.   I have created a database like option one and you're correct about the vast number of empty fields.

                 Regarding, Option 2, are you saying:

                 Have a Detail Records Table (portray common fields in here) and then additional tables specific to the projects?  For Example, Envelopes will have a field specific to itself like:  Flap Type, Envelope Type, Envelope Tint, Envelope Tint Color, etc.   Regarding Catalogs,  specific files maybe:  Paper Tonnage, Paper Roll Size, and Apparel will have sizes, youth or Adult, etc?  In essence, one table with all of the common specifications and others with their specific attributes to avert a massive number of blank fields.  Is this correct?

                 .... Pricing>----Join Table---<Details Table / Envelopes Table / Catalogs Table / Direct Mail Table / Apparel Table / Promotional Table / Stationery Table / Collateral Table /  (in essence, all these tables keys connected to the Join Table to make various layouts?

                 Thanks for your input,

                 JFC

                  

                  

                  

            • 3. Re: NoFields
              philmodjunk

                   Yes, you would use a different related table for each general type of job or group of similar jobs. This is a one to one relationship.

                   The data that you might collect in such tables might be used in two different ways:

                   1) to compute the cost to be charged to the customer if these details you are collecting affect cost

                   2) to document exactly how the print job needs to be set up and run--a production specification.

                   Whether you need to accomplish 1, 2 or both with these tables will affect how this links into your invoicing part of the system.