4 Replies Latest reply on May 6, 2004 9:37 AM by cane74

    Relational table with more than 2 foreign keys

    cane74 Newbie

      Hi.

      Is there any possibility to create CMP beans which are connected through a table like:

      table R_COMPANY_PERSON_POSITION {
       id_company: integer
       id_person: integer
       id_position: integer
      }
      

      Every foreign key corresponds to Entity Bean. Single company may have a lot of employees and every employee can have one or more positions. System should give a functionality of adding person to company with specified position. I don't want to write separate Entity EJB for this table, but I don't know it's possible to manage this relational table through @ejb.relation XDoclet tag. Any help will be appreciated :-)

      Regards,
      Bart


        • 1. Re: Relational table with more than 2 foreign keys
          sesques Novice

          Hi bart,

          First, I think that there is no restriction about managing this relation table through @ejb.relation, just you must use also the @jboss.relation-table, @jboss:relation and @jboss.relation-mapping to define the alternate table.

          But this means that you define many-to-many relations between the 3 entity beans. Do you really need this ?
          Does Employees belongs to many companies ?

          When I was young (so far away), I learned that this type of relation (with more than 2 tables) must be avoided as possible. But if you are sure of your modelisation, you can go on.

          Pascal



          • 2. Re: Relational table with more than 2 foreign keys
            cane74 Newbie

            Hi Pascal,

            suppose CRM system, where companies are customers and each company has many contacts to different employees and each employee has different company position - I think managing single relation table is not bad idea, but I'm still looking for best solution :-)

            The biggest advantage of such modelisation is that every person in system is always SINGLE record, even if the same person will be a contact to more than one company (it happens!). Of course I can do another table (lets say T_POSITION) with foreign keys defining company and person to which this position belongs, but then the information will be not in a single place :-(

            Regards,
            Bart

            • 3. Re: Relational table with more than 2 foreign keys
              Alexey Loubyansky Master

              If this table was read-only it would be ok. Otherwise, it won't work with the current implementation. Each pk pair is supposed to be a seperate row. This means if INSERT succeeds one of the columns will certainly be null.

              • 4. Re: Relational table with more than 2 foreign keys
                cane74 Newbie

                So I need to redesign part of DB - thanks :-)

                Bart