Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2010

    Unanswered: Association table linking several tables

    Hi all,

    I need to create an association table that links several tables. Here is what I’ve got followed by what I think might be a good answer. But, I’d like you to chime in and provide feedback, particularly if there is a better way.

    I have several “modules” on my site (i.e. users, accounts, teams, projects, etc.) that all need the ability to upload documents. I am looking to create one association table that links my modules to my documents. Here is the solution I came up with.

    I have my existing modules:

    users (user_id, name, etc.)
    accounts (account_id, name, etc.)
    teams (team_id, name, etc.)
    projects (project_id, name, etc.)

    I also have my documents table:

    documents (doc_id, name, etc.)

    I created a modules table so that I can have an id for each module (alternatively, I could create these as constants in my code):

    modules (mod_id, name)
    1 Users
    2 Accounts
    3 Teams
    4 Projects

    Then I created an association table to link the documents to my modules:

    documents_xref (doc_id, mod_id, mod_entry_id)

    100 1 23 | document w/id=100 in the user(1) modules w/user_id=23
    101 2 5 | document w/id=101 in the accounts(2) module w/ account_id=5
    102 4 568 | document w/id=102 in the project(4) modules w/project_id = 568

    Is this an acceptable way of doing this?

    Thanks in advance.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    Quote Originally Posted by pietb View Post
    Is this an acceptable way of doing this?

    use a separate association table for each "module" | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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