This is a continuation of the saga of my current project. Here is an outline of what I have:
A. SQLite database with 3 tables. One table will probably grow to 300 or so rows and about 25 columns. It holds data about electronic components, their part numbers, who makes them, and so forth. One table might end up with 200 or so rows in 4 columns; it lists the parts used in each manufacturing “assembly”. The final table has 15-30 rows and 4 columns; it provides a sort of dictionary that translates certain information between the first two tables and will rarely change.
B. A database editor that contains a list box for each db table. There is a strict 1:1 relationship between the listboxes and the db tables. Each listbox is populated directly from the corresponding table.
I have figured out how to edit the listboxes. Now, comes the question of how to save the edited information back to the database. I see several possible strategies:
No matter how much or how little editing has been done, blindly save back every row and every column of every table back to the database.
Save just the edited tables. Save the whole table, but only those tables that were edited. Most of the time, this will only be one table but the small “dictionary” table would almost never be written.
Save just the edited rows in the edited tables. Rarely will more than a few dozen rows be involved out of several hundred. Book-keeping starts to increase but for a full row, a “dirty” flag can be kept as a row tag, so the book-keeping should not be too onerous.
Save just the edited columns of the edited rows in the edited tables. If I start out with a row read from the database and update only those edited columns, (probably) no write time will be saved because (probably) a full row will still be written. Book-keeping is now a more complex task. I am mildly concerned about writing from the db to the the listbox, then writing back listbox cells that SHOULD never have changed (as in case 3).
So, through your sharp eyes and experience far exceeding mine, what would be the (most) appropriate strategy in your opinion? Or is there another more useful strategy that I missed?
If you’re going to go with #3 I’d suggest having an integer version_number field for each record. Before you write an edit back to your table you can check the version number is the same as when you read the record out, then increment it as part of the update process.
I appreciate you’re only expecting your project to be single-user but circumstances change…
If you use ActiveRecord you could call Save on every row and only the ones that have changed will actually be saved. ActiveRecord / ARGen will save you huge amounts of time.
Put the fixed length items, such as floats, booleans, at the beginning, then variable length items such as integers (0 to 8 bytes), strings next. Blobs go at the end. AIUI, when a row is updated, only the columns that are changed are written out, if their aggregate length is the same or shorter. If longer, the whole row is written.
Thanks for the information and suggestions. I will check out ActiveRecord. That might be the final straw that pushes me to update (sale providing good incentive). Do not know what “normalization” is in this context.
AIUI, typically it means making sure you don’t have the same information item in more than one table, meaning you might have to update it in two places. Instead you keep it in a table that’s relevant to that info item. Thus you might have a customer table, and an invoices table, linked together by a CustomerID. You don’t keep the customer address in the invoices table, you keep it (once) in the Customers table, and if processing an invoice, and need the customer’s address, go look it up in the other table. Thus, one customer, many invoices (potentially) but the address is only once in your entire database.
That’s just a simple example. But you do this at the design stage as the more code you write, the harder it would be to fix that later.