Relationships Too Complicated for FileMaker?
I've never created a database before, and the people who have been helping me tell me that I have very complicated data. (I'm an historian, writing a ph.d. on some very collaborative creative productions). I have been advised that FileMaker Pro 11 is not powerful enough to do what I want it to do, and I should use Access or some other program. However, Access limits field size to 255 characters, which makes it useless for note-taking and text-based data & analysis. Is there a way to do this in FileMaker?
Here are my tables:
People, Works, Performances, Albums, Episodes, Characters
I need to have these relationships:
people to works
people to albums
people to episodes
people to characters
people to performances
performances to works
performances to albums
performances to episodes
performances to characters
(so basically, both people and performances have to be linked to all other tables).
And each of these relationships must be many-to-many. They need to accomodate the type of information in this example:
people to works: Joe composed Theme Song. Joe, Bruce, and Jon wrote lyrics for Theme Song.
So basically, I need to keep track of the specific relationship an individual person has to an individual work, a person can have multiple relationships to a work, and multiple people can have the same relationship to a work.
A professional database builder explained to me how to do this on a pc program: My six tables should have no relationships to each other. Another level up, I should create views, which are join tables for each of the relationships I have listed. After the records are created for each person and each work (etc), I enter information into the view/join table where I pull info from multiple tables, then I submit, and that creates the relationship.
I can't figure out how to do that on FileMaker, because it tries to be user friendly by the relationships at the table level, which would be impossibly circular and not complex enough if I tried to implement the relationships I've laid out. And there is no "view" level to get around all that manually. Is there a way to do this on FileMaker, or should I try a different database program?
Thanks in advance,