Tables within Tables within Tables

Question

I am new to database management/design but am fascinated by the subject and the possibilities it provides. I have had some experience with the following:

• tables
• attributes
• fields and data types
• defining foreign and primary keys
• establishing one to many, one to one and many to many relationships using junction tables
• developing queries using SQL

I have encountered one data structure that I have not been able to model properly in SQL for instance. Namely, the case where a field contains itself another table. This is frequently encountered in procedural and object oriented programming where data structures (such as derived types in fortran) can contain subtypes which hold only one value at a time but also subtypes which hold arrays... etc. In python for example it is possible that a "field" in a dict can hold a list which itself can hold fields of any given datatype including another list, dict or class. As you can see this allows for considerable complexity which I am not sure how to represent in a classical "database" schema in SQL where a table can have relationships with other tables but where they key: value schema is strictly enforced.

Anyone else know what I'm talking about? Is it possible/necessary to have tables within tables? If not, what SQL functionality renders this obsolete?

THanks

Answer 1

The concept you are exploring is called "nested tables". Doing a search on this will yield a lot of items to read. I haven't read those items, so I'm not going to make recommendations.

Many database products, such as Oracle, have a provision for defining table types as domains, and then referencing those domains when defining columns of other tables. These are called nested tables. To my knowledge, standard SQL does not have such a provision.

The concept of nested tables is closely related to the concept of First Normal Form, which Ed Codd originally called simply Normal Form, back in 1970. In his paper, he talked about how databases could be limited to First Normal Form without limiting the expressive power of the relational model of data. It turns out that a relational model that conforms to 1NF can be implemented in SQL without using nested tables.

You might also want to revisit CJ Date's treatment of the subject, where he presents the idea that a relation is always in 1NF.

Revisiting the history of ideas in computing can seem dry and boring, but given the question you are exploring, these historical pointers I'm providing are vital to understanding why SQL has not latched onto this concept.

As you go deeper, you'll begin to see how this connects to another phenomenon, which is the difference between relational data modeling and object modeling. Object modeling is profoundly different from relational data modeling, to the point where experts in one form of modeling think the other is incomprehensible. This often causes problems when an application is designed using an object model and the database is designed using the relational model. This is sometimes called the "object relational mismatch".

I can't do justice to Codd's thinking here, but in very simple terms, he expected the first few relational DBMS systems to employ a very simple form of indexing to speed things up, and a table with nested tables can't use simple indexes to speed up keyed reference to data in subtables.

Answer 2

SQL requires tables to be strongly typed. There is no concept of a result set, nor a table that's type can not be determined prior to execution. That said, SQL has,

• composite types which themselves are like a statically typed tuples that can contain other types.
• types that permit representation of arbitrary data: typically, json (SQL:2016) and XML (SQL:2006).

So you can for instance with PostgreSQL represent "tables in tables" with JSON,

CREATE TABLE foo(myjson)
  AS VALUES ('{"foo":{"bar":1}}'::json);

test=# \d foo;
    Table "public.foo"
 Column | Type | Modifiers 
--------+------+-----------
 myjson | json | 

Or you can use a composite type

CREATE TYPE bar AS ( a int, b text );

CREATE TABLE foo(mybar)
  AS VALUES ( (0,'foobar')::bar );

test=# \d foo
    Table "public.foo"
 Column | Type | Modifiers 
--------+------+-----------
 mybar  | bar  | 

Or, a composite type of composite types (don't do this, just use jsonb)

CREATE TABLE typeoftype(mybaz)
  AS VALUES ( (42,(0,'foobar'))::baz );

test=# \d typeoftype ;
 Table "public.typeoftype"
 Column | Type | Modifiers 
--------+------+-----------
 mybaz  | baz  |