3 ms·
A place I worked did something that was at first extremely annoying, but later made a lot of sense -- prefix all the column names of the table with the table na
by borlak 14y ago
A place I worked did something that was at first extremely annoying, but later made a lot of sense -- prefix all the column names of the table with the table name.
So for a table 'user' you would have: user_id, user_name, user_password, etc. Some tables would have ridiculous long column names.
So what is this good for? First off, joins. If you had a 'post' table and a foreign key back to user_id, the naming scheme was: post_userid. So a join would be: "select from post inner join user on user_id = post_userid". There is no need to alias either table. Also, if both tables have the same field, say both have a 'note' field, it is clear which note you are accessing and there are no ambiguous issues (since one table is post_note and the other is user_note).
- mjwalshe 14y agoComplex sql can get long as it is have you not heard of aliaes? Somthing Like. SELECT a.Somthing,b.someotherthing FROM tblSometing a, tbSomthing Else WHERE a.id = b.id
- jcoby 14y agoMaybe I'm missing something but how is post_userid better than post.userid? It's the same length, harder to type, and clutters up your table definitions. I've been through all of these naming schemas over the years (including the OP's tbl prefix and your table_ column prefix) and honestly they don't help. At best they disambiguate a corner case and make for a lot more typing than is needed. At worst you end up with things out of sync and you have a view named tblFoo with a column named post_blah because you don't want to mess up something that was coded a year ago and needs to keep working. Keep things simple, format your queries, be consistent and all will work about as well as it's going to work. SQL is ugly.
- pavedwalden 14y agoThat doesn't sound like a bad system to me, but I think I achieve the same clarity by writing my queries in a more explicit way. I always use multi-part identifiers. For instance, I would join "ON user.id = post.user_id".
- pradocchia 14y agoI have a love-hate relationship w/ column prefixes like you describe. One on hand, they make searching the codebase much easier, and if you can search the codebase, you can refactor w/ confidence. On the other hand, it bakes the schema into every single column reference, and that makes schema changes more costly--either you cruft up your database with now-misnamed columns, or you fix all references, or you avoid changes in the first place, and the business drifts further and further from the database model. My compromise position at places that do use column prefixes has been, column prefixes on base tables, views and/or stored procedures for client access, and no prefixes exposed to the client.