7 ms·

Database Design: Relation Predicates and “Identical Relations”

59 points by sgeneris 10y ago

gnud 10y ago
Wow, this was hard to read. Not because it was very complex, just because of the incomplete sentences and unessescary use of abbreviations. In the other post [1], linked from this one, he nit-picks because the question uses the terms "tables" and "fields" instead of "relations" and "attributes". I'm sure the author knows more about relational modelling than me, but I'm not sure I would enjoy asking him for advice. 1: http://www.dbdebunk.com/2012/09/NormalOrthogDBDesign_3.html http://www.dbdebunk.com/2012/09/NormalOrthogDBDesign_3.html
- Retra 10y ago
  I think the point there is that if you're thinking of a database as a collection of fields and tables, then there's a chance you might be failing to understand why we use relational databases in the first place, rather than just... tables of data which are indexed as fields. Anyway, in my experience, when someone is insisting on some specific terminology, they tend to have a good reason to do so (at least to the end of highlighting the specific confusions that lead to avoiding yours), and the mental hoops you have to jump through to figure out why their terminology is better are the kinds of things one needs to do to learn. > I'm not sure I would enjoy asking him for advice. I'm not sure you should be optimizing for your own enjoyment when it comes to asking people for advice.
  - Chris2048 10y ago
    > I'm not sure you should be optimizing for your own enjoyment when it comes to asking people for advice. Who talked of optimisation? If you can deliver the same information without the pain, why not do so?
- sgeneris 10y ago
  > Wow, this was hard to read. Well maybe the reader had something to do with it.
  - mhluongo 10y ago
    Are you trying to communicate, or be right on the internet? If you're trying to communicate, readability matters.
burnstek 10y ago
I would say the biggest decision point here is how either entity might evolve. If these are truly different domain entities then one may end up with different attributes than the other in the future, meaning these should definitely be different tables. They just happen to have the same set of fields at this point in time.
kahirsch 10y ago
> If two relations have identical designs, the difference can only be encoded in the relation (and possibly attribute) names, in violation of the IP [information principle]. As such, it is inaccessible to the DBMS, which cannot rely on the RPs [relation predicates] to accept/reject tuples as correct/incorrect. Users--with little, or no help from the DBMS--must ensure that the correct tuples are inserted in the proper relation. Moreover, because information is not represented explicitly (as data values), relational operations lose it: if you UNION the two relations, you get users that either viewed or downloaded items, or both. The author appears to be suggesting that the name of a relation has nothing to do with the meaning of a tuple in it. I've read dozens of books and papers on database design and I have never come across anyone ever suggesting any such thing. He also seems to be saying: (1) The name of a relation is inaccessible to the DBMS. (2) You should be able to throw tuples at a DBMS and it should just figure out where they go. (3) If there are tables A and B in your database and you can't reconstitute A and B separately from (A UNION B), then there's something wrong with your database design.
- catnaroek 10y ago
  > The author appears to be suggesting that the name of a relation has nothing to do with the meaning of a tuple in it. The name of a relation has to be a good mnemonic for human use, but ultimately, the real meaning of a relation is what the DBMS can enforce about its tuples, just like the meaning of a type is what the type system can enforce about its inhabitants. You can't prove much about how humans will actually use a relation (or a type), since that depends on their whims, but you can prove useful things about the limits of what humans can do with a relation (or type), even in principle. And the purpose of a good database schema (or typeful design) is to make nonsensical things impossible, even in principle.
  - ulber 10y ago
    >And the purpose of a good database schema (or typeful design) is to make nonsensical things impossible, even in principle. Can you elaborate on what is the nonsensical thing possible with two tables of the same type? To me the (A UNION B) example makes very much sense exactly in the case that you want all x s.t. (x is in A) OR (x is in B). With differently named id columns you might have to write this as (excuse my pseudo code): (RENAME(A, a_id TO a_or_b_id) UNION RENAME(B, b_id TO a_or_b_id)) which is indeed more explicit. Having written that I might see the rub here. In the article the column "item_id" is kind of a "view_or_download_id" and which it is is only specified the namespace (i.e. the name of the table ITEMS or DOWNLOADS the tuple belongs to). In an OO world this would be the difference between: class Person { public string Name { get; set; } } ... var employees = new List<Person>(...); var managers = new List<Person>(...); and the version where employee and manager types are incompatible: class Person { public string Name { get; set; } } class Employee : Person {} class Manager : Person {} ... var employees = new List<Employee>(...); var managers = new List<Manager>(...); The latter would indeed prevent e.g. employees.Concat(managers) on accident. However, in many programming scenarios the former would be preferred for the increased code reusability. Is this not an objective in database design (with e.g. stored procedures that could be applied to more than one set of tables)?
