Interesting problem I can't wrap my head around - Help

[envo]

Hi,

So I have this tinymesh radio network that's sending data to a tablet using a broadcast. On the tablet, I parse the information and save it in a SQL Express database for the UI to access. The tables it writes to is deemed as "read-only" (not marked as such in any way, but data can be deleted/refreshed by the radio portion at any time, so can't be edited)

The tablet should be able to edit some data.

My thinking was to create "history" tables and read columns that can be edited from there so it is kept consistent on the tablet. The idea is to use triggers on the "Read only" tables to populate the History table when changes come through.

One edit is on a comment field on the header of the information on the table (it then sends back, via a direct message, that this has changed) and is pretty straight-forward:

select Header.ID, Header.Customer, HeaderHistory.Comment
from Header
left join HeaderHistory on header.ID = HeaderHistory.ID

The mind-bending part (at least in my current state of mental-wellness, lol), is the LineItems table;

LineItems has 3 defining ID's that are autonumber on the master (that is sending the radio packet over the network to the tablet). A requirement is to change the product of that line (which is easy, as above), but what makes it difficult is adding or removing a line item.

LineItem table
ID (auto number)
VID (auto number)
VVMID (auto number)
ProductID (technically an autonumber but is from the Products table and can be edited)

The history table idea in this instance won't work as well (or would it?). Especially for adding or removing line items. I can't generate autonumber's on my end and have to wait for the radio network to respond with an updated broadcast before knowing those.

Currently, to make the UI work, it just add's a negative number in sequence, and works nicely, but now with the integration of the radio network, it complicates things slightly.

If I add (or remove) a line item, I can show it immediately on the UI if reading only from the "History" tables, but when that data comes back for an update on those tables, they'll be seen as new records being inserted considering the ID's, so I'm not sure I'll show 3 line items (for example) instead of 6, that's on the tablet.

I'm wondering if I should ditch the hybrid model entirely and just read from the history tables on the UI to keep it consistent, and when an update on the LineItems come through, to delete/re-populate them each time?

Any thoughts/inputs would be amazing! My head is a bit cloudy currently.

 

[2026]

Sounds like the "history" tables are acting like a local cache and when you update your local "cache" you are unable to match the master data records (as their ID may be different) to the local store and thus you end up with duplicates on the local store?

Have you tried giving each line item UUID column, pop a unique constraint on that column (and just ignore insert fails where the constraint is violated)?

 

[retromodcoza]

What in the heck? I'm not sure I understand what you're doing. You may need to explain it more clearly. However...

I've heard the idea of "history" tables before. It usually means your DB design is bad.

The key to this whole thing is "deleted/refreshed". This is a mesh network. I assume a node in the network is "checking in" every so often , giving you some sort of stats. You're then storing these stats. Now you want to edit the comments column for the stats and that info gets sent back to the node?

Dont delete/refresh data. Timestamp it , assign the nodes ID to it and store it. Then adjust your queries accordingly.
INSERT INTO Header (Auto HeaderID, Time , NodeID, CustomerID, DataID)
INSERT INTO Data (Auto ID , HeaderID, Time, SomeData , SomeData2 , SomeData3 , Comment)

--Get the latest check in data from the node
SELECT TOP 1 * FROM Header
OUTER JOIN Data
WHERE Header.HeaderID = Data.HeaderID
ORDER BY Date DESC

--Update the comment for the latest check in
UPDATE Data
SET Comment = "Some Comment Here"
WHERE HeaderID =(Select TOP 1 HeaderID FROM Header WHERE NodeID = "ThisNodeID" ORDER BY Date DESC)

This is a shot in the dark. Not sure if it will help.