The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database table naming conventions (prefixes)

I'm looking at a legacy database and the tables seem to be grouped into three sets, each set begining with a certain prefix:

lu_ is a shorthand for lookup and refers to tables containing data that's unlikely to change and may act as foreign keys. Example: the 50 states.

dat_ of course refers to tables containing frequently updated data. Example: street addresses.

br_ appears to be relational tables but it's uncertain what the B stands for. Example: a list of addresses associated with an individual, i.e., all of his addresses.

Has anyone ever seen a naming scheme like this and might be able to provide some additional details? I see using the lu_ and dat_ prefixes being useful (as in lu_location and dat_location), and are there any other convenient tricks for choosing table names?
Thursday, August 30, 2007
well, at first br_ sounds like it might be used for "business rules".

but the way you describe the user and the addresses, maybe bridge?
Thursday, August 30, 2007
I find it useful to have tables with a short prefix for their subject area and a suffix for their type; such as: "INS_CONTRACT_LOG"
hobit Send private email
Thursday, August 30, 2007
In general I find almost all such naming schemes (i.e. attaching type and usage information to identifiers) to be of little use. That said, I reflexively name database tables and views with T_ and V_ (and stored procedures started with SP_, for pity's sake!) because I spent five years at a company that used exactly that convention. I still think it was a dumb way to do things. It's almost the same silliness as using hungarian notation on non-structured variables (e.g. "lpc_name" or "li_count" instead of simply "name" or "count").

The only naming scheme I like for database tables is to name each primary table as a noun (e.g. CUSTOMER, ADDRESS, PHONE_NUMBER, ORDER, ...) and each many-to-many linkage table with the names of the primary tables that are related through the linkage table (e.g. CUSTOMER_ORDER, ADDRESS_PHONE_NUMBER, ...). In this circumstance, however, I could see adding a very short prefix to each table name to indicate a primary table or a linkage table, to avoid confusion (e.g. P_CUSTOMER, P_ORDER, L_CUSTOMER_ORDER, ...).
Jeffrey Dutky Send private email
Thursday, August 30, 2007
I don't see the point of prefixes for table names. Surely, the name itself should be sufficient?
Entries of Confusion Send private email
Friday, August 31, 2007
What P_ stand for? What INS_ stand for?? I don't know why so many people like to use those short prefixes and let user guess what that prefixes mean.

Most DBMS already support auto-complete. And it probably not to hard to type a little bit more code at application coding. Why we still need to have short column name like t_ins_abc_val ??
Carfield Yim Send private email
Friday, August 31, 2007
"And it probably not to hard to type a little bit more code at application coding."

It adds up when you're doing a lot of database interaction. That goes double for column names because unless you're using some kind of ORM you'll generally reference them twice when you do a select: once in the query itself and once when you access the column in the result.

Generally I prefer column names to be as short as possible without being ambiguous. Having a basic set of domain-specific abbreviations that the whole organization understands is helpful.
Friday, August 31, 2007
Well, in this case, the prefixes (or suffixes) are meant to imply the context that the table is used in. If you have a rather long list of table names, it can also be used to easily group similar tables together.

Also, there's nothing wrong with excessively verbose table names, but when you have 20+ tables that all begin with PEOPLE_, the natural inclination is to look for a way to abbreviate the PEOPLE_ prefix. Perhaps P_ is enough.
Friday, August 31, 2007
Boy, that's a toughie, especially since you used addresses for examples for both dat_ and br_.

Maybe they are addresses of all Baskin Robbin's ice cream stores?  (Just kidding)

One thing I didn't see was notation for master file data.  Customers, products, warehouses, things like that.  For the life of me, I can't think of how BR might represent that.

Since addresses are in dat_ and br_, could br_ be receivers for journals?  If a file is journaled, all updates can be written to another file called a receiver.  The DBA usually has a choice of journaling the "before update" image and the "after update" image.  It is common to journal only the before-image since the after-image is in the file just updated.  In other words, maybe BR stands for Before update Receiver.  Just a wild guess...
Friday, August 31, 2007
OneMist8k's suggestion is actually a pretty good guess. It didn't occur to me that these terms might be used for maintaining the database itself, as opposed to organizing the data.

Since addresses were a bad example, I should say that the br_ tables were originally used mainly for relationships, i.e., primary and foreign key pairs, but over time have been extended to include columns unique to that relationship as opposed to properly normalizing them. Achilles may be right in that they're bridge tables or represent business rules.

But by that same token, you would want to index such tables a little differently than normal. For example, you'd want to index pairs of foreign keys instead of a single column. You may similarly want to journal the contents and so on...
Friday, August 31, 2007
I tend to use prefixes on databases with lots of tables just to separate tables related to different parts of application. For example: accounting tables start with ACC_, payroll with PAY_, human resources with HR_.
That way, when there is lots of tables it is easier to find one you need.
This is practice I picked up from Oracle designer long time ago.
Sunday, September 02, 2007
Does anyone remember FORTRAN 66 and its default type rules. Prefix I = integer, Prefix F = floating, C = complex, etc.

After spending years maintaining such really old code I now hate to see such prefixes, including now someone's MS-Access code with TBL, RPT, etc.

Things should be named well enough to explain their meaning.
Tuesday, September 04, 2007
I like prefixing tables with "tbl" because you want to differentiate tables from views.

Prefixing stored procedures with "ins", "upd", and "del" are excellent ways of identifying them in your code or in other stored procedures, and they'll list in a group.  Of course, you can always use a suffix instead if you prefer to group things differently.

BTW, it's bad practice to prefix a select stored procedure with "sp_", since that is the default for system stored procedures and SQL Server will waste time looking through those before it finds your.  I use "sel" or no prefix as a default.
Tuesday, September 04, 2007
Our app has 500+ tables, it feels nice to be able to categorize them eg. oeOrder, oeFill, oeNotes for all Order Entry related tables.
Greg Send private email
Wednesday, September 05, 2007
It sounds like there isn't even a lazy standard, people just use what makes sense for the job at hand.

Thank you everyone for the replies. I can see myself using many of these suggestions, both the abbreviations in some databases where appropriate, and spelling everything out in other databases.
Thursday, September 06, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz