3 Replies Latest reply on Aug 7, 2012 2:42 PM by philmodjunk

    Multiple value relationship or multiple field relationship?



      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

      Character -Death

      Dialogues -Dialogue 1

      Dialogue 3

      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. 

        • 1. Re: Multiple value relationship or multiple field relationship?

          Welcome to the "joys" of many to many relationships. Wink

          A given character can appear in many dialogues and a dialogue can contain many characters.

          You don't want to use separate fields for this as this both limits you to a specific number of characters and results in a very complext network of relationships and data that is very hard to work with.

          The secret to effective many to many relationships is to add a third table, called a "join" table that serves as the link between the original two tables.

          Dialogues----<Dialogue_Character>-----Characters    ( ----< represents a one to many relationship )

          Dialogues::DialogueID = Dialogue_Character::DialogueID
          Characters::CharacterID = Dialogue::character::CharacterID

          With this setup, you can put a portal to Dialogue_characters on a Dialogues layout and it will list all characters that appear in that dialogue. You can enable "allow creation..." for the join table in the Dialogues to Dialogue_Character relationship. Then setup Dialogue_Character::CharacterID as a drop down list of characters from the Characters table. To add a character to the list, you go to the bottom "add" row of the portal and select one in the drop down. You can include fields from Characters in this portal row to display additional info about that character.

          Conversely, if you place a portal to this join table on the Characters layout, it will list all Dialogues in which that character appears.

          Here's a demo file that illustrates what I just described in a many to many relationship linking 'events' to 'contacts'. It also illustrates some more sophisticated options and also methods for ensuring that you don't link the same two records twice (such as Linking a character to the same dialog more than once.)


          • 2. Re: Multiple value relationship or multiple field relationship?

            Thank you so so so much! This was exactly what I wanted to achieve. Many-to-many relationships are so confusing but your example made it really clear and easy. It took me a while to get the script right for the creation of new entries but its all running smooth as a tiger in tuxedo now. I don't think that I can thank you enough for being so helpfull. 

            • 3. Re: Multiple value relationship or multiple field relationship?

              And having been reading the Ring of Fire series by Baen Books, "Bishop Laud" is a familiar nameWink