Designing Tables for Ease of CSV Import Updates

[DimWit]

OK, let's put better words to the wa-wa-wa.

The thing I was asking about is the content of one row of the .CSV file, whether it was a complete entity unto itself, or whether it carried baggage.

The thing I hoped to avoid is when you have a row that has something like:
<PersonID>, <ClassID>, <DateTime>, <other-info-about-overall-class>, <SCORE-for-part-I>, <SCORE-for-part-II>, <SCORE-for-part-III>

Doing the above layout, those three partial scores don't belong in the main table, they would belong in a child table that represents the results of the individual parts. The part-score stuff would be the unwelcome baggage and would also be that part needing normalization.

The "cumulative file" problem can probably be done in two parts plus some setup, but there is still a decision to consider.

In setup, you have to make the .CSV content available to SQL, so that means importing or mapping a table from that .CSV file. Let's hope that there are no anomalies in the file.

The insertion part can be done by building a JOIN between that cumulative table and the target table, where you insert from the cumulative table to the target where the PersonID, ClassID, and ClassDate are NOT already in the target table. (This allows a re-take by having the class date as a part of the JOIN.) See also NOT IN syntax as a potential element in a WHERE clause. You basically want to insert records that are not already in the target table that you are working on.

The update part becomes trickier. You need rules on whether you allow an update to a score when the person, class, and date match between the record with the old score and the related record in a more recent iteration.

Progress: we're down to "wa." No, seriously, when I process long enough the mist clears. It's not you, I just have to exercise the frontal cortex in ways long-atrophied. Metaphorically, I'm an old man with a walker in lane 8 of the Olympics Men's 100 Meter event.
Your example very nicely further illuminates your earlier but I think I grasped it. The only way a row updates is a one-time change when status is changed from "In Progress" to either "Completed" or "Failed" and a date is inserted into the 'Content Completed Date' field (changed from a null/empty value)

 

[June7]

I think restriction on posting links has something to do with number of posts - a spamming countermeasure. When you hit a magic number, the restriction should lift.

 

[The_Doc_Man]

Progress: we're down to "wa." No, seriously, when I process long enough the mist clears. It's not you, I just have to exercise the frontal cortex in ways long-atrophied. Metaphorically, I'm an old man with a walker in lane 8 of the Olympics Men's 100 Meter event.
Your example very nicely further illuminates your earlier but I think I grasped it. The only way a row updates is a one-time change when status is changed from "In Progress" to either "Completed" or "Failed" and a date is inserted into the 'Content Completed Date' field (changed from a null/empty value)

If that change in "Content Completed" changes, that might be the key to some of your update tests. Because you can run queries with a WHERE clause that tests the two dates for equality / non-equality. When I was still active as a chemist, we always looked for "the metric" - which was our way of saying, "What factor, what change, what key indicator will we use to decide to take action?" When you are contemplating an action, always look for "the metric" that signals that it is time to act.