I have claims transactions where there might be adjustments, but the last record represents the current state.
I thought about putting the last record into a separate table, but wondered about creating a view of some sort.
in the attached, the green columns (G-M) represent the business key, so I have to recognize all transactions that match this key, then present the last one received.
Can I create a view that would show the last record ? If so, this seems like it would be a performance nightmare. Record count is about 1,000,000 per year, so far ~7M records.
It seems like from a data integrity point of view, it's preferable to use the view and not have to monkey with another table, but I guess the performance is the driver.
The logic for this is not simple, but
create view my_tables_view as
select field1, field2, field3, field4, field5, field6, max(seq_no) as max_seqno
create view_2 my_current_records as
select * from my_table
where seq_no in (select max_seqno from my_tables_view)
then the user just happily runs a query against "my_current_records"
but wouldn't this be a performance nightmare ?
The alternative is have a table with current records, and then update it when a newer transaction arrives. That seems clean but wanted to get an experts opinion . . .