Naming Tables

I like to use plural names unless I know they will make more sense to be singular. e.g. If I am going to use a framework such as Reactor. (I like to write my own code though)

I use plural names as well. In the case of "people", I would probably try to come up with something different. Maybe, "users", "visitors", "humans". :)

On the basis that a convention (any convention) is the most important thing in terms of saving time, I ALWAYS use singular for object name and table name. Just seems to be less trouble as I never have to worry about plurals - even in method names. I don't even getUsers(). I getUserList().

What Peter Bell said makes sense to me. I had not thought of naming a method as getUserList instead of getUsers. It seems that having a singular name will save time.

Tables are a collection of entities: you sort Persons, filter Persons by the last names starting with 'L', etc. Its also a good idea to name the columns of the table by their approximate type: IDNumber, FirstNameString to help make the schema diagrams and queries more readable.

I used to be in the plural name camp because it seemed natural to think of the contents of a table as a collection of items.

I've since changed my thinking to something very close to Peter's approach. If you keep everything singular, you never have to really think about it. It also supports my cfc code generator better since a bean seems awkwardly named if it's plural.

If you had a cfc named "users.cfc", I would expect to see collections returned instead of a single user.

Couldn't you do persons? I know its not grammatically correct but it's not a book, it's a source file.

I've always used plural for table names and then an abbreviation of the table name as a prefix for each field (never longer than 3 chars though). So if I had a "states" table it would have fields called "st_id", "st_name", "st_abbrv", etc... The handy thing about the abbreviation is that I never end up with a query that has 4 different "id" fields. I'd have "st_id", "cit_id", "co_id", and "usr_id" if I were joining state, city, country, and user tables. Makes foreign keys easy to figure out too - a city record's foreign key to a state would be "cit_st_id". And a state's to country would be "st_co_id"

Like Peter Bell said, the important thing is to have a convention, and not necessarily what that convention is. As a DBA myself, that's not always easy (especially when you're cleaning up after poor previous DBAs), but it does make things easier in the long run.

Also, precursor conventions are nice as well, especially when you have collections of similar data. At my job, we have a CMN_xx set of tables for collecting common data from our clients, and then a set of YYY_xx set of tables for the particular extra information that each client requires us to store. That helps keep table groupings in order as well.

Just as a follow up, the conventions I tend to use for columns are to prefix "object" name to the ID field of the primary identity key (if I have one) and to do the same for foreign keys.

In User table, identity is UserID. If user has a company FK, it is CompanyID (again assuming "ID" FKs).

I personally don't love hungarian style notation (putting data type into name) as it makes refactoring harder as it is not unusual to have to refactor data types. I REALLY dislike hungarian in coe as it makes refactoring a lot more work, but I guess it isn't as bad in tables as long as you alias everything in your DAOs. I get the benefits, but I'm more than happy to use design view (or metadata or runtime reflection) to get the data type as a separate bit of data. Just my preference.

I also try to avoid prefacing column names with the "object" name as a convention so it is FirstName, not UserFirstName. Only exception that comes up is Username which has a fundamental meaning.

I've played with package naming of tables like Greg and am mixed. I like putting commerce and cms tables close to each other but can't decide if it is good or bad as a rule.

Great feedback everyone thanks!

I use the conventions our DBAs specified:

http://livedocs.macromedia.com/wtg/public/coding_standards/database.html

Hi Sean,

I agree 100% with each item. Only one comment, though, WHY IS IT THAT ALL DBAs THINK THEY HAVE TO SHOUT?!

I know they aren't as cool as programmers, but shouting at us all the time won't fix that :->

I'm going to be the odd ball in the group. We name all of our tables using a convention. Each application, or group of applications of the same type get a two character prefix. For instance we have a project management application we built and its prefix is pj. then we have two characters to tell us what sort of file it is, because our logicals and indexes are first class citizens on the as/400, so our tables or physicals have pf (for physical file) and our indexes have ix and our logicals have lf. then we have three characters to name the table, so a users table is usr, our project requests table is req, etc. Then we have two numbers after it. All physicals are 00, primary key indexes are always 00 as well as unique logicals, and then each logical or index after the primary is 01,02,03 etc. So for instance, our project management users table is pjpfusr00, the primary key logical is pjlfusr00, and then we have pjlfusr01, pjlfusr02, etc.

But where that really gets helpful though is in the naming of the columns. Our columns are the two character prefix, the three character table name, and then you have five characters to actually name the field. So our username field in the users table is pjusruser. What makes this nice is if you are using a field, you automatically know what table it came from, and it makes joins much much easier. It seems like a lot of typing at first, but when all of your tables are 9 characters long and all of your columns are a max of 10 characters long, it really makes your sql a LOT easier to read and maintain.

This was mainly done for the rpg work done on the 400, but it lends itself well to web development too.