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:
- People (Primary key: PersonID) has a one-to-many relationship to
- Manuscript references (Primary key: RefID) describes references to those people has a one-to-many relationship to
- 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.