3 Replies Latest reply on Feb 15, 2011 7:15 AM by WilliamdelaMaza

    Do I really need table redundancies in FMP?

    WilliamdelaMaza

      Title

      Do I really need table redundancies in FMP?

      Post

      I need to develop a database for a bookstore that will include the following tables: 1. A book catalog 2. Publishers/ distributors catalog 3. A module to manage book purchases/ consigned items 4. Invoices 5. Inventory I'm not an expert developing databases in general or FMP in particular, but I've been working with FM for a while now and I feel pretty confident on the reliability and ease-of-use of it. However, a few days ago, I meet a developer that uses MS Access to do its work. He previously did some kind of analysis over the solution outlined above and then made an Excel file listing all the fields and tables that the DB should contain -all according to his expertise. The first thing that got me perplexed is that many of the tables on said file contain pretty few fields and that according to him, his design includes many "redundancies" that are supposed to make this DB faster and more reliable in the long run. I've never work with a design such as his but am pretty hesitant on whether or not to follow on his advice. Here's an example of what he proposes: For the book catalog, he proposes a table containing the following fields: Product ID ISBN Title Author Publisher (with a 4-digit code from another table) Retail price Another table will hold "Title number" (a redundancy in his words) and an "Alternate title" that will be related to the table above. Yet another table will hold "Author number" (yet another redundancy) and Co-author. From my perspective, I will only create a single table with all of the above fields integrated into it. My question is, is there really any benefits with the "redundancy" method or should I go with my original idea? Thanks in advance!

        • 1. Re: Do I really need table redundancies in FMP?
          RickWhitelaw

          The beauty of Relational Database design is partly due to the notion that any piece of data need only be stored  once and then accessed as needed in many contexts. Primary and Foreign Keys can not be truly described as "redundancies". You don't specify whether or not Product ID ISBN and Author number are used as keys. Generally a table should contain the match field and whatever data fields directly and necessarily relate. No more or less.

          RW

          • 2. Re: Do I really need table redundancies in FMP?
            Sorbsbuster

            Not using the relational abilities of Filemaker would be a terrible shame.  It could be that the Access guy has simply used the wrong term - the fields you name don't strike me as 'redundant'.  The nearest I can think of to make sense, and to involve the 'speed' word, is that if data is copied from one table to another and stored twice then it can be searched more quickly in both tables.  In that sense, one copy of the data is considered 'redundant', in that is is available elsewhere.  However the speed difference is seldom an issue, and the other huge benefits you lose, and other problems of synchronisation you create, are a considerable loss.

            I believe your understanding is the better practice, by a long way.

            • 3. Re: Do I really need table redundancies in FMP?
              WilliamdelaMaza

              Rick and Sorbsbuster,

              Product ID is the primary key in his DB design. I didn't stop to analyse the word "redundancy" but you're both spot on. None of the fields in his assesment are exactly a redundancy. He's just splitting the fields into several tables with very few fields on them.

              Can't see any benefit with this approach. I'll keep working as usual. Many thanks for shinnning a light unto me!