8 Replies Latest reply on Apr 22, 2015 9:35 AM by mikebeargie

    Normalization

    mariaocheltree

      We have a data base to keep the assets of the company but I saw that is not normalize.

      I start with the first normal form and broke the database to a lot of tables. Now I noticed that some assets does not need some field and some assets need more files. What do I do?

       

        • 1. Re: Normalization
          mikebeargie

          By breaking it out into separate tables you're not TRULY putting it into 1NF. You're just undoing the combination of data from a single table and making things a heck of a lot harder on yourself.

           

          You mention that you have "assets", so assumably 1NF would be:

          -a single table of assets

          -each record unique, possibly with a quantity representing duplicate assets.

          -no repeating groups (for instance many files in a single field, or a single record belonging to both "tractors" and "vehicles" ).

           

          "Some assets don't need all fields", not all fields are required to have a table in 1NF.

           

          "Some assets need more files", not sure if you meant files or fields, but this possibly dictates a child "files" table, where one asset = many files.

           

          I think you're overcomplicating things, not every database has to be rigidly "by the book", and a LOT of developers do fantastic work without using normal form style development. You said you saw it was not normalized, what made you decide that it was not?

           

          In filemaker, even a checkbox set or a repeating field can technically "break" 1NF, so I wouldn't take it too seriously. Do you have a sample of your original data you can post?

          • 2. Re: Normalization
            Stephen Huston

            I'm about where Mike is on this.

            My approach would be to put it all back in one table and allow some unneeded fields to be blank in records which don't use them. You could even have a CLASS field which indicates which of the 4 (or more) groups the item is in, and do validation on various field requirements based on the assigned Class value.

            The ease of reporting when everything is in one table will outweigh any normalization standard compliance in my opinion.

            • 3. Re: Normalization
              mark_scott

              Hi Maria,

               

              Totally agree with Mike and Stephen.  Vehicle, tractor, etc., are all just different types or classes of a single "Asset" entity.  Most of the fields and their domains (data type and allowed values) overlap among the four classes.  As Stephen mentioned, if you spilt these into separate tables, you're going to have difficulty with various reporting tasks: say, for example, a report listing all assets grouped by District and sorted by DatePurchase, with perhaps a summary field showing the total asset expenditures by District.  Since FileMaker doesn't have a native Union operation, this becomes difficult if the assets are divvied up among different tables.  (You could use ExecuteSQL ( ) and a virtual list technique, but why complicate things when the preferred route would be to keep these in a single table from the get-go.)

               

              So, I cast my vote for the single "Asset" table approach.

               

              hth,

               

              Mark

              • 4. Re: Normalization
                mariaocheltree

                Here is an example of the Original database.

                • 5. Re: Normalization
                  mikebeargie

                  yeah, the recommendation holds based on seeing that.

                   

                  It might be different if you have something like a "districts" table with records for salinas and yuma, since you have records that belong to more than one of those values, but that's really the only thing there that's not normalized. Maybe Inventory Category as well.

                   

                  I'd only be creating more tables if you need to report on the data from the district level or inventory category level, then you'd be looking at some multi-to-multi relationships to handle those perspectives. Otherwise the data is fine as-is for such a simple table.

                  • 6. Re: Normalization
                    mariaocheltree

                    Do I leave the blank files for example bed, row, line, pt, Joints, Pum Serial number, Engine Miles, Engine hours, Engine Serial Number in blank because the Vehicles category does not need them.

                    • 7. Re: Normalization
                      siplus

                      When speaking to a new client the first thing I ask is "what is your atom". If he does not know, I help him find it

                       

                      The forum members above have found it for you.

                      • 8. Re: Normalization
                        mikebeargie

                        Yeah, anything that’s required you can do validation on to require a value. Anything else you can just leave blank. Filemaker doesn’t really care about blank values unless you tell it to care in the field validation, or if you try to summarize data based on that field.