garyclarke27 10y ago
Article is poorly written, long winded, nonsense. I am happy for Relation names to distinguish entities. He is basically arguing that this violates some sacred principle and that all meaning should be available from column names, so that relations names can be ignored. I don't think that even Chris Date who is a zealous RM fanatic would argue this - his books are actually quite good much more logical than Fabian Pascall. I'm also sure that Joe Celko would not agree with Fabian on this.
cafard 10y ago
Fabian Pascal tends to write as if the point of database work were to construct perfect ontologies. Perhaps we would be better off if we did that, perhaps not. In this case, perhaps it would be a cleaner design to have a single table with ID, USER_ID, FIRST_VIEWED, FIRST_DOWNLOADED. But would I lose sleep over this? No.
- catnaroek 10y ago
  > Fabian Pascal tends to write as if the point of database work were to construct perfect ontologies. Wait, is it not?
makebelieve 10y ago
I found this approach deeply troubling because it moves us away form semantic design towards logic design... which always runs into problems when the database itself becomes semantic content. The write up was rather confusing. The solution seems straightforward. A single table that captures the meaning expressed by the separate VIEWS and DOWNLOADS tables. eg. USERACTION (USER_ID, ITEM_ID, ACTIONTYPE) where ACTIONTYPE is a value like V for view and D for download. Of course, that solution is hard to see because it's a synthesis of meanings occurring at different levels and not the product of predicate logic.
- sgeneris 10y ago
  Database design IS logic. That's the point of the RDM: to formalize and symbolize semantics such that the DBMS can enforce integrity on and manipulate data, such that logical and semantic correctness is guaranteed. Leave that to users in apps at your peril. We used to do this before the RDM and the whole shabang collapsed. And we're still doing it because practitioners know nothing beyond SQL and coding.
  - dragonwriter 10y ago
    > We used to do this before the RDM and the whole shabang collapsed. No, it didn't, and relational-theory-purists aren't going to sell their ideas to practitioners in the real world by pretending that it did. The RDM certainly offers all kinds of abstract benefits, which practitioners often do not fully understand or leverage, and there is a very real problem when the not fully leveraging is due to not fully understanding (rather than weighing practical costs and benefits in the particular use case.) OTOH, the reason that things built on the relational model took off in practice wasn't that non-relational systems had reached a point of catastrophic logical failure that led to their rejection, but because the relational model had a convenient mapping to implementations that were convenient and efficient in the technology of the day (particularly, hard disk storage), combined with some of the structural improvements over other approaches being particularly attractive for important application domains. > And we're still doing it because practitioners know nothing beyond SQL and coding. Yeah, look, we're probably never going to have a time when most practitioners are deep theoreticians rather than expert tool users, and if you want to sell practitioners on deeper consideration of the underlying theoretical models, you're going to need to make explanations of the practical benefits much more accessible than you have in the source article or your comments in this thread (and you're going to need to be a lot less personally abusive.)
sgeneris 10y ago
With some exceptions, how sad. Here's what I commented on my post page @dbdebunk.com: If you want an example of how lack of education on fundamentals can handicap practitioners to the point of inability to comprehend, then check out the exchange that my post has triggered. https://news.ycombinator.com/item?id=12437389&goto=news https://news.ycombinator.com/item?id=12437389&goto=news There are a couple of exceptions, but other than that, it's very sad to see the level of intellect in the profession (or, more accurately, lack thereof).
- aleyan 10y ago
  Normally I would just downvote this kind of comment and this kind of article and move on. However because of your earnestness in educating people on the subject, I will not do that. Instead I will explain what is wrong with it and why it deserves the downvotes. Quite simply the dbdebunked article is hard to read. Too hard to read in fact. It isn't hard to read because the content is complicated, but rather because of the organization of the content is poor. It starts off with a call to action to read another article and immediately follows it up with what appears to be somehow related but poorly referenced block quote. There was actually several triply nested quotes in there amongst other writing smells. Taking the time to edit is important. When an author is writing for an audience it is their duty to be understood. Having the audience exert extra energy in reading because the author is unwilling to exert energy in writing is inefficient. There might be something brilliant in the dbdebunked article, but I am unwilling to puzzle it out or subject others to it.
  - sgeneris 10y ago
    May have something to do with the reader's ability to comprehend.
combatentropy 10y ago
Hey, I can talk like that too. A mental construct has occurred to my consciousness subsequent to the exposure of said mind to myriad graphological artifacts (GE's) conjecting observations that are encapsulated in a word structure (WS) of such syllabic cornucopia that the reliable transmission of said observation to the recipients takes on a high degree of uncertainty. Now it's your fault if you don't understand it.
- sgeneris 10y ago
  I wouldn't expose your inability to understand if I were you.
  - combatentropy 10y ago
    Why is that?
ErwinSmout 10y ago
"If two relations have identical designs, the difference can only be encoded in the relation (and possibly attribute) names," True, though the (and possibly attribute names) part seems dubious to say the least. Different attribute names makes the designs non-identical, no ? in violation of the IP [information principle]. This interpretation of the IP is outright absurd. One, the "I" in "IP" was obviously intended to cover only ever the [end-]user's own business information. The "information" that is being "hidden" under this absurd interpretation is the mapping that applies from relation names to intended interpretation. That information is never part of the [end-]users "genuine business information". How could it ? The mapping in question arises only when the models are being developed. As long as no computers are involved, no information models and no mapping, but the "genuine business information" stays the same. Two, the relation names are present as a value of an attribute in a tuple in a relation. In the catalog that documents the structure of the database that will contain the [end-]users "genuine business information". As such, it is inaccessible to the DBMS, which cannot rely on the RPs [relation predicates] to accept/reject tuples as correct/incorrect. Not sure what is intended here. Is it claimed that because "table names are inaccessible to the DBMS", it is impossible for the DBMS to enforce declared constraints ? Ouch. The SQL REFERENCES clause ought to suffice to counter that. Users--with little, or no help from the DBMS--must ensure that the correct tuples are inserted in the proper relation. So they must know the mapping from relation names to intended interpretation. That is not an insurmountable problem. Hundreds of thousands of developers have already been doing that [or something extremely similar] since before databases even existed. Moreover, because information is not represented explicitly (as data values), relational operations lose it: if you UNION the two relations, you get users that either viewed or downloaded items, or both. Then don't union the two together. (It is alas left unclear whether the usage of the term UNION here refers to invocations of that relational operator on the two relations in the original design (in which case the "loss of information" is (a) intentional and (b) not really loss of information because the original relations have not magically disappeared by computing the union), or in the sense of blindly mergeing the two tables together at the schema level without adding an indication of view vs. download. In which case it's just a stupid design mistake even my cat probably wouldn't commit.)
- sgeneris 10y ago
dragonwriter 10y ago
Wow, for a site that claims to be "database fundamentals made accessible", that's a fairly inaccessible, overwritten piece. I think the fundamental argument it is making has some value; if 2 or more tables [0] are structurally different only in the name of the table, then the facts in those tables are almost certainly related instances of some general common supertype, such that there should be one table, with an additional field disambiguating the subtype (and which probably a foreign key into a new table specifying the valid subtypes, which have a 1:1 relationship to the tables in the "bad" schema.) OTOH, in lots of practical applications no application would ever care about that "ideal" base table, and all interaction would be through views that exactly correspond to the tables in the "bad" design. Outside of a DB serving as an ideal, application-independent store (which is an important role, and even in single-app DBs designing for this can have advantages in dealing with growth and change and unexpected future uses), the value of this ideal transformation may be minimal. [0] base relations, if you prefer.

59 points by sgeneris 10y ago

gnud 10y ago

Wow, this was hard to read. Not because it was very complex, just because of the incomplete sentences and unessescary use of abbreviations. In the other post [1], linked from this one, he nit-picks because the question uses the terms "tables" and "fields" instead of "relations" and "attributes". I'm sure the author knows more about relational modelling than me, but I'm not sure I would enjoy asking him for advice. 1: http://www.dbdebunk.com/2012/09/NormalOrthogDBDesign_3.html http://www.dbdebunk.com/2012/09/NormalOrthogDBDesign_3.html

Retra 10y ago
I think the point there is that if you're thinking of a database as a collection of fields and tables, then there's a chance you might be failing to understand why we use relational databases in the first place, rather than just... tables of data which are indexed as fields. Anyway, in my experience, when someone is insisting on some specific terminology, they tend to have a good reason to do so (at least to the end of highlighting the specific confusions that lead to avoiding yours), and the mental hoops you have to jump through to figure out why their terminology is better are the kinds of things one needs to do to learn. > I'm not sure I would enjoy asking him for advice. I'm not sure you should be optimizing for your own enjoyment when it comes to asking people for advice.
- Chris2048 10y ago
  > I'm not sure you should be optimizing for your own enjoyment when it comes to asking people for advice. Who talked of optimisation? If you can deliver the same information without the pain, why not do so?
sgeneris 10y ago
> Wow, this was hard to read. Well maybe the reader had something to do with it.
- mhluongo 10y ago
  Are you trying to communicate, or be right on the internet? If you're trying to communicate, readability matters.

burnstek 10y ago

I would say the biggest decision point here is how either entity might evolve. If these are truly different domain entities then one may end up with different attributes than the other in the future, meaning these should definitely be different tables. They just happen to have the same set of fields at this point in time.

kahirsch 10y ago

> If two relations have identical designs, the difference can only be encoded in the relation (and possibly attribute) names, in violation of the IP [information principle]. As such, it is inaccessible to the DBMS, which cannot rely on the RPs [relation predicates] to accept/reject tuples as correct/incorrect. Users--with little, or no help from the DBMS--must ensure that the correct tuples are inserted in the proper relation. Moreover, because information is not represented explicitly (as data values), relational operations lose it: if you UNION the two relations, you get users that either viewed or downloaded items, or both. The author appears to be suggesting that the name of a relation has nothing to do with the meaning of a tuple in it. I've read dozens of books and papers on database design and I have never come across anyone ever suggesting any such thing. He also seems to be saying: (1) The name of a relation is inaccessible to the DBMS. (2) You should be able to throw tuples at a DBMS and it should just figure out where they go. (3) If there are tables A and B in your database and you can't reconstitute A and B separately from (A UNION B), then there's something wrong with your database design.

catnaroek 10y ago
> The author appears to be suggesting that the name of a relation has nothing to do with the meaning of a tuple in it. The name of a relation has to be a good mnemonic for human use, but ultimately, the real meaning of a relation is what the DBMS can enforce about its tuples, just like the meaning of a type is what the type system can enforce about its inhabitants. You can't prove much about how humans will actually use a relation (or a type), since that depends on their whims, but you can prove useful things about the limits of what humans can do with a relation (or type), even in principle. And the purpose of a good database schema (or typeful design) is to make nonsensical things impossible, even in principle.
- ulber 10y ago
  >And the purpose of a good database schema (or typeful design) is to make nonsensical things impossible, even in principle. Can you elaborate on what is the nonsensical thing possible with two tables of the same type? To me the (A UNION B) example makes very much sense exactly in the case that you want all x s.t. (x is in A) OR (x is in B). With differently named id columns you might have to write this as (excuse my pseudo code): (RENAME(A, a_id TO a_or_b_id) UNION RENAME(B, b_id TO a_or_b_id)) which is indeed more explicit. Having written that I might see the rub here. In the article the column "item_id" is kind of a "view_or_download_id" and which it is is only specified the namespace (i.e. the name of the table ITEMS or DOWNLOADS the tuple belongs to). In an OO world this would be the difference between: class Person { public string Name { get; set; } } ... var employees = new List<Person>(...); var managers = new List<Person>(...); and the version where employee and manager types are incompatible: class Person { public string Name { get; set; } } class Employee : Person {} class Manager : Person {} ... var employees = new List<Employee>(...); var managers = new List<Manager>(...); The latter would indeed prevent e.g. employees.Concat(managers) on accident. However, in many programming scenarios the former would be preferred for the increased code reusability. Is this not an objective in database design (with e.g. stored procedures that could be applied to more than one set of tables)?

garyclarke27 10y ago

Article is poorly written, long winded, nonsense. I am happy for Relation names to distinguish entities. He is basically arguing that this violates some sacred principle and that all meaning should be available from column names, so that relations names can be ignored. I don't think that even Chris Date who is a zealous RM fanatic would argue this - his books are actually quite good much more logical than Fabian Pascall. I'm also sure that Joe Celko would not agree with Fabian on this.

cafard 10y ago

Fabian Pascal tends to write as if the point of database work were to construct perfect ontologies. Perhaps we would be better off if we did that, perhaps not. In this case, perhaps it would be a cleaner design to have a single table with ID, USER_ID, FIRST_VIEWED, FIRST_DOWNLOADED. But would I lose sleep over this? No.

catnaroek 10y ago
> Fabian Pascal tends to write as if the point of database work were to construct perfect ontologies. Wait, is it not?

makebelieve 10y ago

I found this approach deeply troubling because it moves us away form semantic design towards logic design... which always runs into problems when the database itself becomes semantic content. The write up was rather confusing. The solution seems straightforward. A single table that captures the meaning expressed by the separate VIEWS and DOWNLOADS tables. eg. USERACTION (USER_ID, ITEM_ID, ACTIONTYPE) where ACTIONTYPE is a value like V for view and D for download. Of course, that solution is hard to see because it's a synthesis of meanings occurring at different levels and not the product of predicate logic.

sgeneris 10y ago
Database design IS logic. That's the point of the RDM: to formalize and symbolize semantics such that the DBMS can enforce integrity on and manipulate data, such that logical and semantic correctness is guaranteed. Leave that to users in apps at your peril. We used to do this before the RDM and the whole shabang collapsed. And we're still doing it because practitioners know nothing beyond SQL and coding.
- dragonwriter 10y ago
  > We used to do this before the RDM and the whole shabang collapsed. No, it didn't, and relational-theory-purists aren't going to sell their ideas to practitioners in the real world by pretending that it did. The RDM certainly offers all kinds of abstract benefits, which practitioners often do not fully understand or leverage, and there is a very real problem when the not fully leveraging is due to not fully understanding (rather than weighing practical costs and benefits in the particular use case.) OTOH, the reason that things built on the relational model took off in practice wasn't that non-relational systems had reached a point of catastrophic logical failure that led to their rejection, but because the relational model had a convenient mapping to implementations that were convenient and efficient in the technology of the day (particularly, hard disk storage), combined with some of the structural improvements over other approaches being particularly attractive for important application domains. > And we're still doing it because practitioners know nothing beyond SQL and coding. Yeah, look, we're probably never going to have a time when most practitioners are deep theoreticians rather than expert tool users, and if you want to sell practitioners on deeper consideration of the underlying theoretical models, you're going to need to make explanations of the practical benefits much more accessible than you have in the source article or your comments in this thread (and you're going to need to be a lot less personally abusive.)

sgeneris 10y ago

With some exceptions, how sad. Here's what I commented on my post page @dbdebunk.com: If you want an example of how lack of education on fundamentals can handicap practitioners to the point of inability to comprehend, then check out the exchange that my post has triggered. https://news.ycombinator.com/item?id=12437389&goto=news https://news.ycombinator.com/item?id=12437389&goto=news There are a couple of exceptions, but other than that, it's very sad to see the level of intellect in the profession (or, more accurately, lack thereof).

aleyan 10y ago
Normally I would just downvote this kind of comment and this kind of article and move on. However because of your earnestness in educating people on the subject, I will not do that. Instead I will explain what is wrong with it and why it deserves the downvotes. Quite simply the dbdebunked article is hard to read. Too hard to read in fact. It isn't hard to read because the content is complicated, but rather because of the organization of the content is poor. It starts off with a call to action to read another article and immediately follows it up with what appears to be somehow related but poorly referenced block quote. There was actually several triply nested quotes in there amongst other writing smells. Taking the time to edit is important. When an author is writing for an audience it is their duty to be understood. Having the audience exert extra energy in reading because the author is unwilling to exert energy in writing is inefficient. There might be something brilliant in the dbdebunked article, but I am unwilling to puzzle it out or subject others to it.
- sgeneris 10y ago
  May have something to do with the reader's ability to comprehend.

combatentropy 10y ago

Hey, I can talk like that too. A mental construct has occurred to my consciousness subsequent to the exposure of said mind to myriad graphological artifacts (GE's) conjecting observations that are encapsulated in a word structure (WS) of such syllabic cornucopia that the reliable transmission of said observation to the recipients takes on a high degree of uncertainty. Now it's your fault if you don't understand it.

sgeneris 10y ago
I wouldn't expose your inability to understand if I were you.
- combatentropy 10y ago
  Why is that?

ErwinSmout 10y ago

"If two relations have identical designs, the difference can only be encoded in the relation (and possibly attribute) names," True, though the (and possibly attribute names) part seems dubious to say the least. Different attribute names makes the designs non-identical, no ? in violation of the IP [information principle]. This interpretation of the IP is outright absurd. One, the "I" in "IP" was obviously intended to cover only ever the [end-]user's own business information. The "information" that is being "hidden" under this absurd interpretation is the mapping that applies from relation names to intended interpretation. That information is never part of the [end-]users "genuine business information". How could it ? The mapping in question arises only when the models are being developed. As long as no computers are involved, no information models and no mapping, but the "genuine business information" stays the same. Two, the relation names are present as a value of an attribute in a tuple in a relation. In the catalog that documents the structure of the database that will contain the [end-]users "genuine business information". As such, it is inaccessible to the DBMS, which cannot rely on the RPs [relation predicates] to accept/reject tuples as correct/incorrect. Not sure what is intended here. Is it claimed that because "table names are inaccessible to the DBMS", it is impossible for the DBMS to enforce declared constraints ? Ouch. The SQL REFERENCES clause ought to suffice to counter that. Users--with little, or no help from the DBMS--must ensure that the correct tuples are inserted in the proper relation. So they must know the mapping from relation names to intended interpretation. That is not an insurmountable problem. Hundreds of thousands of developers have already been doing that [or something extremely similar] since before databases even existed. Moreover, because information is not represented explicitly (as data values), relational operations lose it: if you UNION the two relations, you get users that either viewed or downloaded items, or both. Then don't union the two together. (It is alas left unclear whether the usage of the term UNION here refers to invocations of that relational operator on the two relations in the original design (in which case the "loss of information" is (a) intentional and (b) not really loss of information because the original relations have not magically disappeared by computing the union), or in the sense of blindly mergeing the two tables together at the schema level without adding an indication of view vs. download. In which case it's just a stupid design mistake even my cat probably wouldn't commit.)

sgeneris 10y ago

dragonwriter 10y ago

Wow, for a site that claims to be "database fundamentals made accessible", that's a fairly inaccessible, overwritten piece. I think the fundamental argument it is making has some value; if 2 or more tables [0] are structurally different only in the name of the table, then the facts in those tables are almost certainly related instances of some general common supertype, such that there should be one table, with an additional field disambiguating the subtype (and which probably a foreign key into a new table specifying the valid subtypes, which have a 1:1 relationship to the tables in the "bad" schema.) OTOH, in lots of practical applications no application would ever care about that "ideal" base table, and all interaction would be through views that exactly correspond to the tables in the "bad" design. Outside of a DB serving as an ideal, application-independent store (which is an important role, and even in single-app DBs designing for this can have advantages in dealing with growth and change and unexpected future uses), the value of this ideal transformation may be minimal. [0] base relations, if you prefer.