

Ask HN: Why is there no datatype “rows of (another) table” in databases? - miclill

This way we could still do:
1&lt;-&gt;M
M&lt;-&gt;M
1&lt;-&gt;1
No more handeling of IDs and if you don&#x27;t want the DB to do the &quot;implicit join&quot; just omit the column.
Would we loose any kind of flexibility?
I strongly suspect that I am missing something here but I don&#x27;t know how to find information &#x2F; search for it.
======
andyjpb
This is typed in the foreign key constraints DDL.

Often a foreign key will refer to rows in more than one other table. Not all
queries will want to chase all of those references.

Moreover, when you have a composite key, the foreign part may only be a subset
of the entire key.

For example, if your Key is {Street,House}, you have a relation to another
table which has a list of people inside that {Street,House} and you might also
have a relation on {Street} to a table that contains metadata about that
street as a whole.

The "Type" of the "row in another table" isn't a single thing: it would be
different for all the different relations so it exists in the form of foreign
key declarations which embody all the variability in the relation.

~~~
miclill
Of course! I guess the saying is true that you sometimes can't see the forrest
because of all the trees... Thank you.

------
GFischer
As andyjpb said, it's encoded in the foreign key.

Many ORMs have capabilities for "implicit joins" with either lazy or eager
loading of the foreign rows, for example the latest Entity Framework from
Microsoft.

[http://msdn.microsoft.com/en-
us/data/jj713564.aspx](http://msdn.microsoft.com/en-us/data/jj713564.aspx)

If you like that kind of "automagic" things, check out Genexus:

[http://www.genexus.com/global/home?en](http://www.genexus.com/global/home?en)

it's a 4GL programming environment that has as one of its main strengths that
kind of implicit joins and automatic retrieval of data you mention, as long as
you follow the naming conventions.

It's a REALLY fast way to build your standard line-of-business application
(and it automatically generates iOS and Android apps too), but it falls apart
for any other use, and it's UI is not that flexible usually.

