I have a question regarding historical records in database. I heard that it is possible, to store historical data of records, inside same Table (not two tables with relation). I'm not sure, if this actually works or even exist, so ...
1.Is it possible ? Which database can do that ?
2.In case, that I have to use those old records, is it accessible via script or application like PowerApps ?
3.How much complicated would it be to create/manage that database?
4.Does Azure have that kind of solution ? If not, maybe some other cloud providers (without VM solutions)?
Why I need that ? I'd like to create a solution for Company1(it's for training purposes) where employee will enter data of Client1 with some values, and send them to Company2, Company3 and Company4 and then check, which one of them has best eg. price or offer. Than it would save that data in database, and repeat same process for each hour. On the end of the week, it would generate file with Company with most best offers. I also want to see each of historical records.
This is only for training, so tell me if there's better DB solution :) Thanks !
If I'm understanding your question correctly, this is actually very simple to accomplish in any database. The "historical" data is identified by a "lifespan" (not the technical name for it). This would translate to a "born" and "died" record values or "begin" and "end" datetime columns.
Using Azure SQL as an example, create your table with the begin & end datetime fields:
create table company_prices (
company_name nvarchar(55),
product_name nvarchar(55),
price money,
begin_timestamp datetime,
end_timestamp datetime )
When you insert your records for the first time, use a date value for the begin datetime field and leave the end datetime field null:
insert into company_prices
select 'Company A', 'Widget 1', 13.57, getdate(), NULL
union
select 'Company B', 'Widget 2', 24.68, getdate(), NULL
union
select 'Company C', 'Widget 3', 3.69, getdate(), NULL
Now you have three "active" records. They are active because they do not have an end datetime assigned.
The next time you want to collect your prices, update the previous or historical record by assigning an end datetime value before inserting any new records.
update company_prices
set end_timestamp = getdate()
where company_name = 'Company A'
and product_name = 'Widget 1'
and end_timestamp is null
Now, when you insert your new price you'll have a historical record and a current/active record for Company A, Widget 1.
company_name product_name price begin_timestamp end_timestamp
Company A Widget 1 13.57 2020-11-05 03:19:11.027 2020-11-05 04:19:11.030
Company A Widget 1 13.95 2020-11-05 04:19:11.030 NULL
Company B Widget 2 24.68 2020-11-05 03:19:11.027 NULL
Company C Widget 3 3.69 2020-11-05 03:19:11.027 NULL