2

I'm wondering if there's any database technology where its structure or schema does not need to be defined upfront? For example it may start off with just one column or row and the user will keep adding more columns and rows to the table on the fly, when they see it necessary.

2

If you write your queries properly and avoid SELECT * then any relational database will allow the addition of further columns or tables without requiring adjustment to the application. You do, of course, have to declare each column to the DBMS before referencing it in SQL.

I find the claim that NoSQL is "schema-less" to be slightly misleading. Applications using NoSQL persistance do, indeed, have a schema. The difference is that the schema is held in application code and it is the responsibility of every programmer who touches the code throughout the application's life to enforce that schema. With relational databases the data structure is declared to the service and it then takes the responsibility for enforcing those rules for ever after. The real flexibility of NoSQL is that two rows within the same "bucket" (the definition of which various depending on your DBMS) can have different structure.

| improve this answer | |
  • The key point here is that, in a relational database, the schema is coded as metadata and stored in the system tables. This makes the data self describing. Data that is not self describing has to be managed by the applications that use it. – Walter Mitty Jul 13 '18 at 10:58
1

There are two current methods of doing this that are very popular.

  • JSON SQL:2016,

    With SQL 2016 we have a standardized schema-less type, JSON. Most databases implement it, and many have a binary form of it too (like PostgreSQL, and MySQL). If this form is available you should use it. It has better typing than EAV, and is more easily indexed. It can further support hierarchies, is easier to query, and is ready for use in JSON apis and REST protocols.

    CREATE TABLE foo ( foo_id int, name text, other_data jsonb );
    INSERT INTO foo VALUES ( 5, 'foo', '{"json_key":5,"foo":"baz"}' );
    
    SELECT *
    FROM foo
    WHERE other_data @> '{"foo": "baz"}';
    
  • EAV - Entity Attribute Value with this method you create a table that represents key-value relationships,

    CREATE TABLE entity ( entity_id, name text );
    CREATE TABLE eav ( entity_id int, attribute text, value text );
    

    You can further using a linking table with that,

    CREATE TABLE entity ( entity_id PRIMARY KEY, name text );
    CREATE TABLE eav ( eav_id int PRIMARY KEY, attribute text, value text );
    CREATE TABLE eav_entity (
      eav_id int REFERENCES eav,
      entity_id REFERENCES entity
    );
    

    The idea here is that attribute (keys) and values are in their own table. This has the downside that the attributes must all share the same type. While the values must also share the same type, their type does not have to be the same as the attributes.

| improve this answer | |
0

There is a data architecture called EAV that can be used when the structure of the data is yet to be defined. The user inputs every value as the third element of a triple, called (Entity, Attribute, Value). New entities or attributes can be included as needed.

This structure is completely dynamic, but it's also almost completely out of control. The trouble comes when the owners of the data want the same kinds of results that are cheap and easy with a structured database. I'm talking about reports, extracts, and other common standard outputs. This are often very difficult and expensive to achieve with EAV.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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