0

I have a Users table:

UserId (int)
DeleteSuspended (tinyint 0=ok, 1=delete, 2=suspended)
Email (varchar)
etc

I have multiple tests tables, eg testhistory:

UserId (the same as the one above)
DeleteSuspended (this is the one I want linked)
Mark (a test score)

I do a lot of work with the test tables, but each time I need to check DeleteSuspended, so it is optimal to have that value in that table. But, the user is deleted or suspended globally, ie based on the User table.

My question is this. Can the DeleteSuspended be linked to the one in the User table. Ie, when I change the DeleteSuspended in the User table, it automatically updates all DeleteSuspended in all the test tables, ie the DeleteSuspended in the test tables are in effect references to the main one in the Users table?

Rewind
  • 2,554
  • 3
  • 30
  • 56
  • Do you had a performance problem when the column `DeleteSuspended` was not in your testhistory table? When you use a simple JOIN in your query you can access all the columns from the other tables. Don't run [premature optimization](http://stackoverflow.com/questions/385506/when-is-optimisation-premature). – Progman Mar 28 '16 at 16:24
  • I am unaware of any performance hit. It was just cumbersome to write in php. I do not understand joins. Can I do things with multiple tables with the same SQL statement? (I am looking up JOIN now.) – Rewind Mar 28 '16 at 16:33

1 Answers1

0

Answer: I have done the following using multi-selects. Please comment if this is the best approach.

I have removed DeleteSuspended from testhistory.

Then I use multi-select, eg if I want to get Test scores from non-suspended users only (ie DeleteSuspended = 0)

SELECT
    t.Mark
FROM
    users u, testhistory t
WHERE
    u.DeleteSuspended = 0
Rewind
  • 2,554
  • 3
  • 30
  • 56