3 Replies Latest reply on Aug 19, 2015 9:43 AM by SteveMartino

    Table Design question

    mblogsd

      Title

      Table Design question

      Post

      While I have some experience with databases, I'm having a bit of trouble wrapping my head around the best way to approach this portion of a solution I'm working on.  I'm working on a solution related to wastewater operations. The part I'm currently working on is for sampling.  There are a number of different sample parameters that can be required for each location, and each are usually different requirements.  Each parameter normally has different frequency requirements as well.  My question is, would it work best to have a table of all possible parameters with the ability to select frequency as needed, and then a separate table for results, or would it work better to have a separate table for each parameter, the ability to select frequency, and separate table for each parameter's results?  While I believe either approach would ultimately work, I'm looking for some input from the pros out there for reasons one way or the other.  In my mind, having a separate table for each parameter seems like it would be more flexible, but worry there could be a reason I'm overlooking for not doing it this way.  I can fill in any information gaps that are needed for help coming to a decision.  Thanks in advance as I appreciate any input as I just can't seem to come up with a reason to go one way or the other.

        • 1. Re: Table Design question
          SteveMartino

          I don't quite understand your terms, but I think I would set it up kind of like an invoice solution
          Customer---->Invoice--->LineItems<-------Products

          Location---->Sample--->SampleParameters<--------Parameters

          Then in it's simplest form, you would have a portal on the sample record for SampleParameters, where you can select the SampleParameters by calculation or lookup.  The parameters table would be a list of every type of parameter.  If certain locations always require the same SampleParameters, you could auto populate them by script.

          You would also have a dropdown based on locations, to select the location

          • 2. Re: Table Design question
            mblogsd

            Thanks, Steve.  I honestly hadn't thought to approach it that way having a separate Sample table like that and then having the join table.  Just so my head is straight on this, the Sample table would then have all samples taken for that instance in one record and be associated by the sample number or id, correct?  Never mind, I see where I looked at that incorrectly now.  I'm going to go check out an invoice solution to get a visual as well.  Thanks for the suggestion.

            • 3. Re: Table Design question
              SteveMartino

              Yes one Sample record would show like an invoice.  One sample record would be an instance (record) of one sample taken, at a specific location, at a specific moment in time,  with the sample parameters (lineItems) in a portal.  Those sample parameters would be looked up/added by calculation/script,  from the Parameters table.