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:

PhpMyAdmin result

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.

RandomSeed · Accepted Answer · 2014-07-25T14:58:55.307

4

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.

edited Jul 25 '14 at 14:58

answered Jul 25 '14 at 14:50

RandomSeed

29,301
6
52
87

I suppose the main idea was avoiding name collisions so if I need id when joining tables, I don't need to write t1.id as `t_id`, t2.id AS `t2_id` but that's why I asked the question to know your opinion. +1 from me – Marcin Nabiałek Jul 25 '14 at 14:54
One of the reasons why I avoid having columns called `id` in my tables is to avoid this mess. – Strawberry Jul 25 '14 at 15:31
@MarcinNabiałek I am not sure I fully understand your point. You never *have to* alias your columns: you can write `SELECT t1.id, t2.id FROM t1 JOIN t2 ON t1.id = t2.id` without an alias (i.e. you can refer to these columns as `t1.id` or `t2.id` anywhere in the query). – RandomSeed Jul 28 '14 at 08:56
@RandomSeed Sorry, it seems that I wasn't clear enough. Please look at **Edit2** in my question – Marcin Nabiałek Jul 28 '14 at 09:27
I see. This is what I was trying to cover with the second part of my answer. You can address the issue by aliasing the columns like this: `SELECT user.name AS user_name, country.name AS country_name`. While I *dislike* the idea of prefixing *all* column names in the table definitions, it is probably acceptable to prefix *those* columns names that would otherwise have the same name as another column in another table. I still *prefer* aliasing columns when required, but we are entering a gray area here. – RandomSeed Jul 28 '14 at 09:39
Oh, and in most cases, `SELECT *` is a bad idea, so in my opinion, the common argument "meh, but then I need to list all my columns explicitely instead of just `SELECT *`" does not stand. – RandomSeed Jul 28 '14 at 09:43
@RandomSeed Thank you for your time. I know that usually `SELECT *` is not the best option but still in this case aliases need to be used. But looking at overall answers it seems that's the way it should be done, without using prefixes for columns. – Marcin Nabiałek Jul 29 '14 at 08:57

Sander Visser · Answer 2 · 2014-07-25T15:05:02.350

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`

+1, consistency is more important than good practice when dealing with legacy code — RandomSeed, Jul 25 '14 at 15:05

score 1 · Answer 3 · answered Jul 25 '14 at 15:05

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.

Column prefixes in tables

3 Answers3