What is a single-table relational database

Question

I searched Google for the definition of a single-table relational database and found nothing but redirection to other database models. What's up? Why is this concept confusing? Every relational database is also a single-table relational database. The key difference between a standard RDBMS presentation and its single-table presentation is that relationship management is in the data and not in a tool to manage tables.

As seen in the image above, the single-table form of the RDBMS is a pivot of a normal table. Each value is identified by location in TableID, ColumnID and RowID. Just a two-dimensional matrix of foreign keys. The value also needs to be typed, a function normally handled by the RDBMS table management tool (this may or may not require multiple value columns where only one location will be not null).

The single-table form of the database has unique advantages over standard table form. As shown, semantics is removed. Relations are handled by data description, not by a table tool. This makes the ontologies of relationships an input to the stored procedures that return data to various applications using the data. You can use hierarchical structure, graph database triplets or whatever. Of course, you can always pivot the tables back to their original form.

For example, for the last 18 years I have been using a form of the single-table RDBMS that records the history of building operations (VAV w/Rht is a type of air conditioning device).

I have added four columns to the single-table presentation. The Language column allows table and column names to be presented in the language of choice. This allows the database to work in any language. EntryDate, IsActive and UserID allows edits and deletions (marked inactive, not removed) to be attributed to a user according to when the change was made. This makes the database recordings immutable on a field-by-field basis.

Am I missing something? Is there a hidden flaw I haven't encountered in 18 years? This is a serious question. I am working with an international association dedicated to working with engineers to build and control better heating and air-conditioning systems. Think climate change. They are looking to define a building data exchange standard and I think a single-table RDBMS will eliminate many of the challenges they face.

Answer 1

It is not desirable from a performance or concurrency point of view to make an operation which should logically impact only one table impact every table in the database. For example, consider adding a new column with a default value to a traditional table with one billion rows. With SQL Server, this is usually a very fast metadata operation. With your structure, you would need to insert at least a billion rows into your master table. What exactly is going to happen during that billion row insert? Will the insert escalate to a table level and block all other processes? Will the insert not escalate and will you hold page locks for a billion rows? Will the insert be broken up into separate transactions so the equivalent of ALTER TABLE ... ADD [COLUMN_NAME] is no longer an atomic operation?

That's also not the worst case. Consider adding an index on Standard Edition to your master table. The index building process will need to scan your entire table while it holds a Sch-M lock. Adding an index will block any kind of access to effectively the entire database.

Answer 2

Am I missing something? Is there a hidden flaw I haven't encountered in 18 years?

This looks to me to be based around the EAV pattern (or “property bag”), potentially with all the downsides that can have.

The key difference between a standard RDBMS presentation and its single-table presentation is that relationship management is in the data and not in a tool to manage tables.

How is this relationship management enforced? If you are not using the database for that then you don't have a relational database, you are just using a relational database system to hold a property bag on steroids.

I think a single-table RDBMS will eliminate many of the challenges they face.

What issues are those that you think this model will solve better than other constructs in an RDBMS?