Yup, you could definitely express a new relationship that way. I think the point is that adding join tables like that has traditionally been considered an anti-pattern for relational databases because it increases duplication and denormalizes the data, thus working against the supposed performance gains and data integrity protection from using the RDBMS in the first place. If this is wrong, please do correct me.
One major difference I've noticed between document/graph-oriented databases and relational ones is that they embrace denormalization and even optimize for it insofar as that's possible.
Relational databases have been optimised to do a certain task very well: retrieve information very rapidly from a large dataset, that has well-defined, relatively static, relationships between entities. They were designed for things such as storing government census data, tracking customer details for very large corporations, insurance data etc etc. You may like to think of relational databases as being the C equivalent in the database space: relatively low-level, fast.
But there is a new type of database out there. It is more human in it's scale, with tables that might only have thousands, rather than millions, of entries. But the data has very fluid relationships between entities: Hank's example in the comments here about wanting to track a contact's favourite restaurant is a classic example. We might also want to track the contact's favourite film, their car model, and where they last went for their holidays. A bit later we may also consider it really important to know what brand of dishwashing detergent they use.
This sort of problem is not well handled by relational databases. They aren't optimised for it. They are optimised for speed over large datasets with static relationships. To get fluid relationships, you need to use join tables, which have a large size cost, and which degrade the reliability of the database. But speed is not typically a problem for these types of problems. There are lots and lots of small tables, instead of a few large tables. Manually examining every item in the table to find one that matches your criteria is not necessarily a prohibitively expensive operation.
Thinking about this kind of problem from a Rails perspective, for example, you might decide to modify ActiveRecord such that its has_one, belongs_to style declarators, instead of working on static fields in a table, instead attack one table for the object, containing a bunch of tuples that look like {"relationship_name", table_id, [item_id1, item_id2,...]} Operations on the relationships between objects modifies this table, rather than the actual tables that contain object information. As such, the structure of links between tables becomes just another table. It wouldn't be as fast as a relational database, but for this type of task, it is far better suited, as it would be far more expressive - a new relationship between objects can be created just by inserting a new row into the table, and is hence modifiable programatically. It's the lisp of the database world.
I thought that join tables express a normalization of the data? You are then not storing restaurant data explicitly as a column in the Contact table, which reduces data duplication and gives you more fine-grained control over your structure.
Join tables (especially reflexive ones) gave me a bit of a headache when I first started working with SQL databases. Once I finally wrapped my head around them I started seeing a lot of uses for and advantages of them. However, I've had little formal training in database techniques; only a little bit of relational algebra. Is there something I'm missing here?
My understanding was that hardcore relational database guys would say that join operations are necessary when the data's totally denormalized, but having a join table wasn't necessarily a best practice because now you've got an additional table that could potentially get out of sync.
It's not right or wrong. It's a trade-off, a design decision.
Just because a tool can do something, doesn't mean it should in every case.
Normalize as far as it makes sense for your app.
I think it's besides the point whether or not an RDBMS can handle a given app's data; it almost certainly can. The real question is should people dogmatically choose an RDBMS for every single data persistence problem they need to solve.
While the article's title is obviously hyperbole, I think the dissent against choosing the RDBMS model of storing knowledge is a good one. I see the decision that system architects are faced with here as being an end-to-end argument: should the protections and optimizations provided by relational databases be enforced at such a low level or are dumb databases that delegate those features to other layers better design? There's decent evidence for the latter.
"Choosing the right tool for the job" does not equal "the old tool is dead".
Can't argue with that.
By whom? The number of tables in the schema should be what the app calls for, no more, no less. That's like saying, "Too many lines of code in a program is considered bad programming."
I don't know what implementation's OP has seen, but GOOD RDBMS's are limited only by hardware.
Personally, I have occasionally encountered apps whose best approach was not RDBMS, but I have never run in to a data problem that couldn't be handled by a RDBMS.
Let's look at this example in a relational database. Personally, I've never even implemented a database, but I did take one class on relational databases. The way I learned it, the contacts would be one table and the restaurants would be another table. A third table, let's call it "FavoriteRestaurant" would have two columns [1]: a foreign key to an entry in the Contacts table and a foreign key to an entry in the Restaurants table. The primary key in this table would have to be the contacts column, since restaurants would appear more than once. If each person can appear in the FavoriteRestaurant table more than once (multiple favorite restaurants), then both columns would have to serve as the primary key.
Thus, we have managed to effectively utilize a relational database to express a new relationship, without ever changing the original data. The author said:
Most relational databases actually have an upper limit on the types of objects, typically referred to as tables, which can be handled. Too many tables in a database schema is considered bad design.
If that is indeed the case, that is where the problems lies. I am far from being a champion of relational databases, but it seems to me like a lot of people don't think critically about how best to store the information in their databases. More tables, in my mind, is a good thing.
[1] The number of columns would actually be equivalent to the sum of the number of items in the primary keys of both the Contacts and Restaurants tables. With unique identifiers (SSNs and vendor IDs, for example), of course, this would indeed be two columns.