Normalizing data for joins, dedicated columns, a view or.. something else? As an example, i have the following tables

USERS, USER_JOBS and USER_HOUSES

USER_JOBS keeps a list of jobs for a given user_id

USER_HOUSES keeps a list of houses for a given user_id

the common use case is to see a user, his current job and his current house.

it looks like this :



To get the result i want i would need to join USERS with USER_JOBS and USER_HOUSES using a complex query (matching the latest seq in jobs and houses by last_update column)

here's one for just joining with USER_JOBS

SELECT USERS.USER_ID,
       USERS.DESCRIPTION,
       USER_JOBS.JOB_ID
  FROM USERS, USER_JOBS
 WHERE USER_JOBS.USER_ID = USERS.USER_ID
   AND USER_JOBS.SEQ = (SELECT B.SEQ
                          FROM USER_JOBS B
                         WHERE B.USER_ID = USERS.USER_ID
                           AND B.LAST_UPDATE = (SELECT MAX(C.LAST_UPDATE)
                                                  FROM USER_JOBS C
                                                 WHERE C.USER_ID = USERS.USER_ID))


(not even sure if that works, either way.. not fun)

I'm trying to find the best alternative, since there might be a lot of these

alternative A:



I create columns that make this a straight join to the corresponding SEQ in either table
the actually work happens when those tables are written to (either via trigger, or manually) to update the main tables 'pointers' to the latest seq.

so harder write (although not that bad), much easier read.

PROS:
 * easier queries
 * 
alternative B:

create a view that does the complex SQL of joining
There is no generic best solution, normalization is a trade-off between time, space, redundancy and its consequences.

Given your use case is to see a user, his current job and his current house, the original model is fine. And I think the SEQ column is unnecessary.

SEQ and LAST_UPDATE columns are redundant, they both contain information about a change. SEQ simply represents a change number, but LAST_UPDATE even contains the date of change. The number of changes can be simply found by counting rows for a specific USER_ID, so unless you have multiple changes in a second (the smallest unit a DATE type can store), the SEQ column is not mandatory, and USER_ID, LAST_UPDATE could be the PK. The query you write is very real, I have seen queries like that a lot of times, but you can do even better with analytic functions:

SELECT U.USER_ID,
       U.DESCRIPTION,
       UJ.JOB_ID
  FROM USERS U, 
  ( SELECT JOB_ID 
    FROM (SELECT JOB_ID, ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY LAST_UPDATE DESC) as R FROM USER_JOBS)
    WHERE R = 1
  ) UJ
 WHERE U.USER_ID = UJ.USER_ID AND U.USER_ID = :B1;