8 Replies Latest reply on Nov 25, 2015 5:40 AM by baldewicz

    Database Design One Large Table or Many Small Tables?

    baldewicz

      Hi All,

       

      I am creating a databases which will convert about 5 paper forms to one database.  The forms are always filled out it the same order, the only reason there were multiple forms was there were multiple people that were responsible for filling out the different sections.

       

      I am a newer developer and am not sure what is the best way to go about the design of the database.  I could create one table that could house all of the sections of the form, this section would have about 600-700 fields when completed, with minimal fields being left blank.  My other thought was to create A General overview table with very basic information that is related to several sub tables in a one to one relationship.

       

      The several smaller tables seem easier to manage (less fields to scroll through, obvious devision of fields based on subsection), however when looking for times when people use a one to one relationship I find that most people say that it is not helpful, or possibly makes the solution harder to manage.

       

      Note: Regardless of the design there will be several layouts and/or tabs to organize the fields, which would likely correspond to the subtables if a one to one relationship is used.  This is a local hosted database that will only be used by users in the office.

       

      If there are any details I missed that would be helpful please let me know.

       

      I apologize if this is an easy or obvious question, and thank you for your help!

        • 1. Re: Database Design One Large Table or Many Small Tables?
          RickWhitelaw

          Having hundreds of fields is not a good idea. A table should record records that describe one entity. You can Google this idea and find many entries.

          • 2. Re: Database Design One Large Table or Many Small Tables?
            Mike_Mitchell

            time_saver's answer is on the right track. Instead of thinking in terms of "forms", think in terms of database entities. An entity is a "thing" about which you are trying to track information. What do these "forms" represent? Applications? Registrations? Items in a workflow? Each item - each unique entity - belongs in a table.

             

            I suggest you get a copy of the FileMaker Training Series and go through the chapter on Data Modeling very carefully. The question isn't, "One big table versus many small tables." The question is, "The right number of tables to model the workflow correctly." (Hint: This will usually result in many small tables.)   

             

            HTH


            Mike

            • 3. Re: Database Design One Large Table or Many Small Tables?
              DavidJondreau

              Is there data that is the same between different forms (ie, patient visits where each visit is different, but the patient may be the same)?

               

              After the data is entered, then what? How is the data used?

              • 4. Re: Database Design One Large Table or Many Small Tables?
                coherentkris

                Start here.

                Database normalization - Wikipedia, the free encyclopedia

                Learn what can be done then do what makes sense for your solution

                • 5. Re: Database Design One Large Table or Many Small Tables?
                  keywords

                  I'm with the other replies. Think of a table as an entity (e.g. a person) and fields as characteristics of that entity (name, age, height, hair colour, etc). It can be easy to think of address as also being characteristics of a person, but in fact it is not, they are really characteristics of a specific location, which may have other characteristics—type of cladding, size, etc. A person may have more than one (home, postal, work, etc), or they may move from one address to another, and the database should be able to handle this.

                  David's point is a good one. Taking this a little further, the sections of the forms may at first seem like the obvious divisions, but are they? Do they group together specific entities?

                  Finally, note that while you are assuming you will be dealing with a bunch of one-to-one relationships, I suspect this is not in fact the case. Your description already points to at least one that is not—"multiple people that were responsible for filling out the different sections". Is the identity of the person who fills out a section kept on the form? If not, should it be? And if so, would it not be easier to have their details kept in a table so that all that needs to be entered is an ID to link to them? Right there is a many-to-many which you will need to resolve.

                  • 6. Re: Database Design One Large Table or Many Small Tables?
                    baldewicz

                    All of the forms are pieces to the design of the same product, so there are many pieces which will never have more than one answer (one exception is below).  The Data will be almost exclusively text. The reason we are moving it to FileMaker is it is much easier to search the database then to pull the individual paper files.

                     

                    So we start with an idea for a product, a person will initiate and create a file (Database record), this can be anyone in the office, all users are required to log in to access the database, this is where I grab the name from the user entering the file (Auto Enter Field).  The next section is filled out to reference previous forms (this is one to many).

                     

                    I realize that the database will not be solely one to one relationships, however in the next section there are many, many fields which have a yes/no answer which are required to be answered in relation to our quality system, about 100 questions.  This is the primary reason for my question.  All of these questions are required to be answered by our Quality Manager, she is the only one with permissions to do this (both company policy and FileMaker permissions).  These questions will only be answered once for each product, and the answer to these questions will never change.  There are several other sections like this in which a text field is required that will have exactly one entry (ie patent number, patent date, ect.).

                     

                    I hope this makes what I'm doing a bit clearer.  We are a very small company,  the maximum people that would ever work on the form is 4.  I would like to plan for expansion, however in the foreseeable future this will not be exceeded.

                     

                    Thank you everyone for your help!

                    • 7. Re: Database Design One Large Table or Many Small Tables?
                      Mike_Mitchell

                      baldewicz wrote:

                       

                      I realize that the database will not be solely one to one relationships, however in the next section there are many, many fields which have a yes/no answer which are required to be answered in relation to our quality system, about 100 questions.  This is the primary reason for my question.  All of these questions are required to be answered by our Quality Manager, she is the only one with permissions to do this (both company policy and FileMaker permissions).  These questions will only be answered once for each product, and the answer to these questions will never change.  There are several other sections like this in which a text field is required that will have exactly one entry (ie patent number, patent date, ect.).


                       

                      The questions / section items should be in a separate table. They're like line items on an invoice. This is similar to a survey solution, where you have a parent record (the survey) and all the questions associated with it (the line items).

                       

                      Define a library of questions. Import the questions into the line items table (whatever you want to call it) whenever you create a new parent record. Then you can filter the questions according to whatever stage of the evaluation you're on and not drag the solution to a painful halt.

                       

                      These questions will only be answered once for each product, and the answer to these questions will never change.

                       

                      Uh huh. I've heard that before.  

                       

                      I don't ever believe anyone when they say, "This will never change." Did you say you had a Quality Manager? Those are the sorts of folks who love to add new things to look at.  

                       

                      Seriously, there are very few solutions or processes that "never" change. Like, I've never worked with any. Design your solution so it can be flexible. If your line items are a separate data table, then you can update the question list by simply updating data in that table. You will thank yourself many times before it's over.

                      • 8. Re: Database Design One Large Table or Many Small Tables?
                        baldewicz

                        I apologize, i'm not sure how to Quote text.

                         

                        "Define a library of questions. Import the questions into the line items table (whatever you want to call it) whenever you create a new parent record. Then you can filter the questions according to whatever stage of the evaluation you're on and not drag the solution to a painful halt."

                         

                        That makes a lot of since to put the questions  as "line items", thank you.


                        "I don't ever believe anyone when they say, "This will never change." Did you say you had a Quality Manager? Those are the sorts of folks who love to add new things to look at."

                         

                         

                        What I meant by the answers will never change once answered on that form, the form will not be edited once answered.  We are a medical device company and require notification (FDA, ect.) and these changes would require additional forms.  Under our current system these are not filed together (this will one day be a database that will talk to this one.  One project at a time, I'm not a full time developer ).   There is a possibility that the questions may change over time.

                         

                        This is why I asked I would never of thought of the questions as line items! This method I think will get me much closer for a large portion of the project.  Thank you.