Database Table Prefixes

Question

We're having a few discussions at work around the naming of our database tables. We're working on a large application with approx 100 database tables (ok, so it isn't that large), most of which can be categorized in to different functional area, and we're trying to work out the best way of naming/organizing these within an Oracle database.

The three current options are:

• Create the different functional areas in separate schemas.
• Create everything in the same schema but prefix the tables with the functional area
• Create everything in the same schema with no prefixes

We have various pro's and con's around each one but I'd be interested to hear everyone's opinions on what the best solution is.

Edit: Thanks for all the answers. It's hard to pick a right answer seems it is very subjective so I'll go for the one with the most votes (it helps that it matches what I was thinking! ;-)

Answer 1

Definitely don't name every table with a prefix. I tried that once and it's annoying to maintain and keep straight, especially since some tables apply to multiple sections.

I currently work with a system which has no naming prefix for most tables, but there is a naming prefix for modules-specific tables

For example, we have the usual Users, Customers, Products tables for most objects, however a module that is specifically designed to work with customer relations management might prefix it's module-specific tables with CRM_

I would use separate schemas for sections that are completely separate from each other, however if there are links between the two (such as the CRM_ module linking to the main Customers table), then I'd probably just add the module-specific tables to the existing schema and prefix them with something

Answer 2

Creating everything in separate schemas is what makes the most sense, IMO. It provides a logical separation, and also will allow you to easily organize what you are doing.

If your application grows, it will become easier to replicate data over to separate read servers as well, since you could split out to have one or more read servers per-schema. It will be much easier to track that way.

Answer 3

In my opinion, Prefixes don't seem to have physical or logical value, except in cases like this:

• To distinguish normal tables from aggregated summary tables. This is to remove confusion from Sales and Sales summary tables without using the word summary.

• To distinguish views from actual tables

• When you deal with demoralized database where a single table has to appear twice to distinguish between staging area data vs. transactional data.

Prefixing tables is not possible to be done right because it is common to find shared tables across business functions. For example, a table like Country, can be part of the functional areas: Payroll, Sales, Accounts, Security, etc.

Information Engineering methodology promotes logical categorization of tables into what is referred to as Business Subject Areas. However, in the physical implementation, there is no such division and there is no name prefixing required.

Advanced data modeling tools allows you to group tables in separate groups from a main model and allow the admin. of the main model to provide access rights so that each sub-model can be viewed and changed in a controlled manner.

Another problem with prefixes, is that they make names longer without providing value to either developer, dba or end user.

I need to mention here that Microsoft Dynamics have a different approach to this. Tables in MS Dynamics are prefixed with some prefix, for example:

• CustBankAccount
• BankAccountStatement
• BankAccountTrans

Again, personally, I don't see the value of prefixes except where mentioned above.

Answer 4

I did use schemas because they also make it easy to handle security on the tables: you can define what type of access each user or group of user on the overall schema.

I also have table such as Users or Currencies which are used for every department and I created a Common schema for it.

I think it does make a lot of sense and it's definitely the easier to maintain.

Answer 5

1. Prefixes are best if you:

a) Want to be able to install multiple copies of the same application within a single schema or database

b) Have to provide protection against SQL injection especially for common CMS platforms

1. However I find them a drag and problem. Your tables should be named in such a way that their names define their contents, just like @Emmad Kareen has described from Microsoft Dynamics above.

2. I am from the MySQL world where applications that use multiple databases are not that common, though they are in Oracle and other databases.

3. Also what is common is that you use one application user to connect the application to the database then use applications security to control what a user can see or access.

My advice, stay away from them as they make development and ORM mapping more complex

Answer 6

I wouldn't do it. More than that - if you can clearly split your application into different sections handling each a different functionality, you could just go message-bus-oriented all the way and make them completely separated apps, each with data in a separate schema (or even server) all passing messages through a shared bus under a publish/subscribe mechanism.

So, to keep the answer short, don't. If you try to guess where the table will be used at this time, you'll probably be wrong and be annoyed forever because crm_users table will unavoidably be used in the identity manager. Your question makes me suspicious your organization is trying a BDUF approach. Whatever you think you know about the system now, trust me, you really don't. ;-)