5 Replies Latest reply on Sep 3, 2013 1:11 AM by keywords

    Relationships from Same Table


      I have a table for organizations and two tables for members. One for members and one for new members. Is it more effieicent for a larger database to have the two tables for the different types of members or to just have one table for members with a type field?


      If I were to use one table for members how would I set up a join table so I coud assign members to new members (ie: as an advisor or mentor)?



        • 1. Re: Relationships from Same Table

          Been using a similar design construct for years where I employ a "data-driven" approach and lots of "compound" relationships to achieve what you are asking about. "Type" and "Category" fields are used all the time where I take your design approach one step further with the Abstract Normal Form (ANF) model where there is only one "Names" table that contains Organizations and People.


          If interested, you may want to check out my Jan 5, 2013 reply in the DB Design/Normalization topic:



          I also added a URL link to a paper on the concepts of "Abstract Normalization."


          Hope this helps address some of your issues.

          1 of 1 people found this helpful
          • 2. Re: Relationships from Same Table

            Hello, tm9.


            If you only have one advisor, you wouldn't really need a second table. All you would need is an "advisor ID" field in the same table and a self-joining relationship. Works like this:


            1. Create a second table occurrence on your Relationships Graph for the same table.
            2. Draw a relationship from "advisor ID" in the primary TO to the primary key of the new TO.


            Done. Now, to associate an advisor with a current member, just insert the ID (primary key) of the advisor in the advisor ID field.





            1 of 1 people found this helpful
            • 3. Re: Relationships from Same Table

              In my opinion: one table for members with a type field.

              Data should be ONLY in one place.

              • 4. Re: Relationships from Same Table

                That is the method I decided to go with. 


                I have created a dynamic button set (With a repetition field) that changes based on the users settings to navigate between layouts. It appears to be working correctly and navigating to the correct layouts.


                My problem now is that I created a Self-Join Relationship within the single members table relating the primary key fields and the type field to a global type field.  I have selected one of the layouts to use this self-join as its table occurence but it is still not filtering the records to only that type. I am not sure what I am doing wrong?



                • 5. Re: Relationships from Same Table

                  From what you describe I would have thought you may need two global fields on the parent side of the relationship: globalPK = primaryKey, AND globalTYPE = type.