Re: How to find values without a FK?

^Bart wrote:

> I got two tables to manage school's exams:
>
> subjects
> --------
> id_subject
> name
>
> users
> -----
> id_user
> name
> FK_id_subject
>
> The values of user's table are: "Tom, Peter, Sam"
>
> The values of subject's table are: "Economics, Biology, Latin, History,
> Religion, German"
>
> An user like Tom is linked by a FK to subjects "Economics, Biology,
> Latin" and it's easy to show which exams Tom passed but I need to show
> also which exams Tom should do in the future ("History, Religion,
> German"), how can I do it?

[Quoted] The easiest way I can think of is a many-to-many table.

  CREATE TABLE user_subject (
    id_user .... ,
    id_subject .... ,
    status ENUM ('complete', 'future'),
    FOREIGN KEY id_user REFERENCES users(id_user),     FOREIGN KEY id_subject REFERENCES subjects(id_subject),     PRIMARY KEY (id_user, id_subject)
  )

Populate the user_subject table with the appropriate user and subject keys for each course, along with the status of that particular combination (either 'taken' for a course that's complete, or 'future' for a course to do in the future).

Thus, to show that Tom (id_user 100) has taken Economics (id_subject 3), Biology (id_subject 11), and Latin (id_subject 21) and still needs to take History (id_subject 5), Religion (id_subject 30), and German (id_subject 20), you would have 6 entries in the table

    id_user id_subject status

• ========== ====== 100 3 complete 100 11 complete 100 21 complete 100 5 future 100 30 future 100 20 future

With this, you can get rid of the FK_subject on the users table.

-- 
Lew Pitcher
"In Skills, We Trust"