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.
Piet

no

use a separate association table for each "module"