sqlclient-serversocial-network-friendship

Need help in designing friendship request SQL table

I have SQL table for friendship requests. Table is on server - clients are mobile phones

Table:
Key = index, int, auto increment
C1 = userA_ID
C2 = userB_ID
(C1, C2 = unique)
C3 = status (pending, accepted, declined, unfriend....)

For better practice in the mobile, for not querying all the time the entire friendship request table I store a table also in the local DB on the device. Once table was queried - it is stored in the local DB, so if nothing was changed device does not need to do queries from the server.

So - in the app init... (or every time entering mailbox of the app of the device) Device is asking server to know if there are new messages and friendship request updates... For messages it is simple - since each new message has a different ID and I search on server all messages where id > stored id.... But for friendship request - I update the line in the server's DB so the index is still same index...

I thought of two options:

  1. Add Date column and check for updates done later than last check (last check will be stored in the local DB). I prefer to do the comparison on indexes and not on a date

  2. Get all friendship requests entries of the user when app inits and do compares locally

Any recommendations? Better ideas?

Solution

  • You could add a bit column that tracks if the request has been viewed, and set it to true when you retrieve the request, then filter on that column. That would probably be slightly better performance than storing the date, and the date last retrieved, and comparing the two each time.