When you have to join two tables, both with an unrelated field called "id", you'll regret that.
Or when you have to join a foreign key, and you end up with two fields both called "id" in the same table - and don't ever give a foreign key a different name in the parent and child tables.
And even if you say: I'll just prefix the column name with the table name in the query, remember that when your app gets the field, it'll just be called "id", and you'll be tempted to alias it just so you know which id field it is, at which point you'll realize that you should have called it something else from the start.
When you join those tables, you won't regret it. You may have to think about it for a moment; if you need the ids in the join they're there: SELECT A.id FROM A JOIN B ON A.project = B.project; this is concise and makes sense.
When you do a self-join, the table-name-prefixed-column-names strategy doesn't buy you anything anyway, you'll have to explicitly qualify or rename the column(s) regardless.
Don't name foreign keys the same as the referenced column, name them to make the represented relationship clear, if you have a parent-child relationship, name the column "parent" (or if you must, "parent_id"; redundant if every table has a synthetic primary key named "id").
And when you do: SELECT A.id, B.id FROM A JOIN B ON A.project = B.project
How are you going to know which id field your app has?
And there are a lot more relationships than just a parent/child. You often has a table with many child keys, each to a different attribute.
I should tell you, I used to do like you - I would name all the primary keys ID, and the foreign keys tablename_id.
I've learned it's not actually a good way to structure a database. It's much much clearer is parent/child columns have the same name. You'll see it once you start making complicated databases.
"How are you going to know which id field your app has?"
Depends on how your interface to the data-layer layer behaves. If you're going right into an associative array, you may get "A.id" and "B.id" for free or you may have to explicitly rename A.id and B.id, e.g., "SELECT A.id as A_id...". That's not a consequence of using "id" as a column name, you'd have the same problem with any other name collision in a join unless you named every column databaseName_schemaName_tableName_columnName and you'd still have the problem in a self-join.
"name all ... the foreign keys tablename_id."
Sorry, I wasn't clear, I'm saying don't do that! Use a name that describes the relationship between the entity in the referencing relation and the entity in the referenced relation!
In the parent-child case, I meant the parent column as a foreign key back to PERSON stored in a column called "parent", like CREATE TABLE PERSON (id int primary key.... parent int foreiegn key references PERSON(id)...)
Using tablename_id is only going to get you one relationship per ordered-pair of tables, so its a non-starter, you may want to have parent and spouse relationships between PERSON and PERSON for instance, naming them both PERSON_ID... well you don't want to marry your mother :)
Or when you have to join a foreign key, and you end up with two fields both called "id" in the same table - and don't ever give a foreign key a different name in the parent and child tables.
And even if you say: I'll just prefix the column name with the table name in the query, remember that when your app gets the field, it'll just be called "id", and you'll be tempted to alias it just so you know which id field it is, at which point you'll realize that you should have called it something else from the start.