4 Replies Latest reply on Jun 23, 2010 4:44 PM by Rgulotty

    Database design for Time Series



      Database design for Time Series


      I am new to this and I  have a design question.  I have a flat yearly dataset on prices of many products that I would like to turn into a relational database, but many products undergo changes in how they are categorized.  


      Lets say apple prices are listed in 1928, but then in 1935, red apples and yellow apple prices are listed.  In 1940, yellow apples are again split into large yellow apples and small yellow apples, so in 1940, we have red apples, small yellow apples, and large yellow apples.  


      I would like a relational database where I can look up "red apples" for 1928, and get the rate for "apples" in 1928, and "small yellow apples" for 1935 and get the rate for "yellow apples"



      I am thinking something like the following would work.



      Table 1:  Product ID, Product Features.


      Table 2:  Yearly price data


      Table 3:  Table of product "categories"


      But I don't know how to relate these tables or solve the problem that some products have prices for some years and are themselves categories for other products.


      Thanks for your help.







        • 1. Re: Database design for Time Series

          The phrase garbage in garbage out comes to mind here. SInce the raw data is inconsistently labeled any attempt to use the text in them to relate records will be problematic.


          Somehow your going to have to find a way to identify which word in the text field is sufficient to serve as a key for your relationship. (How will filemaker "know" that "Apple" is significant and Small, red and yellow are not?)


          I'm thinking of a keyword list based relationship here where each field computes a list of keywords which match to a text field where you the user select or enter a keyword to match.


          Define a field, cKeyList as Substitute ( TextField ; " " ; ¶ ) where text field is the field where you find text like "red apples".


          Now you can set up a relationship as Categories::Category = Products::cKeyList


          A Category record with "Apple" in the Category field will then match to all products that contain "Apple" in this field.


          Alternatively, you may want to assign category names or ID numbers to your records by performing a find for "apples" using Replace Field contents to assign a common value to identify the category, perform a find for "Peaches" and assign a different value... etc. until all records have an assigned category.


          You might be able to do this with a script that loops through your category records, using the names as criteria in find requests performed on your product table.

          • 2. Re: Database design for Time Series

            What you are describing is known as "recursive hierarchy". There's a good introduction to the topic here:


            • 3. Re: Database design for Time Series

              I really appreciate the help.  I think finding the key would not be so difficult, as I have a code that is appended each time there is a split, the code starts 1.1, and then when there is a split, it becomes 1.1.1 and 1.1.2, so I can parse the text to back out the relationship.

              • 4. Re: Database design for Time Series

                This is a great introduction to recursive data structures, and I think will be very helpful.