butlerm

> If that's the case, you will have an overhead anyway, the only question being whether it's at the DB level or at the application level.

Inserts and updates do not require referential integrity checking if you know that the reference in question is valid in advance. Common cases are references to rows you create in the same transaction or rows you know will not be deleted.

If you actually want to delete something that may be referred to elsewhere then checking is appropriate of course, and in many applications such checking is necessary in advance so you have some idea whether something can be deleted (and if not why not). That type of check may not be race free of course, hence "some idea".

Referential integrity problems usually happen due to missing deletes, improper deletes, or references that should be cleared.

The overhead of checking for the existence of referred to records in ordinary inserts and updates in application code is unnecessary in most cases, and that is where the problem is. Either you have to check to have any idea what is going on, because your key values are being supplied from an outside source or you should be able write your application so that it does not insert random references into your database.

If you actually need to delete a row that might be referred to, the best thing to do is not to do that, because you will need application level checks to make the reason why you cannot delete something visible in any case. 'Delete failed because the record is referred to somewhere' is usually an inadequate explanation. The application should probably check so that delete isn't even presented as an option in cases like that.

It is quite common for modern databases to have multiversion concurrency so that writers do not block readers. If you do not your transactions should either be awfully short, you should be prepared to wait, or you should implement dirty reads (which are quite common in any case).

Orphaned detail records are usually inconsequential, like uncollected garbage. References to anything with an optional relationship should use outer joins as a matter of course. If you delete something that really needs to be there you have a problem, which is one of the reasons not to delete rows like that, ever, but rather to mark them as inactive or deleted instead.

Typically you look for orphan rows - the sort of thing ON DELETE CASCADE was invented to prevent. Another thing to check for are records that need to exist but should have references cleared when something else is deleted, e.g. ON DELETE SET NULL. And the third thing is ON DELETE RESTRICT.

You can check for the first two of those things after the fact, and they are relatively benign. In many cases it will make no difference to application queries, especially with the judicious use of outer joins, which should be used for all optional relationships anyway.

If you need ON DELETE RESTRICT application code should probably check anyway, because otherwise you have unexpected delete failures with no application level visibility as to what went wrong. That can be tested for, and pretty much has to be before code that deletes rows subject to delete restrictions is released into production.

As far as race conditions go, they should be eliminated through the use of database transactions. Another alternative is never to delete rows that are referred to elsewhere and just set a deleted flag or something. That is mildly annoying to check for however. Clearing an active flag is simpler because you usually want rows like that to stay around anyway, just not be used in new transactions.

Large ERP systems do that sort of thing as a matter of course and have for decades now. It does require careful planning and design. I mean AR / AP / scheduling / manufacturing / inventory and so on.

The main downside of splitting everything into isolated databases is that it makes it approximately impossible to generate reports that require joining across databases. Not without writing new and relatively complex application code to do what used to require a simple SQL query to accomplish anyway.

Of course if you have the sort of business with scalability problems that require abandoning or restructuring your database on a regular basis, then placing that kind of data in a shared database is probably not such a great idea.

It should also be said that common web APIs as a programming technique are much harder to use and implement reliably due to the data marshalling and extra error handling code required than just about any system of queries or stored procedures against a conventional database. The need to page is perverse, for example.

That does not mean that sort of tight coupling is appropriate in many cases, but it is (typically) much easier to implement. Web APIs could use standard support for two phase commit and internally paged queries that preserve some semblance of consistency. The problem is that stateless architecture makes that sort of thing virtually impossible. Who knows which rows will disappear when you query for page two because the positions of all of your records have just shifted? Or which parts of a distributed transaction will still be there if anything goes wrong?

If you do not particularly care about performance or have a great deal of headroom then database enforcement of referential integrity is great. Alternatively you could just write test cases to check for it and not pay the severe performance penalty.

The other major downside of database enforcement of referential integrity is the common need to drop and re-create foreign keys during database schema upgrades and data conversions.

Perhaps someone should define a new C compatible threading API to allow C libraries (including glibc or a wrapper around glibc) to work with something other than native pthreads. Such as goroutines or Java threads and so on.

It is basically impossible to write general purpose software like compilers, word processors, and layout engines without doing heap allocations. That means either pointers or references, which are difficult to distinguish if you do not engage in pointer arithmetic.

Any C++ program that does not do heap allocations either uses arrays as a substitute for the same thing or isn't a general purpose application.

It is generally speaking difficult to make an efficient implementation of the compiler and/or the virtual machine for many memory safe languages without writing it in a more efficient, statically compiled language like C, C++, or Rust. And that is to say nothing of software like operating system kernels and browser engines. So perhaps Rust will gradually take over the world there.

That would be convenient, and some programming environments have support for that kind of thing already. A hierarchical object valued expression would also be convenient in a different way.

The main application for this is where you have detail data for parent records in a snowflake pattern. In that case SQL tends to require a ridiculous number of queries, where common formats like JSON and XML are capable of transferring hierarchical data like that in a single response. That is a major weakness of SQL and the common inability to return a hierarchical set of relations in one response in particular.

Also, running a ridiculous number of queries in parallel is not practical on many databases due to per connection overhead, a problem that is so severe that many databases have internal many-to-one connection demultiplexers already, i.e. they have N execution engines for M connections. That should sound familiar to those who are familiar with threading models.

It is possible for telcos to provide point to point or point to multipoint layer 2 permanent virtual circuits (PVCs) from customers to providers or branch offices to home offices, and it used to be common. Frame relay and ATM (asynchronous transfer mode) were quite popular technologies for that.

In many if not most areas in the United States DSL (digital subscriber loop) based Internet access was originally delivered over PVCs established through a layer 2 ATM network. There were interesting problems with that so PPP or PPP over Ethernet is more common these days, even when the telco no longer really lets anyone compete with them in the provision of broadband Internet access services at layer 3 over the network they maintain thanks to a rather convenient federal court decision.

Layer 2 mostly Ethernet access over VLANs (virtual local area networks) to a chosen provider does live on in certain mostly municipally owned multi-provider networks though, and in some countries that is normal, although usually with the incumbent telco or ILEC (incumbent local exchange company) installing and maintaining the last mile to homes and businesses rather than a municipal operator as in some parts of the United States. Either way more than one provider can provide layer 3 Internet service on the same physical facilities that way, with layer 2 (e.g. switched Ethernet) virtual lans or virtual circuits operated by one company or municipality.

Making your code run quickly will not help if your software architecture is inefficient or optimized against the customer as so many web applications are these days, for example. There are many commercial web pages that appear and approximately ten seconds later clicking on a button will actually do something. I am not sure why that it considered acceptable, but customer experience doesn't seem to rank very high on the list of priorities.

There is a considerable divergence of opinion on that subject. In my view, C++ isn't remotely suitable as a programming language for someone without a healthy understanding of C. Perhaps a dialect of C++ could be developed that was more of a cousin to Rust, but C++ as we know it is a C like programming language with a very large number of features added and all the ways to fail just like a C program does. There are real world advantages of course, but it is not a language for the faint of heart, not even close.