-1

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.

RDBMS seen as single-table and standard

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). Single-table RDBMS Historian

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.

Improve this question
9
  • How many rows are in this? What sort of throughput can it sustain, on what sort of hardware?
    – Michael Green
    Mar 31, 2022 at 18:42
  • This part isn't really true: "relationship management is in the data and not in a tool to manage tables" in a RDBMS by definition is supposed to be stored in the catalog - this is Codd's Rule 4: en.wikipedia.org/wiki/Codd%27s_12_rules
    – Cade Roux
    Mar 31, 2022 at 19:16
  • Michael, I have implemented this in a personal copy of SQL Server, on-prem for a customer managing energy and facility control information across state-owned facilities (updates 400k values daily) and in Azure in the cloud.
    – Gregory Cmar
    Mar 31, 2022 at 20:50
  • Cade, a single-table RDBMS does not violate Codd's Rule 4.
    – Gregory Cmar
    Mar 31, 2022 at 20:54
  • @GregoryCmar, because you are a new contributor, I have to be nice. Otherwise I would ask if you are a troll.
    – Gerard H. Pille
    Apr 1, 2022 at 11:04

2 Answers 2

Reset to default
2

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.

Improve this answer
3
  • You misunderstand. Every table in every RDBMS has a pivoted equivalent. When you pivot any set of tables to their single table equivalent you remove any fixed relationships and get to retrieve the data according to any desired ontology. I do puzzles. Not recognizing a pivot exists is like trying to solve a crossword with only the across clues.
    – Gregory Cmar
    Apr 1, 2022 at 19:38
  • @GregoryCmar you misunderstand. Your pivot operation creates an EAV pattern, with the problems inherent with that pattern, potentially including the ones Joe discusses if you store this EAV model in a single table in a relational DB.
    – David Spillett
    Apr 1, 2022 at 23:04
  • David, It took me a bit to understand what Joe was talking about. In a single-table view no row is added until data is entered. Adding a new column, therefore, remains a fast metadata operation. One additional point, there are the same number of logical tables no matter how you view them. Single-table means each row contains only 1 record in the database, other columns in the single table describe where the record is found and what it is directly related to.
    – Gregory Cmar
    Apr 2, 2022 at 1:26
1

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?

Improve this answer
6
  • David, thank you for your comments. 1) I used the EAV pattern as a starting point. But this is not EAV. 2)The relationships are enforced directly in your query, not indirectly with table tools. This allows having multiple ontologies defining how row data is related. 3) I did not mean it solves it better, I meant it makes certain operations easier, such as providing data outputs labeled in the language spoken by the user. I point out again, every RDBMS solution has an inherent single-table representation that fulfills the same business purpose. Think of it like a Fourier transform.
    – Gregory Cmar
    Mar 31, 2022 at 20:36
  • 1
    Many would argue that EAV with decorators is still EAV. EAV is not always an anti-pattern, but you need to be careful of its downsides even when it is a good tool for the job. As you are not using any of an RDBMS's tools to model & enforce referential and type integrity, you might find other database systems are much more optimal for this design.
    – David Spillett
    Apr 1, 2022 at 9:28
  • I am using the RDBMS to collect data from equipment operation, energy consumption, IoT, etc. This systems data needs to be organized according to how the devices are connected with each other as well as how it interacts with metered data. This data is also related to architectural and engineering data which may or may not be available at the time of collection. All of this needs to meet regulations that are changing or not yet written. If nothing else, the single-table RDBMS allows schema and relations communications among peers to be vendor independent.
    – Gregory Cmar
    Apr 1, 2022 at 20:19
  • I'm not denying that you are using an RDBMS to store the collected data, I'm saying that you are not storing it in a relational manner and as such another type of database may be more optimal. You could use a helicopter gunship as a planter for your geraniums if you want, but you would still be gardening not piloting a VTOL attack aircraft and plant pots are likely to be a more cost effective solution.
    – David Spillett
    Apr 1, 2022 at 22:58
  • David, This isn't an April Fools joke, but I like the joke. You seem to be suggesting that a single-table RDBMS is in violation of Codd's Rule 12. My example shows how to change the language of a single column name at query time. How does it violate? What I am saying is pivoting your view of the data doesn't violate any of Codd's rules and makes it easy to expose additional RDBMS functionality.
    – Gregory Cmar
    Apr 2, 2022 at 1:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.