Personally, I would never use a simple name like “id”. It’s confusing for one, especially if there are no FK constraints to point you to what that ID refers to. So I make my IDs common across all tables. A quick example would be something like this:
[B]Blog Table[/B]
blog_id
blog_title
blog_content
author_id
[B]Tags Table[/B]
tag_id
tag_name
tag_count
[B]Blogs2Tags[/B][B] Table[/B]
blog_id
tag_id
[B]Authors Table[/B]
author_id
author_name
author_email
author_password
It’s a very simple example so it may not appear necessary, but I’ve worked on some systems with 100s of tables and foreign keys are all over. I’ve seen some where they’d have:
You may work out eventually that they mean:
- id - table PK and row identifier
- cid - category ID. In this case the manufacturer
- tid - tariff ID
- pid - phone ID
Only then you go elsewhere and pid means product ID, and there are still cids, tids and ids. If you’re going to use an ID, make it unique. It gets even more confusing when you end up with something like:
SELECT *
FROM products p
, phones ph
, pictures pic
, people pe
WHERE p.pid = ph.id
AND ph.pid = pic.id
AND pic.pid = pe.id
Wait, what? All of them have an id column and a pid column, and it’s not obvious what any of them do! This is better:
SELECT *
FROM products p
, phones ph
, pictures pic
, people pe
WHERE p.phone_id = ph.id
AND ph.pic_id = pic.id
AND pic.pe_id = pe.id
But this is better still IMHO:
SELECT *
FROM products p
, phones ph
, pictures pic
, people pe
WHERE p.phone_id = ph.phone_id
AND ph.pic_id = pic.pic_id
AND pic.people_id = pe.people_id
Suddenly the relationships appear much more clear.
Yes, it’s a deliberately messy situation created completely off the top of my head, but I’ve actually been in situations where databases have been that confusing (more so, actually) so I always try to keep field names unique, especially where they’ll be used as FKs.
And no, I can’t always use FK constraints to make relationships clearer. As we found out when we migrated from InnoDB to NDB Cluster, it didn’t support FK constraints (I believe that is now remedied, mind you)
I think it’s personal preference, but I’d always try to make it as clear as possible, especially if I’m sure that a) I won’t be the only person maintaining it and b) it’s likely to grow large