Primary Key Name

[Banana Man]

Hi,

I have a question about how to name the primary row id key in my MySQL tables. Is it good practice to use the same naming convention for the primary row id field in every table e.g. "row_id", or should i name them all uniquely e.g. in the users table call it "users_row_id" and in the products table call it "products_row_id"?

I would assume that using the same name in each table is the best way to go but i'm just worried that when i do a join query that it will be a bit more awkward to distinguish between each tables row_id field.

Thanks,
Ronan

[Mikl]

There's no definitive answer to this question. Different database designers and developers will all have their own views.

My personal choice is to go for the same column name in every table. The name I use is ID, but it could just as well be something else.

Using the same name in every table can save a lot of effort - and risk of errors - when writing queries. It means that you don't need to think about which column is the target of the join. It's always ID - or whatever. For the same reason, I use a consistent naming convention for foreign keys. My convention is <entity name> + underscore + ID (e.g. Customer_ID). I'm not saying that's a good convention; the point is that it's consistent, which means you can (usually) see at a glance if you get it wrong in a query.

But I do stress this is my personal choice. It's really up to you (and the developers who will be accessing your database) as to whether it's right for you.

Mike

[r937]

My convention is <entity name> + underscore + ID (e.g. Customer_ID). I'm not saying that's a good convention;

i am

[Banana Man]

Ok, thanks. I like the sound of your setup so i think i'll go with that.