My Forward-Seeing Predecessors
-
@Lorne Kates said:
@Jaime said:
There is an entire syntax that your naming convention makes worthless: USING clause. I'm not saying this makes you wrong, but it is strong support for using the same column name for both PK and FK usage. If only Oracle had the syntax I'd say it's safe to write it off, but DB2 has it too.
But but but.. what am I supposed to do when I need multiple FK to the same table? You know:
@bad fucking dumb shit database said:
student_id int,
primary_parent_person_id int foreign key references person(person_id),
secondary_parent_person_id int foreign key references person(person_id),
alternate_caretaker_person_id int foreign key references person(person_id),
favorite_uncle_person_id int foreign key references person(person_id),
...And don't say "put it in a table with student_id, person_id, person_type", because then I'd have to write multiple joins, wouldn't I?
...... what do you mean how am I going to get the names of all those people? By writing a join for each of those foreign keys, of course.
It doesn't matter because any situation with multiple foreign keys referencing the same table cannot ever use the USING clause. Even your proposed solution isn't a solution at all because the columns aren't named person_id. So, the person_id naming convention doesn't cause any harm in your example. It doesn't help, but it also doesn't hurt. Besides, I didn't say I was for it, just that the syntax of two of the "big three" databases gave a nod to it.
Now that we've started one religious database war, let's start a few more; intelligent/surrogate keys? GUIDs as primary keys? Underscores, camelCase, or PascalCase? ID, id, or Id? Factor out NULLs by using one-to-one relationships? Cluster on the primary key? Stored procedures?
-
@TheCPUWizard said:
Wait.. If there is a 1-1 between the two then every Party is an Employee, and every party is a Contact, and therefore the number of Contacts, Parties, and Employees would all be identical.
Your scenario only makes sense if there is a 1-0:1 relationship (i.e. one side of a full join may be null for any given key). In a 1-1, there most always be exactly one key on both tables, and never a key in one table that is not in the other.
One-to-one is a stupid name for a relationship because it can't exist at the physical level. If any database engine actually enforced a literal one-to-one relationship, you could never add rows to either of the tables - at least until someone invented a syntax to insert into two tables with one statement. So, in reality, one-to-one is really a name for a 1:0-1 relationship and only really applies to conceptual design. The most common implementation of 1:1 is to make the primary key column of table B also a foreign key that references table A.
-
@Jaime said:
@TheCPUWizard said:
Wait.. If there is a 1-1 between the two then every Party is an Employee, and every party is a Contact, and therefore the number of Contacts, Parties, and Employees would all be identical.
Your scenario only makes sense if there is a 1-0:1 relationship (i.e. one side of a full join may be null for any given key). In a 1-1, there most always be exactly one key on both tables, and never a key in one table that is not in the other.
One-to-one is a stupid name for a relationship because it can't exist at the physical level. If any database engine actually enforced a literal one-to-one relationship, you could never add rows to either of the tables - at least until someone invented a syntax to insert into two tables with one statement. So, in reality, one-to-one is really a name for a 1:0-1 relationship and only really applies to conceptual design. The most common implementation of 1:1 is to make the primary key column of table B also a foreign key that references table A.
There are a fair number of systems that will enforce 1-1 (not 1-0:1) at the transactional level, which is really what matters. In these cases, if you add a row to one table, but not the other then the transaction will fail when you try to commit.
Remember "Reality does not matter" (a favorite quote - followed closely by "there is no spoon"), what matters is what can be OBSERVED.
In this case, one would NEVER be able to observe a condition where there was not a true 1-1 relationship (is the relationship was declared as thus).
In addition to the actual DBMS, this distinction becomes quite critical in developing UML diagrams (and even more so for code generated from such diagrams). If something is a 1-0:1, then the code must check for null (at some point). If something is a 1-1 then a null check is unnecessary, since such a condition will never be observed.
-
@Jaime said:
@morbiuswilters said:
@Cassidy said:
There is an entire syntax that your naming convention makes worthless: USING clause. I'm not saying this makes you wrong, but it is strong support for using the same column name for both PK and FK usage. If only Oracle had the syntax I'd say it's safe to write it off, but DB2 has it too.@PJH said:
Aren't you joining on two primary keys there?
No, joining on two columns called "ID". Admittedly, I should have called them something like "UserID" or "CustomerNo" but I'm talking in the context of the PK and FK of related tables having the same column name in each table, which makes the previous join syntax possible.
What I was suggesting was naming PKs "id" and FKs "foreign_table_id".
MySQL and Postgres have it, too. I don't really consider it a reason to use the table name in the PK.
-
@Jaime said:
intelligent/surrogate keys?
Depends, but usually surrogate.
@Jaime said:
GUIDs as primary keys?
GUID unless it really creates a performance problem.
@Jaime said:
Underscores, camelCase, or PascalCase?
Underscores, all lower case. No real reason, just personal preference.
@Jaime said:
Cluster on the primary key?
Yes, but my database doesn't give me a choice.
@Jaime said:
Stored procedures?
Usually not.
-
@Jaime said:
If only Oracle had the syntax I'd say it's safe to write it off, but DB2 has it too.
I really don't think "DB2 has it" is a good reason to do anything.
-
@morbiuswilters said:
@Jaime said:
intelligent/surrogate keys?
Depends, but usually surrogate.
@Jaime said:
GUIDs as primary keys?
GUID unless it really creates a performance problem.
@Jaime said:
Underscores, camelCase, or PascalCase?
Underscores, all lower case. No real reason, just personal preference.
@Jaime said:
Cluster on the primary key?
Yes, but my database doesn't give me a choice.
@Jaime said:
Stored procedures?
Usually not.
Those are all the wrong answers and you should fisted with a porcupine puppet for giving them.
Jerk.
-
@Jaime said:
There is an entire syntax that your naming convention makes worthless: USING clause.
Now I'm sober (or more sober than last night) I think this is the point I was alluding to in my previous post.
I probably didn't make it clear by providing just examples.
-
@Cassidy said:
What you're suggesting would look like:
SELECT * FROM Foo JOIN Bar ON foo.foo_Id = bar.bar_Id;
Um, no. Suppose that the table 'person' contains information about persons, and 'person_id' is a primary key. The table 'cat' contains information about cats, and whereas 'cat_id' is the primary of that table, 'person_id' is a foreign key indicating which person is the personal servant of said cat. (For the sake of argument, I'm assuming that cats have only one servant, whilst acknowledging that the entirety of humanity is there to serve cats.)
So we get:
SELECT * FROM person JOIN cat ON person.person_id = cat.person_id
@Cassidy said:
Another advantage of omitting the table prefix in the column names means that queries could be written like:
SELECT * FROM Foo JOIN Bar USING(Id)
or:
SELECT * FROM Foo NATURAL JOIN Bar
In other words,
SELECT * FROM person JOIN cat USING (person_id)
The advantage with this solution is that it's clear what ID we're talking about, and if each cat has its own set of toys, you can make a 'toy' table with a 'toy_id' primary key and a 'cat_id' foreign key. With your solution, you'd be looking for a name that could never fit your naming scheme.
-
I believe that was the point I was trying to make, just that I used foo and bar instead of person and cat.
.. or are my examples different? I can't see where they are, but that may be my mind filling in omitted info.
-
@morbiuswilters said:
Your model sounds.. weird. Employee, User and Contact all extend a base type, but then are composed of each other? And if I had extended tables referring to a base table I'd FK the extension tables on the base table's PK.
Actually, I think Hibernate (and possibly other ORM frameworks) may do something similar if you configure it to use "table-per-subclass" mapping for persisting data objects of related classes. It creates one table containing the fields of the base class, and additional tables for each derived class containing any additional fields introduced by that class. For queries, it then joins them together on their common primary key - there are no separate foreign key relationships between the tables, because basically, the primary key is enough to uniquely identify all the data belonging to the concrete instance of an object (one row in the base-class-table, and at most one row per derived-class-table). Like this.I'm not saying that that's the best way to do it, but it's a way, to be sure.
-
@Cassidy said:
I believe that was the point I was trying to make, just that I used foo and bar instead of person and cat.
.. or are my examples different? I can't see where they are, but that may be my mind filling in omitted info.
Well, you use 'id', whereas I use 'person_id' (or 'cat_id'), which is what the whole discussion was about in the first place.
-
@Severity One said:
@Cassidy said:
No wonder you confused him, making a post about the point of the discussion. That's not the way it works around here... or are my examples different? I can't see where they are, but that may be my mind filling in omitted info.
Well, you use 'id', whereas I use 'person_id' (or 'cat_id'), which is what the whole discussion was about in the first place.
-
@Scarlet Manuka said:
No wonder you confused him, making a post about the point of the discussion. That's not the way it works around here.
In my defense, I did mention cats, which were totally off-topic.
