Column prefixes in tables

Question

First I need to point that I read Database columns type prefix but it's not the same issue.

A long time ago someone who I worked with told me that in project I took part, all columns need to have unique prefix.

For example for users table I use prefix u_ so all columns are named u_id, u_name and so on. The same with all other tables for example for products it will be p_ prefix.

The reason of that was easier SQL JOINS - all columns will have unique names if 2 or more tables would be join. To be honest I've used this suggestion so far but in fact I don't know if it is used by many of you or it's really so useful.

What's your opinion on that? Do you use such column naming or maybe you think this is completely unnecessary and waste of time? (when displaying data you need to use prefixes if you don't remove them using function or foreach)

EDIT

Just in case more explanation

Assume we have users table with fields id, name and address table with fields id, name, user_id

In case if this method is used if we want to get all fields we can do:

SELECT * 
FROM users u 
LEFT JOIN address a on u.u_id = a.a_user_id

And in case we don't use prefixes for columns we should use:

SELECT u.id AS `u_id`, 
  u.name AS `u_name`, 
  a.id AS `a_id`, 
  a.name AS `a_name`, 
  a.user_id 
FROM users u 
LEFT JOIN address a on u.id = a.user_id

assuming of course we want to use columns as names and not numeric indexes 0,1 and so on (for example in PHP)

EDIT2

It seems that I haven't explained enough what's the problem - in MySQL of course in both cases everything works just fine and that's not a problem.

However the problem is when I want to use data in programming language, for example PHP. If I use:

<?php

$db = new mysqli('localhost','root','','test_prefix');

$result = $db->query("SELECT * FROM `user` u LEFT JOIN `address` a ON u.id = a.user_id ");
while ($data = $result->fetch_array()) {
    var_dump($data);        
}

I get:

array(8) { [0]=> string(1) "1" ["id"]=> string(1) "1" 1=> string(5) "Frank" ["name"]=> string(3) "USA" [2]=> string(1) "1" [3]=> string(3) "USA" [4]=> string(1) "1" ["user_id"]=> string(1) "1" } array(8) { [0]=> string(1) "2" ["id"]=> string(1) "2" 1=> string(4) "John" ["name"]=> string(6) "Canada" [2]=> string(1) "2" [3]=> string(6) "Canada" [4]=> string(1) "2" ["user_id"]=> string(1) "2" }

Whereas result in PhpMyAdmin for that query look like this:

In PHP get all the data but I can access data using numerical indexes: $data[0], $data[1] and that's not very convenient. I cannot use user name because in $data['name'] there's only address name, the same in id. If I used any of both: aliases for columns or prefixes for columns I would be able to use string indexes for accessing data for example $data['user_name'] to access User name and $data['address_name'] to access Address name.

Answer 1

I'd go so far as to say it's a dangerous practice as it encourages sloppy coding.

Suppose you have 2 tables: User and Usage

select u_type, us_name
from User
inner join Usage on u_id = us_id

Which field is coming from where? You'd need to go look at the table structures to determine it and it can be tempting to make assumptions in these cases.

select u.type,us.name
from User us
inner join Usage u on us.id = u.id

Now you have all the information you need right in front of you.

Answer 2

I think it's unnecessary, BUT in the name of consistency on an existing project you should maintain it or refactor the whole database.

Why it is unnecassary? Well take a look at the following query wich illustrates how you can get whatever you want out of the database

Also i think it's more readable and the alliasing works fine

In the cases where your column names collide wich doesn't work that well with some drivers you could use the AS statement to get that specific field because you can JOIN the same table twice wich gives you the exact same problem anyway when you use the prefixes

SELECT 
  `m`.*, 
  `u1`.`username` AS `sender`, 
  `u2`.`username` AS `receiver`
FROM `messages` `m` 
INNER JOIN `users` `u1` ON `m`.`sender` = `u1`.`id`
INNER JOIN `users` `u2` ON `m`.`receiver` = `u2`.`id`

Answer 3

I believe this is stupid. You actually end up prefixing all columns in all queries with their table names (or "identifiers"), even where there is no ambiguity.

If you compare:

SELECT t1_col1, t2_col1
FROM t1, t2;

... with:

SELECT t1.col1, t2.col1
FROM t1, t2;

... then the recommendation may appear sensible.

Now compare:

SELECT t3_col3, t4_col4 FROM t3, t4;

... with:

SELECT col3, col4
FROM t3, t4; -- assuming col3 exists only in t3, and col4 only in t4

Now where is the benefit?

One can still argue that a one-or-two letter prefix is still preferable to a long table name:

SELECT t1_col1, t2_col1
FROM very_long_table_name1, very_long_table_name2;

But why bother with a prefix when you can do:

SELECT t1.col1, t2.col1
FROM very_long_table_name1 AS t1, very_long_table_name2 AS t2;

---

Actually, there could be cases where the prefix might come in handy (handy does not mean recommended in my mind). For example, some drivers (I'm thinking old PHP) may get confused when multiple columns of a result set have the same name (because they return rows as an array indexed by column name). The problem could still be worked around by aliasing the columns in the result set.