Multiple value relationship or multiple field relationship?
I am currently making a database of collection of literature called dialogues. I am having an issue designing part of the structure of the database. As part of the database I am listing all the characters in each dialogue, as many of them are used in multiple dialogues I want to create a relational field that will allow me to list all the dialogues that certain characters such as Death, Mother, Bishop Laud, and Student feature in along with their character type in a seperate table.
At the moment I was thinking of having a field for each character in the dialogue, and then relating them to a character record in another table, but I am having trouble in creating the relationship between the character fields in one record and assigning them to a related table because a character can be put in any of the three character fields in the table.
Ie. Table 1 - Dialogue 1
Character 1 -Death -Mythical
Character 2 -Mother -Stereotype
Character 3 -William Tyndale - Historical
Table 2 - Characters
Dialogues -Dialogue 1
Beacause the character death could be either character 1,2 or 3 in the dialogue I want the relationship to be linked to all three fields but this doesn't seem to work as the multiple relationships are AND not OR and obviously Death is not going to be characters 1,2 and 3. What I am wondering is: is it possible to have multiple values in one field so that I could have a character field that could contain all three characters whilst at the same time making it so that each character can be related to character table? Ideally it would also use the creat record to create records when new characters are added. I am sure it must be possible to do this but I can't work out how. Any help on how to get around this would be much appreciated.