Post reply

(Cross) Relationship between 2 tables.

  • July 29, 2010 at 3:26 am

    #387460

    I am search of cross relationship between the 2 tables.

    Suppose, I have a table named 'Resources', which stores data for Employees (including managers, employees) so it's recursive table with self join when query for employees reporting to managers, which are nothing but resources.

    Now, I have another table named, 'Projects', and have a relationship with Resources, for managers who manages the projects.

    So, from above perspective, (Manager) Resources has relationship of 1-many with Projects.

    Additionally, Project has resources too which is nothing but employees, and in above case, they are stored in Resources table.

    So how these tables will be related? If there is alternative solution, please do share.

    Thanks,

    Jag

  • August 2, 2010 at 3:48 pm

    #1201282

    Couple of questions about "Projects" table.

    Is there one and only one row per "project" in Projects table?

    When you say "Project has resources" do you mean "Projects" table knows which "resources" belong to each project? How?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • August 3, 2010 at 6:51 am

    #1201477

    It sounds like a many to many relationship.

    A "resource" can be assigned to many projects.

    A "project" can have many resources assigned to it.

    You need to create an intersection table with each key in it.

    ResourceByProject

    ResourceId (PK of Resource table),

    ProjectID (PK of Project table),

    other columns (if these columns exists of the parent table, then you may want to eliminate them from the parent and keep here)

  • August 3, 2010 at 9:11 am

    #1201607

    Thanx.

    Much appreciated.

    I did the same and its working!

  • August 3, 2010 at 9:17 am

    #1201613

    you're welcome. good luck.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply