How should I handle updates that occur hourly?

Question

I’ve got a table:

CA

• ca_id
• placement_id
• blah
• blah

This table gets updated with new unique placements every hour from data warehouse. Now the end-user wants to track statuses for those placements.

I was thinking of adding a new table which would get updated hourly as well:

CA_Status

• placement_id
• record_status
• last_updated_on

OPTION 1:

Add unique constraint to placement_id. Implement some mechanism (trigger perhaps?) where if the placement exists update the record_status and last_updated_on fields. If not create a new record. I can then join this with the CA table, by placement_id, and get the latest status of each placement.

OPTION 2

I can dump the unique constraint on the placement_id. This will let the table grow with the latest record_status for any placement. When I join I can get MAX(last_updated_on) to get the latest record_status.

OPTION 3:

Dump the CA_Status table all together. Add the new attributes to the CA table and do something like option 1

If OPTION 1 are triggers the way to go?

If OPTION 2 would this make my table un-necessarily large.

---

update: I guess with Option 1 I don't really need the CA_Status table. I could possibly incorporate those fields into the CA table and update accordingly.

Answer 1

My vote's for option 1. Just make sure that if you do alot of updates on the hour performance doesn't take a huge hit with the triggers.

What is sending the updates to the DB? If it's a written program it'll take a few more database calls to line up all your ducks to insert/update CA_Status that way.

Answer 2

In MS SQL 2005 and above you can grow the table and select like this:

SELECT placement_id, update_time, anything_else
FROM (
  SELECT m.*, ROW_NUMBER() OVER (PARTITION BY placement_id ORDER BY update_time) AS rn
  FROM mytable m
  ) mo
WHERE rn = 1

This is most efficient, as this will use an index scan, no joins and no triggers.

Of course, it you use MS SQL 2008 and don't want to keep history, you may add you data like this:

MERGE
INTO mytable
USING VALUES(@new_id, @new_time, @new_data)
ON placement_id = @new_id
WHEN MATCHED THEN
 UPDATE
 SET update_time = @new_time,
     anything_else = @new_data
WHEN NOT MATCHED THEN
 INSERT (placement_id, update_time, anything_else)
 VALUES(@new_id, @new_time, @new_data)

This will insert the placement if it isn't there yet, update it if it is — and still no tirggers.

Answer 3

Since you have full control over the process, OPTION 3 seems easiest to me.

record_status and last_updated_on into the CA table, and include these fields in your INSERT statement.

If your requirement is to change the record_status and last_updated_on every time a change is made to the CA table, then an on UPDATE trigger could be used.

However, you say you have full control over the business logic layer. So, changing the UPDATE statements to update those fields as well is again, the easiest way.

Unless, you need to track the hourly change in status over time. Then a separate table is required to hold those changes.