For example, if we do Analytics for the record page_type, item_id, date, pageviews, timeOnPage.
They seem to have several ways to avoid this. Is there an automatic way?
create an index in the fields that uniquely identify the record, for example [page_type, item_id, date], and make the index unique so that when adding the same record it rejects it.
or, make a primary index higher, which is unique if the DB or wireframe supports it. In Rails, usually ID 1, 2, 3, 4 is the main index.
or request a record with [page_type, item_id, date], and then update this record if it already exists (or do nothing if the pages and timeOnPage already have the same value). If the record does not exist, insert a new record with this data. But if you need to query the record this way, it looks like we need an index in these three fields.
Insert new records all the time, but when you ask for values use something like
select * from analytics where ... order by created_at desc limit 1
i.e. get the newest created record and ignore the rest. But this seems like a solution for 1 record, but not so realistic when it sums up the values (making aggregates), for example, select sum(pageviews)or select count(*).
Is there also some automatic solution besides using the above methods?