What is a table prefix?

Question

What is a table prefix, and what are their advantages and disadvantages? This is in relation to MySQL.

Floorman

Are you referring to a table name prefix such as tblTasks or the schema i.e. dbo.tasks – Haberdasher

A table prefix is a prefix that is added to every tablename, as the name implies. – Ulent

Answer 1

This is often used to distinguish different installations of the same script from each other. For example let´s say you have two Joomla Installations with different content on your server, but only one MySQL Database.

Now, for obvious reasons both Joomla installations can´t share the same database tables, as that would result in both installations displaying the same contents. And that is where the prefix kicks in.

By using different table prefixes you can let Joomla Installation #1 know that it is supposed to use all the table with Prefix JOS_ and Joomla Installation #2 has to use all the tables with the prefix JOS2_

Kriemhild

Answer 2

Tables do not require prefixes.

This is purely up to you.

However, we prefix tables with relation to the MODULES in the application they belong to, just to group the tables more easily.

Hydrodynamics

Answer 3

Some people advocate tbl or tbl_ (e.g. tbl_MyTable or tblMyTable) whilst others go with a suffix such as MyTable_T.

Personally I avoid the prefixes/suffixes. I may substitute in a View in place of a Table if a schema is changing over time so I don't really distinguish between the two types of object.

The most important thing is that you have your naming guidelines documented within your team and you all stick to the same set of guidelines for consistency.

Ursal

Answer 4

In a small amount of cases, such as those where malware scripts have been created to target specific types of sites such as WordPress etc., changing the table prefixes has been useful as an extra security measure.

For example, adding table prefixes obscures common table names making it harder for hackers to access data in your database through SQL injection or other security holes because they will first need to discover what your table names are.

Be sure, however, to look at table prefixes as only a very minor layer of security. It should NOT be your main security method. You should still be taking other more important security measures to prevent SQL injection and other similar threats. For example, depending on how your code is set up it may still be possible for a hacker to run a "show tables" command through SQL injection to get the names of your database tables.

Epilimnion

Security through obscurity is not a security. It's an illusion of security. – Costly

@MariuszJamro Did you even read my answer? Also, not all obscurity is just an illusion of security. For example, I just went on vacation and hid some of my technology somewhere in my house in a very difficult to find place. Could someone eventually figure out where it was? Absolutely. Does it add an extra layer of security? Absolutely. Is it an illusion of security just because it is obscuring it? Absolutely not. – Epilimnion

It's an illusion because you feel safer, when you're actually not. In reality it won't stop anyone from reverse engineering your DB structure and figuring out what particular tables actually store/do. – Costly

@MariuszJamro Again, did you even read my answer? I have already addressed your concern in my answer. – Epilimnion

I did and i understand your reasoning. I just find it wrong in context of this question. Table prefixes are not useful for security purposes as you don't get "extra security" by using them. You just make your life harder. – Costly

A few years ago there were various worms going around that would look through vulnerable WordPress sites, and retrieve and manipulate the wp_users table. In these cases, those who had used a different prefix were not affected by these worms. And while having a different prefix was a weak security measure, it was a security measure nonetheless. While the best security would have been for the developers of WordPress to completely eliminate all chances of SQL injection, the end users should not assume that this is the case. – Epilimnion

Answer 5

Strange no one mentioned that you also can use table prefixes to use normally reserved keywords as tablenames.

E.g. t_user or t_order are now possible.

Louvre

Answer 6

If you have a complicated website and database structure, table prefixes may help prevent naming conflicts in the database.

You often see table prefixes in situations where:

• Multiple scripts are being integrated together into one website, and the finished website needs to share data, but the table names would conflict without a prefix unique to each script.

• You are adding functionality to a script you acquired, and you want to distinguish between tables native to that script and new tables you are manually creating. That way if you create a new table, it won't conflict with any future updates to the base script, since it has a different table prefix.

• You have a hosting plan that only gives you one database and you want to use that database to service multiple scripts. (This isn't recommended for a variety of reasons, but I've seen users do this.)

When you are writing a script from scratch, table prefixes usually aren't necessary since you control all aspects of the database structure. It's when you start integrating multiple scripts together that it becomes useful and sometimes even necessary. It allows you to create unique data views, and join tables, and such between multiple scripts without worrying about naming conflicts in the database.

Artemisia

Answer 7

It may help to distinguish between tables and views depending on what your naming convention is.

The disadvantage is that you may be limited as far as the name of a table is concerned. Oracle has a limit of 30 characters for this. If you use "Tbl_" as the prefix, you automatically lose 4 characters. That may be a problem.

Amuse