Dynamic Relational

"We should not impose regularity where it does not exist." --TedNelson

Giving relational dynamic character that is roughly analogous to dynamically-typed or type-free programming languages. (The "Oracle model" is more analogous to static or strong-typed languages.) Column existence, and perhaps table existence can also optionally be dynamic. Thus, a DBA is optional. Such a tool could be useful for RapidApplicationDevelopment, and experimental or demo applications.

(Topic title not to be confused with DynamicSql.)

MultiParadigmDatabase describes the basic ideas behind DynamicRelational, but adds (or subtracts) features to accommodate OOP. Since the mixture of dynamism and OO features tended to confuse people, I felt a dedicated topic was in order. (Dynamism seemed necessary there to allow such a database to accommodate more paradigms.) Thus, for a working definition of dynamic relational, lets exclude these features from MultiParadigmDatabase:

But these features are kept:

Optional features include:

TupleDefinitionDiscussion argues that dynamic records don't really violate the "tuple" requirement of relational because one can conceptually view such as an ever-changing traditional "rigid" rectangular view. It may hurt your brain a little to think of it that way, or burn CPU to get a full rectangle, but it still manages to keep it technically honest to relational rules. Relational does not dictate that schemas never change.

Tips for adapting ODBC drivers and/or SQL for dynamism are given in links near the bottom of MultiParadigmDatabase.


SqLite seems the closest existing product to this idea, although only the cell "types" are dynamic, not the schema. Schemas must be declared and updated explicitly.

Some also suggest that ProLog creates or infers ad-hoc relational or relational-like structures.

Yeah dbdebunk.com really thinks highly of SQLite... http://www.dbdebunk.com/quotes2004.html

Comparison operator discussion and examples moved to ComparingDynamicVariables.

This article describes DynamicRelational-like techniques that are becoming popular:


But, it does not call the alleged alternatives "relational". One possible reason can be seen in one of the examples that violated uniqueness in column name per record, such as having two colors for a car. If they instead supplied "color_1" and "color_2", then the unique column name requirement would not be violated, keeping it true relational (or at least closer to it). I wonder if they have a good reason to abandon unique attribute names per row. And some of them look more like an EssExpressionDatabase (rows of nested lists), which is a curious idea, but not "relational". --top

Examples of optional/incremental "lock-down" constraints:
Possible uses:
PageAnchor Comparison-Symbols

To use SQL with DynamicRelational, explicitness in types for comparing etc. is strongly recommended. There are different ways to go about this, as described in ComparingDynamicVariables. As a draft suggestion, I'll propose "type symbols" borrowed somewhat from old-style BASIC.

  WHERE columnA $< columnB    // $ = string compare (resembles an "S" for String)
  WHERE columnA #< columnB    // # = numeric compare
  WHERE columnA @< columnB    // @ = date/time compare (at)
  ORDER BY $columnA, #columnB, @columnC  // first on string, second on numeric, third on date/time
I propose these symbols be required for all comparisons with dynamic columns to prevent static-database SQL habits from leaking into the dynamic version. If the type of column(s) participating in an expression is known, (explicit types via DDL are optional, but possible) then the symbols would not be required. Thus, if one imports data and schema from a traditional RDBMS, which would give each column an explicit type, then the SQL originally used in that traditional RDBMS would work as-is (barring dialect differences) and not need type symbols. If symbols are given that conflict with explicitly-defined column type(s), then a coercion of types to that of the operator (symbol) is attempted; and error-out if not convertible. Thus, "numberColumnA $> numberColumnB" would compare with string versions of the numbers, equivalent to "toString(numberColumnA) > toString(numberColumnB)" in a traditional RDBMS.

The two system-required and system-defined columns, "entity" and "row_id", are defined as string and numeric types respectively, and are not normally changed by users nor the DBA. (I won't rule out a low-level custom tweak.) Thus, explicit type comparison indicators are not needed for comparisons such as "...WHERE row_id=7 AND entity='foo'". ("Entity" is essentially the table name and thus is not normally referenced as an explicit column, but typically comes from the FROM clause. An advantage of column-based referencing is that the table can be "computed" at query run-time, adding to dynamicness.)

If two columns are of predefined types (via DDL or system-defined), then comparisons of such columns don't need indicator symbols.

  ...WHERE numberX > numberY  // both are predefined as numbers
  ...WHERE numberX #> thingA  // a dynamic column is involved such that symbol needed

It would be simpler to just require that all comparisons have explicit indicators rather than deal with the mental juggling of considering whether an indicator is needed or not for a given query. However, this conflicts with the gradual-evolution-to-traditional-RDBMS goal (of a given shop's tables) stated above. I'm kind of torn between which direction to go in: It's simplicity versus compatibility.

Perhaps we can distinguish between these two sub-categories of DynamicRelational:

   I would prefer morphing a shop's traditional RDBMS into a DynamicRelational one -- JonGrover
The second is probably easier to implement.


See also: ObjectsAreFromMarsTablesAreFromVenus, DoesRelationalRequireTypes, MaspBrainstorming, NoSql, MultiParadigmDatabase, MultiParadigmDatabaseQuestions (has samples)

CategoryRelationalDatabase, CategoryLanguageTyping, CategorySpeculative, CategoryInformationOrientation

View edit of December 5, 2014 or FindPage with title or text search