We have a report store with a denormalised flat table that stores identical data to a multi-table model in a different database.
Flat table (example):
| col 1 | col 2 | col 3 | timestamp |
|-------|-------|-------|-----------|
| val1 | val2 | val3 | 1/1/1990 |
| val1 | val9 | val3 | 1/1/1990 |
In multiple tables:
| id1 | id2 | timestamp |
|-----|-----|-----------|
| 001 | 111 | 1/1/1990 |
| 001 | 112 | 1/1/1990 |
| id1 | col 1 | col 3 |
|-----|-------|-------|
| 001 | val1 | val3 |
| id2 | col 2 |
|-----|-------|
| 111 | val2 |
| 112 | val9 |
There are several old reporting queries that we would like to port over to the new flat table without having to rewrite them all up front - there are many of them and they are complex.
Is there a way of writing Views that can simulate a set of relational tables from the single flat table, so that the old reporting queries work without modification?
HereI create dynamical IDs. You could also initialy make that table with fix keys, and always when adding or removing a row in the flattable do the same with the key here. Otherwise instead of Groub by use the OVER statement.
CREATE VIEW multitabkey AS
SELECT ROW_NUMBER() as key, col1, col3
FROM flattable
Group by col1, col3
WARNING: those keys are not persistent: if you delete the first row, all others get their id one smaler than before. You have dynamic IDs, but they are consistnet.
If you have a translation for your Keys you can use them as following:
CREATE VIEW multitabone AS
SELECT f.timestamp
FROM flattable as f
JOIN multitabkey as m ON m.col1 = f.col1 AND m.col3 = f.col3
Group by col1, col3
I assumed col1 , col2 are together a natural key.
As mentioned, this is a workaround, your DB is not in 3rd normalform what can cause inconsistency.