Should my database tables be prefixed?

Question

Quick question: I have the following tables in a database called 'galaxy' - where galaxy the name of my web application.

The tables:

galaxy_user
galaxy_user_profile
galaxy_user_subscription
galaxy_subscription_type

Is it necessary or wise to include the 'galaxy' prefix to all the tables? Or is it better practice to just name them, 'user', 'user_profile', 'user_subscription' , if so why?

Thanks!

score 4 · Accepted Answer · answered Mar 11 '11 at 00:36

4

Database table prefixes might be useful if you need several applications to cooincide within the same database. It prevents different applications from trying to create/use the same name (plenty of applications have a table named user, and this would conflict if they shared the same database)

If you have no plans on having multiple applications sharing the same database then its probably not worth using a prefix since it will just cause you more typing!

Martin

answered Mar 11 '11 at 00:36

Martin Booth

8,485
31
31

> It prevents different applications from trying to create/use the same name — it also prevents the mess when you don’t know which table belongs to what application. – Arseny Mar 11 '11 at 00:39

score 4 · Answer 2 · answered Mar 11 '11 at 00:37

Prefixes are used if you are limited to only one database. However, in normal cases you aren’t, so prefixes are not needed. Some major web frameworks like Django don’t even have that feature (they can, however, use a specified name for the table).

So, the short answer is: you don’t really need to use prefixes unless you are limited to just one database.

score 3 · Answer 3 · answered Mar 11 '11 at 00:37

3

If you are going to use the same prefix for all tables in the database, then it's useless. It's useful if you have separate prefixes for tables with separate purposes.

answered Mar 11 '11 at 00:37

Guffa

687,336
108
737
1,005

score 1 · Answer 4 · answered Mar 11 '11 at 11:55

With MySQL (and maybe other DBMS ?), you can execute queries across several database schemas.

Example:

Schema sales: table product
Schema common: tables person, country

Query example :

SELECT *
FROM sales.product prod
  LEFT JOIN common.person pers ON prod.customer_person_id = pers.id
  LEFT JOIN common.country c ON pers.country_id = c.id
  etc.

This way, you can organize your tables into schemas, avoiding hundreds of tables with unnecessary long names (due to uncomfortable prefixes) into one single schema.

Plus, when you have to query on a single schema, you don't need to prefix tables, provided that you have selected that schema with USE myschema or mysql_select_db() if you use PHP.

Should my database tables be prefixed?

4 Answers4