5 Replies Latest reply on Jul 18, 2016 6:54 AM by Magnus Fransson

    choosing an appropriate DB structure

    jabs.far

      Hello all,

       

      I’m wondering what would be the best database strategy to apply to a spectral analysis project that I’m pursuing.

       

      For a number of fluorochromes (around 50) I have measurement data over a range of wavelengths, corresponding to their fluorescence intensity. Wavelengths run from 300 to 900 (nm), in increments of 1 (integer). Fluorescence intensities run from 0 to 1 (real).

       

      I have the same kind of measurement values for a number of optical filters (around 50 also) that we use for measuring fluorescence intensities. Over the same range of wavelengths (300 to 900 nm), I have values for filter transmission running from 0 to 1.

       

      I’d like to be able to multiply values of fluorescence intensity by filter transmission values at each given wavelength, and sum over the entire wavelength scale. In other words, multiply one column of data by another column of data in a line-by-line fashion ...

       

      As an extra, it would be nice to be able to do some graphing of curves (wavelength versus fluorescence intensity), but that could also be achieved after exporting to more appropriate graphing software.

       

      For the moment, I’m using an Excel spreadsheet that contains all the data, but I’m not happy with it’s performance. Especially at the level of user interaction, when choosing what filters and what fluorochromes to add to the equation, it’s tedious.

       

      Would someone have a suggestion of how to wrap this kind of a problem in a database structure that would be able to efficiently deal with this ?! Should I use repeating fields in a simple table, that I could “self-join” based on a user-selected filter-value. I’m not sure if that would be a smart approach …

       

      Perhaps my explanation needs more detail. If so, excuse me for not being clear, and please let me know.

       

      Amicalement yours,

      Jan

        • 1. Re: choosing an appropriate DB structure
          coherentkris

          Understanding your data is crucial to making design decisions.

          Database normalization - Wikipedia, the free encyclopedia

          • 2. Re: choosing an appropriate DB structure
            Magnus Fransson

            Hi Jan,

             

            Let me start by saying that, database developers and scientists comes from different part of the universe and speaks different languages. Thus we must be patient with each other’s inability to understand what we intend to say.

             

            Though I'm not sure on you intention, I give you my best interpretation what you have said so far. That way you get a sample of my language, and can hopefully better express your intent, at next attempt.

             

            I interpret what you write as:

            You have one table with measurements made on different "fluorochromes" (I have no idea what that is), over a number of different wavelengths, using different filters. Which could look something like this:

            Measurement 1, Fluorochrom, Filter, Wavelength, Value 
            Measurement 2, Fluorochrom, Filter, Wavelength, Value 
            Measurement 3, Fluorochrom, Filter, Wavelength, Value 
            Measurement 4, Fluorochrom, Filter, Wavelength, Value
            

             

            Then you have another table with "Filter correction data" for different filters over a number of different wavelengths. Which could look something like this:

            #1, Filter, Wavelength, Value 
            #2, Filter, Wavelength, Value 
            #3, Filter, Wavelength, Value 
            #4, Filter, Wavelength, Value
            

             

            What you like to do is to combine each measurement with corresponding filter correction, and then calculate the corrected value, by multiplying the measurement value with filter value, value by value.

             

            If I'm right so fare, you should crate those two tables in FileMaker and then create a relation between them using both "filter" and "wavelength". After that there are a couple different ways to go to create a calculation in the "measurements table".

             

            With best regards Magnus Fransson.

            • 3. Re: choosing an appropriate DB structure
              wimdecorte

              jabs.far wrote:

               

              Should I use repeating fields in a simple table, that I could “self-join” based on a user-selected filter-value.

               

               

              Adamantly NO!

              use proper tables for each entity.  To figure out what entities you have, start by listing the nouns in your narrative...

              • 4. Re: choosing an appropriate DB structure
                jabs.far

                Hello Magnus,

                Thank you very much for having taken time to try to understand ...

                As ever so often, I guess my problem was presented in an overly complicated way. But first of all, to take away some of the magic, a "fluorochrome" is a fluoresceing substance, that emits light upon excitation. Take a glass of Tonic water, expose it to blacklight, and it will fluoresce blue, due to the Quinine it contains .

                I could have omitted talking about fluors and filters. Basically, I have a rather large Excel data sheet (100+ columns), and I would like to multiply values in one column by values in another column, row by row (C1=A1xB1;C2=A2xB2; ... etc.), and then SUM the result column (C1+C2+ ..., etc.). There are approximately 100 columns, and each column has between 500 and 1,000 values.Screen Shot 2016-07-18 at 13.52.56.png

                My feeling now is that I should perhaps generate a single Table that contains 1,000 records, one for each row (wavelength) in my data sheet. It should have fields for each of the columns of my data sheet. That would make for some 100 Fields. Self-joining this table using user-entry filter values would create the possibility to do the necessary calculations.

                I'll work on this a bit and come back with my impressions ...

                Thank you again,

                Jan

                • 5. Re: choosing an appropriate DB structure
                  Magnus Fransson

                  Hi Jan,

                   

                  As soon as someone starts a database normalization discussion talking about "self-joins" I get uncomfortable. To me, self-joins are something that comes in much later.

                  My experience is that persons without database experience, using Excel, often fools them self to believe that they should do every thing in one table. But they are seldom right. (Though it happens.)

                   

                  Still being unsure, I believe your data should be divided in to three tables.

                  • The first one, should loosely correspond to your "Rows" and contain about 1000 records, but only a handful fields (including a primary key field).

                  • The second table, should loosely correspond to your "columns" and contain about 100 records, but only a handful fields (including a primary key field).

                  • Then there should be a "join-table" that should contain: one primary key field, two foreign key fields (for joining the two previous tables) and a measurement/value field. And about 100 000 records.

                   

                  This might not be correct, but should hopefully start you thinking about your data in a new, constructive way.

                   

                  With best regard Magnus Fransson.