8 Replies Latest reply on Mar 8, 2017 11:58 AM by fmpdude

    How can I add a new top-level table?


      I am trying to work out how to create two new tables at the very top of a very extensive existing database structure. I am an historian, and only an amateur programmer, and I'm really confused about how I can do this, so my apologies that the following is simplistic.


      My existing structure consists of three tables:


      1. People (Primary key: PersonID) has a one-to-many relationship to
      2. Manuscript references (Primary key: RefID) describes references to those people has a one-to-many relationship to
      3. Letters (LetterID) describes letters in these manuscripts


      There are also tables about Places, Dates, and so on, but they're beyond the scope of this question.


      I've realized that this structure is flawed. Two more elements need to be added above manuscript references:

            +1. Libraries -- describes the repositories in which manuscripts are stored, will need a one-to-many relationship to

           +2. Manuscripts -- describes the manuscripts in which manuscript references occur, which will have a one-to-many relationship to my                       existing manuscript references table.


      Obviously, some data in table 2 will need to be moved into +1 and +2.


      I currently have a table of the libraries with a primary field (LibraryID) but in the manuscript references table, I simply use a value list to get their names -- i.e. they are not in a relationship to manuscript references. I do not have a separate manuscripts table yet, but would want it to include information about the manuscripts, which would then be drawn on in the references table.

      I'd be very grateful for a good (hopefully simple) answer, as it would overcome a long-term confusion and produce a much more useful database. I have used Filemaker scripts, so a scripted solution should not be a problem.


      Many thanks,


        • 1. Re: How can I add a new top-level table?

          Refactoring is what you are considering and it can only be scripted/performed by you as you are intimate with the current system and data.


          The first part is as simple as creating a new table, filling it with the data elements you need for the new entity, adding foreign keys to the existing tables and linking the tables together in the relationships graph.


          The difficult, second, part will be to use scripting and other FM functions to move data from the old tables into the new tables.


          Learning how to refactor is a good skill to have as it will force you to use FM functions that you may not use in everyday use.


          Refactoring comes with a cost because it takes time and effort that might be better spent doing a thorough analysis / data modeling activity so you only have to refactor once.

          • 3. Re: How can I add a new top-level table?

            It's not clear to me why you have a table named manuscript and a table named manuscripts. What is the purpose of each table? What does one record in each table represent?

            • 4. Re: How can I add a new top-level table?

              Thanks Kris.... I thought there might be a simpler way.


              Phil -- sorry, I'm not being clear.


              [2] is "manuscript_references". It records references to people (from records in [1], the "People" table) found in various manuscripts, which themselves are held in repositories, "libraries" [+1]. In my existing structure, manuscripts are not a separate entity, and information about them therefore are duplicated in the "manuscript_reference" table, because each manuscript can refer to many different people.


              The new table, "manuscripts" [+2], would record information about the manuscripts themselves.


              The tables should be related to each other like so:


              [+1] Libraries < [+2] Manuscripts < [2] Manuscript References [join table] > [1] People


              i.e. one library to many manuscripts;

              one manuscript to many manuscript references;

              one person to many manuscript references.

              • 5. Re: How can I add a new top-level table?

                I would recommend you create an ERD of what you have first. Document each table and field.


                Having a real ERD (not the Relationship Graph), it's so much easier to conceptualize and modify your database.


                Best yet, there are tools like SQL Editor that will read/create/update your FMP LIVE database for you.


                HOPE THIS HELPS.

                • 6. Re: How can I add a new top-level table?

                  Weird how I missed that one word. I see that you plan this sort of relationship between people and manuscripts:


                  Manuscripts---<Manuscript_References>--------People    (----< means "one to many" )

                  Manuscript::__pkManuscriptID = Manuscript_References::_fkManuscriptID

                  People::__pkPeopleID = Manuscript_References::_fkPeopleID


                  This sets up a many to many relationship between people and manuscripts so that one manuscript can reference many people and a given record in people can be referenced by more than one manuscript.


                  If there's a lot of data in Manuscript References that should be removed from it to Manuscripts, you may have a bit of a challenge. Import records could copy over the data, but you don't want more than one record to a Manuscript in the new table. If there's a field that uniquely identifies each manuscript in your existing data in Manuscript References, you can set up a field for it in Manuscripts and specify "unique values, validate always" and then import your records. That validation rule will filter out subsequent records for the same manuscript during import. But that assumes that the first such record has the data you want. If you have different records for the same manuscript and the data you want to import differs from record to record, you may have to use a more laborious process to move the data.

                  • 7. Re: How can I add a new top-level table?

                    Thanks Phil. I think your "unique values, validate always" tip will work in my case, but I'll need a few days to go over it. At the moment looking at a ferociously complicated relationships diagram, which I'll try to get down to size using an ERD as fmpdude suggests. Will report back how it goes!