2 Replies Latest reply on Jun 23, 2009 3:21 PM by comment_1

    Initial database set up question....

    cwright

      Title

      Initial database set up question....

      Post

      Hi there,

       

      I am setting up my first database -  it is to manage 3 kinds of people within one organization:  we are a healh related clinic that sees patients, sells a few products and does training. I would like to set up a database(s) that has the ability to contact manage people in each of these three categories, understanding that there is a lot of overlap many of the individuals fall into more than one of the three categories. The difficulty is, for 1) student 2)customer and 3)patient, the fields will be slightly different.  How do I create one large database, that enables us to manage contacts accross the board, while having fields that are unique to 'customer', 'patient' and 'student'. 

       

      I hope this makes sense - please let me know how I might begin to set this up - 

       

      Thanks very much.

      Catherine

        • 1. Re: Initial database set up question....
          mrvodka
            

          You need one contacts table.

          Then perhaps an Events table.

          You will then need a join table that allows you to add people from your contacts table to a particular event.

          • 2. Re: Initial database set up question....
            comment_1
              

            cwright wrote:

            The difficulty is, for 1) student 2)customer and 3)patient, the fields will be slightly different.  How do I create one large database, that enables us to manage contacts accross the board, while having fields that are unique to 'customer', 'patient' and 'student'.


            Basically, there are two ways to handle this situation: a simple one and a proper one.

             

            The simple solution is to put all the fields in the same table, and leave them empty for records they do not apply to. This solution is particularly attractive in version 10, where script triggers can give an appearance of dedicated tables without actually creating them: all you need is dedicated layouts, and scripts to find records in the desired category and select the correct layout.

             

             The proper solution is to have one supertype table for all contacts, and a satellite table for each subtype, with a one-to-one relationship between them. This is not easy to implement - if you are just starting with Filemaker, I'd suggest you put it aside for the moment. If you decide to go this way at a later stage, converting existing data between the two methods shouldn't be too difficult.