6 Replies Latest reply on Jun 12, 2013 5:28 AM by NathanVeitch

    A quick question on database design

    NathanVeitch

      Title

      A quick question on database design

      Post

      Hi there,

      I have a quick question to ask with regards to database design. Would it be better to have few tables and many records or where possible, split the the tables up? I read on one of the forums that having narrow tables is better. The way I understand that is, it would be better to split up tables that have many records. 

      For example, I have an employees table with all the usual stuff like name, address, next of kin ect. I now need to add a skill set feature for the employees. I just want to find out, would it be better to create a new table just for the skill set feature, or should I just add it into the employee's table, or am I just being picky about my design? The skill set will just be a set of fields that has a checkbox 1 -10 and the user will be rated according to the skill level. The total fields required for the skill set feature is around 20. The skills might be changed in the future. 

        • 1. Re: A quick question on database design
          KenCheung

               Personal suggestion:

               If there's a fixed skill set and boolean Yes | No flag, I'll prefer adding fields in the Staff table.

               If the set of skills set keeps changing and you need to keep the old skills on the staff record, I'll prefer a "Staff Skills" portal and linked table, where the Skill edit box will have a custom values Value List / value list from another table.

          • 2. Re: A quick question on database design
            philmodjunk

                 Note the word "prefer" in Ken's Cheung's post. This is one of those areas where I've seen loud noisy arguments amongst skilled experienced developers as there isn't a hard, clear "rule" that makes an unequivocal case one way or the other. Instead, you have trade offs.

                 There's a certain amount of "Art" to go with the "science" of data modeling such as this.

                 My Preference, is that if it's multiple skills to one person, to use a related table for greater flexibility unless the list of skills is very small, the data recorded for each skill extremely limited and the likelyhood that you will later need to change the list of skills approaches zero. Adding more fields to your table to add a new skill is a design change. Adding a new related record in a skills table is a date entry task.

                 You may even need two tables, one as a master list of skills and another table set up as a join table so that a given person can list multiple skills and so that multiple people can list the same skill.

                 A good rule of thumb is that if you find you have a very similar set of data fields in two or more tables, there's very good chance that those fields should be placed in their own table and linked via relationships to the tables where you have currently defined that common set of fields. An example of that is when you find that you have a set of name, phone, email, mailing address fields in both an customer and a vendor table. Putting that set of fields in a new table called "contactInfo" and linking it to the original customer and vendor tables may become a very good improvement in your data model--especially since it enables you to have a vendor that is also a customer without having to enter and maintain their contact info in two different tables...

            • 3. Re: A quick question on database design
              davidanders

                   A list of links about design and general and specific FMP techniques.

              http://forums.filemaker.com/posts/f6ed4be796?commentId=222931#222931

              • 4. Re: A quick question on database design
                NathanVeitch

                     Hi there,

                      

                     Thank-you so much for the advice. I have ended up using a join table to a master skill set table. This is a new concept for the company so they are not to sure how they want it to work. Having a the separate table is helping cuase I can modify the skills as my client works on it. 

                      

                     Thank-you again for the help.

                • 5. Re: A quick question on database design
                  philmodjunk

                       You may find this demo file that uses a join table a helpful source of ideas: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                  • 6. Re: A quick question on database design
                    NathanVeitch

                         Hi Phil,

                          

                         Thank-you so much for the DemoW fp7 file. I have just been able to go over it and there is a lot in there that is going to help me. Thank-you