>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)?
> 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
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.
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):
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: and the version where employee and manager types are incompatible: 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)?