5 ms·
For example, imagine starting out with a contact list. Some months later, you add a restaurants list. Some months later again, you decide it would be great to b
by BrandonM 19y ago
For example, imagine starting out with a contact list. Some months later, you add a restaurants list. Some months later again, you decide it would be great to be able to capture, for each contact, what their favorite restaurants are. Ideally one would want to just establish a “favorite” relationship between a restaurant and a contact without changing the restaurant structure or the contact structure.
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.
- pius 19y agoA 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. 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.
- edw519 19y ago"If this is wrong, please do correct me." 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.
- pius 19y agoThe "please do correct me" part was in regards to my characterization of the arguments that RDBMS gurus make against join tables. I wasn't asking you to judge my architectural decisions, thanks. ;) 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.
- LogicHoleFlaw 19y agoadding join tables like that has traditionally been considered an anti-pattern for relational databases because it increases duplication and denormalizes the data 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?
- edw519 19y ago"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." 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.
- BrandonM 19y agoIf I was downvoted because I said "More tables... is a good thing," I want to clarify that I meant in terms of properly abstracting relationships away from the data, not in terms of performance. If that's not why I was downvoted, then I'm curious to know why.
- pius 19y agoI don't know why someone would downmod you for that comment, but I threw an upmod your way to try to even things out.