Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: DB Table Design

  1. #1
    Join Date
    Jun 2010
    Posts
    11

    Unanswered: DB Table Design

    Hello,

    I am creating a small database in Access with the following tables:

    Organisation
    Projects
    Project_Managers
    Employee
    Financial
    Invoicing

    I am unsure of how to link the tables, specifically Organisation and Projects.

    Organisation has the following fields:
    ID
    NAME
    ADDRESS
    COUNTRY
    EMAIL
    PHONE

    Projects has:
    ID
    Project_Name
    Project_Number
    Assignee
    Start
    Finish

    Each organisation can be involved in more than one project, so I am a bit unsure of how to create this link, do I need some extra fields?

    Many Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The next question is can a project have more than one organisation involved?

  3. #3
    Join Date
    Jun 2010
    Posts
    11
    Yes, each project will always have several organisations involved.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In that case you have a many-to-many relationship. Google "Association tables database"

  5. #5
    Join Date
    Jun 2010
    Posts
    11
    OK cheers, In Access, when I add the relationships I only seem to be able to add one to many, am I missing something?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Did you read up on Association tables? Because if you did you should know what you are missing.

  7. #7
    Join Date
    Jun 2010
    Posts
    11
    OK, let me check it out.

    Cheers

  8. #8
    Join Date
    Jun 2010
    Posts
    11
    ok, I think I understand. I need a 3rd table. I would have Organisation and Projects, then one in the middle called something like "Org_Proj"

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Cool - once read repeat back what you learned to check you got it sorted.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - there you go.

    Yes - in an RDBMS there is no way to create a many to many relationship. So, to allow one we create another table allowing two one-to-many relationships.

  11. #11
    Join Date
    Jun 2010
    Posts
    11
    ok, I have created the new table, but I can only have one of the fields as the primary key, If I am understanding the tutorial correctly, they should be both primary key?

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nope - you can have more than one column in a primary key (or index or unique constraint). A PK with two or more columns is known as a composite key.
    Highlight the two columns simultaneously and click on the key symbol.

  13. #13
    Join Date
    Jun 2010
    Posts
    11
    ok, got it. Cheers. Now to populate it

  14. #14
    Join Date
    Jun 2010
    Posts
    11
    Hi Again, what about in cases where the reslationship is one to one, are there special considerations in that case?

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The relationship is still many-to-many. If there is only one organisation for the project and only one project for the organisation you only insert one row.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •