Database design - primary key naming conventions

ID is the worst PK name you can have in my opinion. TablenameID works much better for reporting so you don't have to alias a bunch of columns named the same thing when doing complex reporting queries.

It is my personal belief that columns should only be named the same thing if they mean the same thing. The customer ID does not mean the same thing as the orderid and thus they should conceptually have different names. WHen you have many joins and a complex data structure, it is easier to maintain as well when the pk and fk have the same name. It is harder to spot an error in a join when you have ID columns. For instance suppose you joined to four tables all of which have an ID column. In the last join you accidentally used the alias for the first table and not the third one. If you used OrderID, CustomerID etc. instead of ID, you would get a syntax error because the first table doesn't contain that column. If you use ID it would happily join incorrectly.

OK so forget example 3 - it's just plain silly, so it's between 1 and 2.

the id for PK school of thought (2)

drop table if exists customer;
create table customer
(
id int unsigned not null auto_increment primary key, -- my names are id, cid, cusid, custid ????
name varchar(255) not null
)engine=innodb;

insert into customer (name) values ('cust1'),('cust2');

drop table if exists orders;

create table orders
(
id int unsigned not null auto_increment primary key, -- my names are id, oid, ordid
cid int unsigned not null -- hmmm what shall i call this ?
)engine=innodb;

insert into orders (cid) values (1),(2),(1),(1),(2);

-- so if i do a simple give me all of the customer orders query we get the following output

select
 c.id,
 o.id
from
 customer c
inner join orders o on c.id = o.cid;

id  id1 -- big fan of column names like id1, id2, id3 : they are sooo descriptive
==  ===
1     1
2     2
1     3
1     4
2     5

-- so now i have to alias my columns like so:

select
 c.id as cid, -- shall i call it cid or custid, customer_id whatever ??
 o.id as oid
from
 customer c
inner join orders o on c.id = o.cid; -- cid here but id in customer - where is my consistency ?

cid oid 
==  ===
1     1
2     2
1     3
1     4
2     5

the tablename_id prefix for PK/FK name school of thought (1)

(feel free to use an abbreviated form of tablename i.e cust_id instead of customer_id)

drop table if exists customer;
create table customer
(
cust_id int unsigned not null auto_increment primary key, -- pk
name varchar(255) not null
)engine=innodb;

insert into customer (name) values ('cust1'),('cust2');

drop table if exists orders;
create table orders
(
order_id int unsigned not null auto_increment primary key,
cust_id int unsigned not null 
)engine=innodb;

insert into orders (cust_id) values (1),(2),(1),(1),(2);

select
 c.cust_id,
 o.order_id
from
 customer c
inner join orders o on c.cus