Keith Devens .com

I'm interested as to WHY you include the datatype as part of the field name. I can't say I agree. To me, the datatype of a field is self apparent due to its contect:

text:
SELECT field FROM table WHERE field LIKE 'test%'

numeric:
SELECT field FROM table WHERE field > 10

date:
SELECT field FROM table WHERE field > '10 Mar 2003'

Is it so you can programatically tell the data type of a field? If you need to know the datatype of a field from an arbitary query, you should rely on the schema to tell you. PHP for example has odbc_field_type() and mysql_field_type() functions, DAO has .type properties, etc.

What if you later decide to change the datatype of a field from say text to date? All your hard coded queries will have to be re-written :-(

I'm not nocking it really - just interested in your motivation.

Is it so you can programatically tell the data type of a field?

No, that's not it. It's so that the field names are more self describing. This example probably isn't optimal, but say I have an event database (like, for keeping a calendar of appointments, tasks, birthdays, etc.) You might have an "Appointment" field. But is that a boolean telling you that the event is an "appointment" as opposed to a task, or is it the Date of the appointment? Or, a DateTime or just the Time? Using my naming convention makes it clearer what's going on.

Keep in mind, however, that my scheme is not meant to indicate the type of the field, so much as how it's used. So, it's really not much like hungarian notation (also see Hungarian Notation - The Good, The Bad and The Ugly).

For example, in my weblog entry table I have fields like Entry_Id, Entry_Title, Entry_Name, and Entry_Text. And in my wiki "revisions" table, I have fields like Revision_Number, Revision_Text, and Revision_Description. All of those suffixes have very specific meanings for me. Though the page is currently very messy and incomplete, I go into some detail about these conventions in my naming conventions page on my wiki.

'Id' means a unique (numeric) id. If it's a "User" table and the field is "User_Id", you know that that's the primary key. If that table also has a "Group_Id" field, then you know that's a foreign key for the Group table. On the other hand, if it was just "Group" it's not immediately obvious what's supposed to go in that field or whether or not it's a key.

'Name' means a (at least semi) unique textual name for a record. I say semi because it's possible for it to be unique within a range in some other field. For instance, Entry_Name in my weblog has to be unique within its creation date.

'Title' is a title, not guaranteed to be unique at all, 'Description' is something that's not really part of the record, but rather about the record. Whenever you see a description, think "metadata". So each revision has a description of the change made. It could be named "Revision_Comment", but that's less clear, I think.

A 'Text' suffix means that it should be a large text field, and implies that it should be able to hold as much as you could possibly want to put in. I might limit 'Title's to only 50 characters, for instance, 'Name's to 15 (or something), and maybe 'Description's to 255, but a 'Text' field should probably be able to hold something like 65k or more. Plus, it's a lot better, in the Revisions table, to have something like Revision_Text than just Revision, or Comment_Text rather than Comment, or Entry_Text rather than Entry.

These conventions make it much easier to look at a database schema and see exactly what's going on. If your field names can be as self-describing as possible according to a short convention like I use it usually saves you from having to describe in a human language what the field is meant to do, and lets you comprehend a potentially large database schema more easily. In an ER diagram, for instance, you simply have field names without the benefit of a "description" field, even though database products often provide a place for you to enter a description in the table definition. So, for many reasons, databases require different naming conventions than you need in programming code.

One more example: If you have an "Address book" table, you should probably have fields like Name_First, Name_Last, rather than First_Name, Last_Name, etc.

Thanks for the feedback Keith - always good to peer inside the head of another developer.

You raised a lot of good points here, but I can't say I agree with including a table name in the field name (eg. entry_title). I realise a few RDBMS' enforce unique field names over an entire database but they are few and far between.

In my case, I always include the table prefix anyway, even in simple queries (as simple queries generally become more complicated over time):

select event.title FROM event...

I also usually name my primary key fields id too and include a unque name field where apropriate. It makes writing generic functions a whole lot easier when you know the schema in advance:

function lookup($table, $name) {

$sql="SELECT id FROM table WHERE name LIKE '$name%'";

... etc

}

I use a hungarian style notation in all my Visual Basic code, but I don't for PHP - it just 'looks wrong' in PHP - go figure