sql - DB schema for updating downstream sources? -

i want table sync-able web api.

for example,

get /projects?sequence_latest=2113&limit=10  [{"state":"updated", "id":12,"sequence":2116}, {"state":"deleted" "id":511,"sequence":2115} {"state":"created", "id":601,"sequence":2114}] 

what schema achieve this?

i intend postgresql django orm, uses surrogate keys. presence of orm may kill answers unions.

---

i can come half-solutions.

1. i have modified_time column, cannot convey deletions.

2. i have table storing deleted ids, when returning 10 new/updated rows, return deleted rows between them. works when latest change insert/update , there moderate number of deleted rows.

3. i set deleted flag on row , null rest, kinda bad schema design set columns nullable.

4. i have table stores id, modification sequence number , state(new, updated, deleted), table maintain , setting sequence numbers cause contentions; imagine n concurrent requests querying latest id.

if you're using orm want simple(ish) , if you're serving data via api want quick.

to go through suggested options:

1. correct, doesn't you. have deleted flag in main table though.
2. this seems quite random way of doing , breaks insistence there no union queries.
3. not sure why need null rest of column here? benefit bring?
4. i advise against having table has modification sequence number. either means you're performing lot of analytic queries in order find out recent state or you're updating same rows multiple times , maintaining table same pk normal one. @ point might have deleted flag in main table.

essentially design of api gives 1 easy option; should have in same table because data being returned through same method. follow point 2 , wolph's suggestion, have deleted_on column in table; making like:

create table my_table (       id ... primary key    , <other_columns>    , created_on date    , modified_on date    , deleted_on date      ); 

i wouldn't bother updating other columns null. if want ensure return no data create view on top of table nulls data deleted_on column has data in it. then, api accesses table through view.

if really, really worried space and volume of records and perform regular database maintenance ensure both controlled maybe go option 4. create second table has state of each id in main table , delete data main table. can left outer join main table data. when there no data id has been deleted. honestly, overkill until know whether require it.

you don't mention why you're using web api data-transfers; but, if you're going transferring lot of data or using internal systems might worth using lower-level transfer mechanism.