0

I have a table called 'notes', on this table I need to track who made that note, but the problem is that the creator of the note can be a user stored in one of three possible tables:

users leads managers

I have though of simply create three fields on 'notes' to represent the three possible relations: note.user, note.lead, note.manager

With this approach I would be forced to create three table joins when requesting the notes to gather the creators information, and I don't think that is the way to go, so I would like to hear your ideas or comments and what would be the best approach on this.

ulizama
  • 13
  • 2
  • similar to http://stackoverflow.com/questions/4050784/defining-multiple-foreign-keys-in-one-table-to-many-tables/4051523#4051523 – Damir Sudarevic Jan 24 '12 at 20:44

4 Answers4

1

For me personally this smells like a design problem on a totally different part of the schema: Are manageers not users? Do leads carry person information?

With any approach that creates a relation between one column and one of three others, you will need three joins for the select. If you can't rectify the underlying problem, I recommend you use

note_type ENUM('users','leads','managers')

as an additional field and 

SELECT
  ...
  IFNULL(users.name(IFNULL(managers.name,leads.name))) AS name
  ..
FROM notes
LEFT JOIN users ON notes.note_type='users' AND users.id=notes.note_source
LEFT JOIN managers ON notes.note_type='managers' AND managers.id=notes.note_source
LEFT JOIN leads ON notes.note_type='leads' AND leads.id=notes.note_source
...

for the query

Eugen Rieck
  • 64,175
  • 10
  • 70
  • 92
  • As the other answers have noted, there is an underlying design issue, but there is no way right now to change that, so I think this solution might be the most reasonable one at the time being. – ulizama Jan 24 '12 at 21:00
0

I think you need to abstract out the concept of a user id, so that it does not depend on their role. The author of a note could then be specified by the user id.

Users could be assigned roles, and maybe more than one.

yas
  • 3,520
  • 4
  • 25
  • 38
0

The correct way to structure this would be to pull all common data out of users, leads, and managers. Unify this data into a "contact" table. Then if you want to get all notes for a given manager:

managers->contacts->notes

for a lead:

leads->contacts->notes

Notice your original post: "the problem is that the creator of the note can be a user stored in one of three possible tables"

From the structure of your sentence you even admit that all these entities have something in common; they are all users. Why not make the DB reflect this?

Timothy Baldridge
  • 10,455
  • 1
  • 44
  • 80
0

you have to model a parent table for the three tables you already have. Define a table that depicts generally user, leads and manager tables. Something like "Person". So you have all of the ids of the three tables and any common attributes on the Person table. And when you must define the relationship you put the foreign id "Person_ID" on the note table. And when you model user, leads and manager tables you also put the primary key as a foreign key to the Person table. So you would have something like this: Table users: Users( person_id primary key ...(attributes of Users) foreign key person_id references Person.person_id )

This model i depict is common to any relational model you have to model using parents and childs

Marcos Santiago Lambolay
  • 21
  • 2