Software application is an amusing company. For example, you have Account A and Account B. You withdraw from one and add to the other inside of a transaction. Except that’s not how accounting works. You should probably do this in a various way.It is not just possible to compose a database application that never ever issues an update or a delete, but frequently useful. Developers of IoT (Web of Things) applications do this all the time. Devices send out time series data, usually status information, which goes in a table with the time stamp. Despite whether you are using a traditional database like Oracle, a more recent distributed SQL database like CockroachDB, Yugabyte, or MariaDB Xpand, and even a NoSQL database like MongoDB, the method is basically the same.Consider a table like this: Client id BIGINT(0)UNSIGNED AUTO_UNIQUE NOT NULL, name_given TINYTEXT, name_middle TINYTEXT, name_family TINYTEXT, email [varchar] TINYTEXT, dob DATETIME An upgrade is needed if
the client changes their e-mail or household name. Nevertheless, this indicates history is lost. An upgrade can rationally be thought of as an erase and an insert. Another way of doing it would be something like: Client entry_id BIGINT(0) UNSIGNED AUTO_UNIQUE NOT NULL, entry_date TIMESTAMP NOT NULL, id BIGINT(0)UNSIGNED NOT NULL, name_given TINYTEXT, name_middle TINYTEXT, name_family TINYTEXT, email [varchar] TINYTEXT, dob DATETIME The entry_id ends up being the unique secret for
the row, however the id is the key identifying that special person. To find someone’s existing name and email you would release a query like: choose … from Customer where id=1 and entry_date=( select max(entry_date )from consumer where id = 1)This query pulls the last entry for the consumer where id equals 1. To change the client’s email or household name you simply place a new row with an id of 1 and a brand-new row.(Note: Do not do max(entry_id )if the id is an auto_unique and not a series.) This has a clear downside because you need a subquery and another join. However, it has a clear benefit if, for instance, some communication or other data returns with the old household name or the firm gets an e-mail from an old e-mail address. Another advantage is that it dates details. In some jurisdictions information is needed to be purged upon demand or based upon the date it was recorded. This style makes that easy.There are some other issues to think about. Think about the problem of discovering customers who were shipped a specific item. You may have Customer, Order, Shipment, and Shipped_Item tables. Assuming you desire just the “existing”record which all of the tables are versioned, you end up with at least three subqueries. Instead you can have a more standard structure like the first customer table meaning however problem inserts on erase with an archive table: Customer_Archive p> NULL, name_given TINYTEXT, name_middle TINYTEXT, name_family TINYTEXT, email [varchar] TINYTEXT, dob DATETIME The advantage of this is that just the current record remains in the Consumer, Order, Delivery, and Shipped_Item tables and the number of joins is reduced.
Plus it maintains a search advantage over audit logs. There is a downside to queries that browse current records in mix with history.In any functional system, one does not desire history to obstruct of performance. While the application may never delete, some system process might need to purge records older than a provided date. Moreover, it may make good sense to feed an analytical database some kinds of data.Updates and deletes get rid of history. Despite the structure you selected, when creating a database schema, it is sensible to take a note from double entry accounting and consider maintaining history in addition to the existing state
. This principle is not for every application, however it is not merely for IoT or accounting applications. Copyright © 2023 IDG Communications, Inc. Source
