7 Replies Latest reply on Jan 7, 2013 9:31 AM by BruceRobertson

    DB Design/Normalization


      The problem that I have before I start to really build/layout my database is the overall design/normalization of the database. Like how many tables should I use for starters? I am trying to take an existing MS Access database that has been kept alive since the 90's and put it into something new. This database will only be used by an IT group of less than 10 to manage assets that consists of computer equipment. As we move equipment often we chose FileMaker as it's heavily used in our field plus we would like to use FileMaker Go for assigning equipment or marking the location of an Asset if its moved.


      I first extracted all the data from the existing database into Exel files and cleaned it up. This consist of 5 main tables: "Users", "Computers", "Monitors", "Printers", and "Devices" (barcode scanners, scanners, misc equipment). These tables all have similar fields (~20 fields), but do not share all of the same fields. There are around 1500-1700 records from the 4 asset related tables and less than 200 records in the user table. Should all of the assets be in one table or multiple tables? I looked at the concept of moving the Assets to one table based off the starter file included with FM12 and largely due to Portals. As I could select a user from a User Layout and have a portal display all of the equipment assigned to them, however this would get confusing if I had to try and pull data from multiple tables. I know I could use tabs to go through different tables and have different portals, but each user really won't have much equipment.


      If it makes any difference we have fast internet and are running FileMaker Server 12 Advanced. As there will never be many users on this database I don't see network traffic being an issue. If anyone could offer some advice I would greatly appreciate it.





        • 1. Re: DB Design/Normalization

          soberbob wrote:


          Should all of the assets be in one table or multiple tables?


          It depends on what you intend to do with them. If at some point you need to treat all assets, regardless of type, alike - for example, show all assets in the same portal, or produce a report of all assets by user - then they should all live in the same table.


          The question then becomes what to do with the fields that are specific to each type. In most cases you can simply include all of them in the same Assets table and leave them empty where inapplicable (you would, of course, have a layout or a tab panel for each type, with only the applicable fields shown). Although, to be strictly correct, these fields should be farmed out to satellite subtype tables with a one-to-one relationship to the supertype Assets table. But this is not  easy to implement.

          1 of 1 people found this helpful
          • 2. Re: DB Design/Normalization

            A lot of ideas and concepts have been studied during my RDBMS design and development days since 1985. Back in the early '90s, I had the honor to meet and work with Les Cardwell prior to him earning his PhD in database design theory when he was creating his new concepts on "Abstract Normalization."


            An Advanced Concept of Relational Theory


            Since then, I have employed this concept in all projects where "Data Driven" is my design "motto" in order to minimize data redundancy and speed up most all database operations.


            For example, many developers will use two different tables, one for Company and another for Contact. Using the Abstract Normal Form (ANF) model, only one table is employed, Names, where there is a new Names field, Type, that defines the type of Name; either an organization or an individual, which is a part of the defined relationship key(s).


            As I slowly continue with my legacy database migration project into FMP from DataEase (ERP) and SalesLogix (CRM), this model is the cornerstone of the design construct.


            Hope this helps some how in getting you started in a direction that aligns with your project scope.




            PS: Dr. Cardwell's initial white paper noted above is worth studying if this is at all intriguing to you.


            PPS: https://fmdev.filemaker.com/message/69791

            1 of 1 people found this helpful
            • 3. Re: DB Design/Normalization

              And it is intriguing; thanks for the link to the paper!

              • 4. Re: DB Design/Normalization

                As the database is suppose to be very simple and not heavily used I think I can start it off like this.  I may seperate some of the fields into different tables, but it defiantly answered my question about seperating the different types of assets and being able to use just the one table.

                • 5. Re: DB Design/Normalization

                  Interesting paper. It pleased me: I've developed a very similar model working in FMPro v7-11. Translating SQL design models into FMPro often meant re-inventing the wheel. In doing that I ended up with something like the ANF model. With FM12 we can implement SQL methods natively, which brings a lot  more of the paper into scope for FMPro.



                  • 6. Re: DB Design/Normalization



                    I think Mike is exactly right. With only 20 fields, the asset table isn't bloated. While each asset type is likely to have a few unique fields, the majority of them will be common. Their will be tremendous advantages in reporting and printing if they are all in the same table.


                    Keeping them all in the same table also will much more easily allow you optimize finds.


                    One thought for portals and reporting would be to concatenate the "unique" info for each asset type into one text field. That way you could easily display tech info for each asset type in a portal or list (resolutions of monitors, speed of computers, size of desks, and so forth).


                    Bob Gossom

                    • 7. Re: DB Design/Normalization

                      I found the paper interesting, but it uses unfamiliar terminology and is very hard to follow. Projections?


                      I wish there was a translation; or example FileMaker files.