12

We are deciding the naming convention for tables, columns, procedures, etc. at our development team at work. The singular-plural table naming has already been decided, we are using singular. We are discussing whether to use a prefix for each table name or not. I would like to read suggestions about using a prefix or not, and why.

Does it provide any security at all (at least one more obstacle for a possible intruder)? I think it's generally more comfortable to name them with a prefix, in case we are using a table's name in the code, so to not confuse them with variables, attributes, etc. But I would like to read opinions from more experienced developers.

Anthony Mastrean
  • 21,850
  • 21
  • 110
  • 188
Fernando Briano
  • 7,699
  • 13
  • 58
  • 75

7 Answers7

25

I find hungarian DB object prefixes to indicate their types rather annoying.

I've worked in places where every table name had to start with "tbl". In every case, the naming convention ended up eventually causing much pain when someone needed to make an otherwise minor change.

For example, if your convention is that tables start with "tbl" and views start with "v", thn what's the right thing to do when you decide to replace a table with some other things on the backend and provide a view for compatibility or even as the preferred interface? We ended up having views that started with "tbl".

Dustin
  • 89,080
  • 21
  • 111
  • 133
  • 9
    I find the whole hungarian concept antiquated today anyway. I think it's just leftover from the days where it was harder to organize things and sometimes the name of an object was the only distinguishing mark. – Chris Nov 27 '08 at 18:00
  • I think I can agree with you as long as the default schema is either not used or is not set. It's a lot harder to search for usages when it could be 'MyTable', '[dbo].MyTable', 'dbo.MyTable', 'MyDatabase..MyTable', 'MyDatabase..[MyTable]', etc.; this is increasingly more difficult when other objects in other schemas within the same database have the same name. – tuespetre Mar 31 '14 at 13:48
11

I prefer prefixing tables and other database objects with a short name of the application or solution.

This helps in two potential situations which spring to mind:

  1. You are less likely to get naming conflicts if you opt to use any third-party framework components which require tables in your application database (e.g. asp net membership provider).

  2. If you are developing solutions for customers, they may be limited to a single database (especially if they are paying for external hosting), requiring them to store the database objects for multiple applications in a single database.

Ian Nelson
  • 57,123
  • 20
  • 76
  • 103
  • 3
    The only caveat about prefixing table names is that some tables may end up being used by multiple applications, in which case, the original prefix becomes misleading. In the worst case, the App1 prefix survives long after App1 itself has gone the way of the dodo. – Jonathan Leffler Nov 27 '08 at 17:47
  • 7
    Prefixing application names adds no value. 1. 3rd party components should not be going under the same schema/owner as your app tables to cause naming conflicts in the first place. 2. Limited to single database does not mean limited to a single schema. – Amit Naidu Jul 29 '11 at 03:30
  • 3
    @AmitNaidu I agree. I have totally changed my mind in the five years since I posted this question. If I could, I would delete the answer. – Ian Nelson Mar 28 '14 at 17:31
  • @AmitNaidu but what if [I want to name my table `user`, which is a reserved word in PostgreSQL](http://stackoverflow.com/questions/22256124/cannot-create-a-database-table-named-user-in-postgresql)? Using prefixes would solve this conflict. – ma11hew28 May 04 '16 at 23:42
  • Both of these two situations are quote edge casey to most people (me included). It's less verbose & neater to not prefix. – niico Jun 24 '16 at 15:28
8

I don't see how any naming convention can improve security...

If an intruder have access to the database (with harmful permissions), they will certainly have permissions to list table names and select to see what they're used for.

But I think that truly confusing table names might indirectly worsen security. It would make further development hard, thus reducing the chance security issues will be fixed, or it could even hide potential issues:

If a table named (for instance) 'sro235onsg43oij5' is full of randomly named coloumns with random strings and numbers, a new developer might just think it's random test data (unless he touches the code that interact with it), but if it was named 'userpasswords' or similar any developer who looks at the table would perhaps be shocked that the passwords is stored in plaintext.

Stein G. Strindhaug
  • 5,077
  • 2
  • 28
  • 41
  • 1
    If the intruder can only conduct, say, a limited SQL Injection attack then its far from sure they will be able to list table names. Having said that - this would be a pretty weak additional security measure (and should never be used for security really). – niico Jun 24 '16 at 15:40
3

Why not name the tables according to the guidelines you have in place for coding? Consider the table name a "class" and the columns a "property" or "field". This assists when using an ORM that can automatically infer table/column naming from class/member naming.

For instance, Castle ActiveRecord, declared like below assumes the names are the same as the member they are on.

[ActiveRecord]
public class Person
{
    [PrimaryKey]
    public Int32 Id { get; set; }

    [Property]
    public String Name { get; set; }
}
Anthony Mastrean
  • 21,850
  • 21
  • 110
  • 188
  • The new link for ["assumes the names are the same"] [ https://github.com/castleproject-deprecated/ActiveRecord/blob/master/docs/README.md ] – iYazee6 Dec 06 '20 at 06:31
2

If you use SqlServer the good start would be to look at the sample databases provided for some guidance.

kristof
  • 52,923
  • 24
  • 87
  • 110
2

In the past, I've been opposed to using prefixes in table names and column names. However, when faced with the task of redesigning a system, having prefixes is invaluable for doing search and replace. For example, grepping for "tbl_product" will probably give you much more relevant results than grepping for "product".

Johan
  • 110
  • 1
  • 6
  • Indeed, the more unique terms the better. It makes support easier too when digging in your codebase. – mukunda Apr 20 '22 at 02:43
-4

If you're worried about mixing up your table names, employ a hungarian notation style system in your code. Perhaps "s" for string + "tn" for table name:

 stnUsers = 'users';
 stnPosts = 'posts';

Of course, the prefix is up to you, depending on how verbose you like your code... strtblUsers, strtblnmeUsers, thisisthenameofatableyouguysUsers...

Appending a prefix to table names does have some benefits, especially if you don't hardcode that prefix into the system, and allow it to change per installation. For one, you run less risk of conflicts with other components, as Ian said, and secondly, should you wish, you could have two or instances of your program running off the same database.

nickf
  • 537,072
  • 198
  • 649
  • 721