"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 dymnamic.
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
- Optional table designation (entity attribute). Tables are assumed mandatory here.
But these features are kept:
- Values are essentially stored as strings, with an almost PerlLanguage-like typing system. There is no "side flag" or required formal schema to indicate "type" of a column. Explicit types via schema's can be optionally added as needed, as described later. (As in Perl, this may require more explicit operators, such as not overloading "+" to mean both addition and concatenation. See ComparingDynamicVariables.)
- There are no explicit nulls. If an application wants to treat zero-length strings as null, or some other symbol, it can.
- There is no way to distinguish between a "missing" column and an empty, zero-length column. (This is perhaps a debatable feature, but I personally like it for reasons I'm still trying to articulate. One can use one or more spaces to indicate existence if one wants.)
- Dynamic schema via "Create-on-Write" - Columns and tables can be created just by putting or updating data in them. No explicit schema alteration is required. These "non-limits" can be incrementally revoked, per below. (The "non-missing-distinguish" rule above is what gives this behavior to columns.) An insert or update is sufficient to "create" a column and/or table. There would never be a "no such column" error (and perhaps tables). Creating indexes would perhaps be the only explicit "Create" command in a dynamic environment (if not automatic). The need for DDL commands would thus be greatly reduced.
- An operation to find all columns in a given schema has to do an entire table scan to find those columns with values. (However, such an action is generally against the philosophy of DynamicRelational.) One can view each row as an independent map (dictionary array). The schema for the table is simply the unique union of all map keys of the table rows.
- There may be ways to automatically track the existence status of columns, but this probably creates overhead and thus should be optional.
- Every row automatically get's a read-only auto-number "rowID" field.
Optional features include:
- Optional schemas or "rigidity flags" on tables which require some columns to be statically defined, required, and/or possibly column types defined also. A trigger-like mechanism would be used similar to "On row change, if field foo less than 2 characters in length, then abandon change with the message 'foo is bad...'". This will allow a gradual transition to the static model if needed. For example rapid prototyping may favor the dynamic approach, but later we may want to put some rules (such as types, validation, "required") in place when the model solidifies and matures. Maybe some columns will be "static" and others left dynamic. See below for a list of possible incremental constraints.
- Uniqueness guarantees - The option of having key- or record-based unique record verification. (Without such, the DB may not qualify as "relational" in a pure sense, but rather BagAtational. However, every row automatically gets an "auto-number" key.)
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
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.
- Dynamic schema is a key feature. If SqLite lacks that, it doesn't qualify in my book.
- A database does not contain "cells" and "tables of cells" as you speak. It contains relvars according to dbdebunk, Date, et al.
- But then most readers wouldn't know what the heck we were talking about.
- Then make a RelVar page and beat it into them because the current industry is in a sad state and most people on here are smart enough to grasp the idea of a relvar after reading about it anyway
- [We can always point them at the page that already exists: RdbRelVar]
- I didn't feel such was necessary. The "street" terms are good enough for topic at hand because this topic is not about formal definitions. (Do I smell a documentation-style HolyWar brewing?)
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:
- Require that a given column is required to have data for a given table.
- Require that a given column is of a certain type (or passes a "parse test" against typical "type" templates or reg-ex's).
- Disallow "instant" new columns (for non-DBA)
- Disallow "instant" new tables (for non-DBA)
- Table-oriented GUI system
- INI file replacement
- RAD for experimental applications or prototyping
- In-house short-term projects
- User/customer insists on more self-service flexibility
See also: ObjectsAreFromMarsTablesAreFromVenus