2006-08-26

The dreaded ID or even worse IDENTITY

Ruby on Rails, the convention over configuration framework, wants us to have an ID column named ID as a primary key in every table. Now THAT is poor SQL! If we look at the Microsoft Adventureworks database they use singular form object for table names and use the singular form+ID for every table as in Vendor and VendorID. Poor SQL! So what should you use?

I use my own convention.:

Say I have a table Vendors, then the Vendor ID column I call Vendor. For foreign keys to Vendors I call the column Vendor, rather than as Rails would like me to Vendor_ID.

For me this works very well. I find it very clear and consistent. When I talk to customers they always refer to stuff like "the Project" meaning the Project ID for a given Project. So in my Projects table I have the primary key column Project. Rails is very good at distinguishing plural forms and singular forms of words so why didn't they come up with this convention?

And why do Microsoft make a sample database full of horrible SQL? Beats me! Or I may have read to much Joe Celko.

"Joe Celko the SQL apprentice" blog can be found here. I'm pretty sure it's not maintained by Mr. Celko, but the answers are very Celkoish (Would Celko ever reference SQL Server 2005 as SQL-2005?? NO!!!)

Now I have to hack Rails to follow my convention ;-)

Happy codein(g,e)!

No comments: