6 ms·

> If two relations have identical designs, the difference can only be encoded in the relation (and possibly attribute) names, in violation of the IP [informatio

> 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)?
  - cafard 10y ago
    For what it's worth, I seldom use dynamic SQL in stored procedures, and then seldom for the purpose of changing the names of the objects used; usually it is to vary the criteria applied to a query.
  - catnaroek 10y ago
    > Can you elaborate on what is the nonsensical thing possible with two tables of the same type? There's nothing intrinsically wrong with it a priori. But see below. > 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). To me, the very idea of making a collection whose elements have different logical types is completely nonsensical. I consider it a weakness of SQL that I can form the relation: select customer_id as id, customer_name as name from customer union select employee_id as id, employee_name as name from employee The attributes customer_id and employee_id should have different abstract types, so that they can't be accidentally conflated with one another, even if their internal representation is the same (say, an autoincremental int). Then, if you want to make a derived relation containing both customers and employers, you should be forced to use a sum type (à la Haskell's Either) as the primary key. > In an OO world this would be the difference between: (snippet) Object-orientation is simply the wrong paradigm for building data-oriented applications, at least if you care about automatically enforced data integrity and declarative data manipulation (which you should): https://news.ycombinator.com/item?id=11861520 https://news.ycombinator.com/item?id=11861520
- ErwinSmout 10y ago
  No, the real meaning of a relation is the intended interpretation it was given when it was designed (and which is also the criterion by which the database user decides whether a tuple should be present or not, thus by which the database user decides which updates to carry through).
  - catnaroek 10y ago
    > intended interpretation The DBMS doesn't read your mind before interpreting relations. It interprets relations according to its own data integrity enforcement rules. Most programmers are used to expecting language constructs to mean what a human would mean (“I called my class `Customer`, so of course its instances are customers, right?”), but the formal semantics of the language in question seldom, if ever, justifies this. If you want to establish that your code means what you mean, you have to prove it. For more information: https://en.wikipedia.org/wiki/Semantics_(computer_science) https://en.wikipedia.org/wiki/Semantics_(computer_science)
    - dragonwriter 10y ago
      > The DBMS doesn't read your mind before interpreting relations. The DBMS doesn't interpret relations at all. It performs relational operations on them; humans interpret them (often using other software as a tool to present an interpretation.) > Most programmers are used to expecting language constructs to mean what a human would mean (“I called my class `Customer`, so of course its instances are customers, right?”), but the formal semantics of the language in question seldom, if ever, justifies this. Which "language in question". If you mean the DB query language, that's true. OTOH, in a sense, most programming involves creating specialized languages (often layered on top of existing languages) with their own (in the layered case, additional) semantics. In the constructed language of a particular library or database, that the instances of a Customer class or members of a Customer relation are representations of customers is part of the semantics of that language, even if it isn't part of the semantics of the host language.
      - catnaroek 10y ago

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)?
- cafard 10y ago
  For what it's worth, I seldom use dynamic SQL in stored procedures, and then seldom for the purpose of changing the names of the objects used; usually it is to vary the criteria applied to a query.
- catnaroek 10y ago
  > Can you elaborate on what is the nonsensical thing possible with two tables of the same type? There's nothing intrinsically wrong with it a priori. But see below. > 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). To me, the very idea of making a collection whose elements have different logical types is completely nonsensical. I consider it a weakness of SQL that I can form the relation: select customer_id as id, customer_name as name from customer union select employee_id as id, employee_name as name from employee The attributes customer_id and employee_id should have different abstract types, so that they can't be accidentally conflated with one another, even if their internal representation is the same (say, an autoincremental int). Then, if you want to make a derived relation containing both customers and employers, you should be forced to use a sum type (à la Haskell's Either) as the primary key. > In an OO world this would be the difference between: (snippet) Object-orientation is simply the wrong paradigm for building data-oriented applications, at least if you care about automatically enforced data integrity and declarative data manipulation (which you should): https://news.ycombinator.com/item?id=11861520 https://news.ycombinator.com/item?id=11861520
ErwinSmout 10y ago
No, the real meaning of a relation is the intended interpretation it was given when it was designed (and which is also the criterion by which the database user decides whether a tuple should be present or not, thus by which the database user decides which updates to carry through).
- catnaroek 10y ago
  > intended interpretation The DBMS doesn't read your mind before interpreting relations. It interprets relations according to its own data integrity enforcement rules. Most programmers are used to expecting language constructs to mean what a human would mean (“I called my class `Customer`, so of course its instances are customers, right?”), but the formal semantics of the language in question seldom, if ever, justifies this. If you want to establish that your code means what you mean, you have to prove it. For more information: https://en.wikipedia.org/wiki/Semantics_(computer_science) https://en.wikipedia.org/wiki/Semantics_(computer_science)
  - dragonwriter 10y ago
    > The DBMS doesn't read your mind before interpreting relations. The DBMS doesn't interpret relations at all. It performs relational operations on them; humans interpret them (often using other software as a tool to present an interpretation.) > Most programmers are used to expecting language constructs to mean what a human would mean (“I called my class `Customer`, so of course its instances are customers, right?”), but the formal semantics of the language in question seldom, if ever, justifies this. Which "language in question". If you mean the DB query language, that's true. OTOH, in a sense, most programming involves creating specialized languages (often layered on top of existing languages) with their own (in the layered case, additional) semantics. In the constructed language of a particular library or database, that the instances of a Customer class or members of a Customer relation are representations of customers is part of the semantics of that language, even if it isn't part of the semantics of the host language.
    - catnaroek 10y ago