Is prefixing each field name in a table with abbre

Question

Do you prefix each field in a table with abbreviated table name?

Example:

Table: User

Fields:
user_id
user_name
user_password

Or do you do you name your fields minimally?

Fields:
id
name
password

If you have used both, then which format you feel helped you most in the long run?

Edit: This question doesn't seem to have a definite answer and both side presents good points. But I have kept the question open for too long time and maybe it's time to mark one answer as accepted. I am therefore marking the highest voted one as accepted.

Answer 1

if you do it you will end up writing queries like:

SELECT user.user_name, user.user_password, user.user_firstname ...

instead of

SELECT user.name, user.password, user.firstname

so IMO the answer to your question is quite clear.

Answer 2

When I add the field "ordinal" to a table I like to add in a prefix so I don't have to alias ordinal fields from other tables in JOINS. It's handy for JOINS sometimes... not sure I have seen other benefits.

MediaWiki (the Wikipiedia software) uses that convention. Download the source. They limit themselves to a two character prefix.

I don't recommend the practice though. For most databases its not necessary.

Answer 3

Don't do that. It's redundant and leads to frustration in the long run.

The only field, where you could apply this might be id, because user_id would obviously be the id of the user and it would simplify writing joins in SQL. But I wouldn't even do that.

Answer 4

The prefix variant just takes longer to write and makes it harder to read sql statements with many fields.

Even when you are selecting from several tables, this gives you only the benefit of not having to prefix ambiguous fields with the table name. But

SELECT user.name, image.name FROM user, image

is not very different from

SELECT user_name, image_name FROM user, image

The benefit of havong no ambiguous fields in your queries is quickly eaten up by the overhead of having to type the table name each time you are using a column name.

Answer 5

I'd recommend sticking with table aliases, like:

SELECT 
    user.id,
    user.email, 
    user.firstname, 
    user.secondname,
    avatar.filename
FROM
    pain_in_the_butt_table_name user
LEFT JOIN
    table_with_the_avatars avatar
ON avatar.user_id = user.id

The advantages:

• maintaining an easily comprehendable list of fields you select, and which tables you take them from

• avoid typing long or hard to comprehend table names and replace them with a short and understandable name (which you should have done when creating the table)

• perform joins that are readable (your example would read:

LEFT JOIN table_with_the_avatars.user_id ON user.user_id = table_with_the_avatars.avatars_user_i

• create even shorter aliases, in my example that would mean u instead of user, and a instead of avatar, shortening your query

Answer 6

For all the reasons given, I don't think this is a good idea. Besides, you don't prefix all the methods in your classes with the class names, do you? So why do it for database objects?