On 05/05/17 20:54, Gary Stainburn wrote:
> On Friday 05 May 2017 11:00:37 hubert depesz lubaczewski wrote:
>> On Fri, May 05, 2017 at 10:44:54AM +0100, Gary Stainburn wrote:
>>>> How many rows are in people? How many in assessments? Do you really
>>>> want data on all people? Or just some?
>>>
>>> I was looking for a balanced solution, combining performance and SQL
>>> 'purity'.
>>
>> Solution depends on answers to questions I asked. Will you always get
>> data for all people? Or just some? How many assessments per person will
>> there usually be?
>>
>> Best regards,
>>
>> depesz
>
> Oddly, part of my reply was missing.
>
> As I said, there are currently 720 rows in the people table.
> The will be a new assessment record for each person roughly every six months.
> The dataset will be open ended so will slowly but indefinitely grow.
>
> The part that was missing was that there will be new people records added who
> initially will not have an assessment. They will still need to be included in
> the report.
>
>
>
Depending upon what you might want to do with assessments that are
not "most-recent", you could use a trigger function to move
"out-of-date" assessments into a separate table and only keep the
most recent as the "current" assessment in the assessment table.
Makes the joins a little simpler and less time-consuming, again,
depending upon what you want to do with non-most-recent rows.
I have a real-world application that has marks awarded to assessments
per student (>10k students, >1k assessments) and I keep two tables:
'mark' for "current" (or most recent) marks and 'old_mark' as a kind
of audit trail of marks that have been modified, by whom, when and for
what reason. I use two trigger functions: 'insert_mark_func' and
'update_mark_func' which are triggered on inserts and updates on the
mark table respectively. Deletes are not allowed, but we can update
an existing mark so that its value is "NULL", which gets treated as
if it were a non-mark.
Don't know if this is best-practice, and certainly not "pure SQL",
but it is a balanced solution and has been working well for us for
over 15 years.
cheers,
Bob Edwards.