mysql primary key id debate

[Nicholas Leippe]

On Friday 30 December 2005 10:07 am, Ross Werner wrote:
> If the latter, unless I misunderstood, that was never under debate--I
> think everyone prefers $x_id for foreign keys. The argument is between:
>
> (Table "foo" with two foreign key columns)
> foo.foo_id
> foo.bar_id
> foo.baz_id
>
> vs.
>
> foo.id
> foo.bar_id
> foo.baz_id
>

I think it's 6 of one, a half-dozen of the other.

In the former, you get a form of type-safety by knowing that all joins must 
have matching column names, but at the cost of a small amount of redundancy 
foo.foo_id.

In the latter,
- you remove that redundancy
- you still have some of the type-safety (except when joining to the table 
that originated the key--since it's often aliased you have to read the query 
more carefully to make sure that 'id' comes from the correct table, but this 
is the more common case--every query with a join will have at least one of 
these)
- you also gain a tiny bit of type-safety in the fact that no join should ever 
be 'ON(a.id = b.id)' unless you're joining a table on itself.

When I say type-safety, I am merely referring to the idea that it becomes 
trivial to verify that a query is always joining apples on apples by way of 
the naming convention.

For what it's worth, I'm torn between the two of them myself, but have chosen 
the former more often.


-- 
Respectfully,

Nicholas Leippe
Sales Team Automation, LLC
1335 West 1650 North, Suite C
Springville, UT  84663 +1 801.853.4090
http://www.salesteamautomation.com