Ask HN: Why is there no datatype “rows of (another) table” in databases?
This way we could still do:
1<->M
M<->M
1<->1
No more handeling of IDs and if you don't want the DB to do the "implicit join" just omit the column.
Would we loose any kind of flexibility?
I strongly suspect that I am missing something here but I don't know how to find information / search for it.
2 comments
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.
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.
Of course!
I guess the saying is true that you sometimes can't see the forrest because of all the trees...
Thank you.
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
If you like that kind of "automagic" things, check out Genexus:
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.
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
If you like that kind of "automagic" things, check out Genexus:
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.