Seeing a big MySQL schema where all the tables look like "tblUsers", "tblAccounts", etc. typically lights up the amateur lights for me - because pretty much when I see that, only about 58% of the tables will actually be named that way. Another 32% will be the other way, "accountsTable", "profileTable"; 10% will not have prefixes or suffixes; about 60% of the tables will be named with a plural name (i.e. "users") and 40% will be singular ("datatype"). Foreign keys being present is pretty much a 50/50 crapshoot (and in the 50% case, it's a further 50/50 chance that they actually knew to use InnoDB so that those FKs have any impact). CamelCase and lowercase_with_underscores will be gratuitously mixed, and MySQL's platform-dependent handling of casing conventions (http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensi...) will go a long way towards making the semi-camelCase application, which probably doesn't quote identifier names (or does so inconsistently; all the SQL is hardcoded), non-portable.
Use What Works: Prefixing Database Tables With 'tbl'
21–30 of 47 posts
Re: Use What Works: Prefixing Database Tables With 'tbl'
#22I dislike the prefix from a stylistic point of view only, the arguments for and against are weak.
I remember debating with our datawarehouse designer whether our tables should be singularly-named or plural. Also if the primary key should be TablenameID or just ID.
In the end it doesn't matter. The only thing that materially impacts productivity is maintaining consistent conventions. Take it from someone currently working on a database with three different object prefix styles, key naming and data access methods (ORM, stored procedures and argh dynamic SQL).
Re: Use What Works: Prefixing Database Tables With 'tbl'
#23adjHungarian nounNotation verbHurts detThe nounReadability prepOf possYour nounCode.
I've been a hungarianNotation hater for several years. I despise the codecademy sections which require variable names use this convention.
Re: Use What Works: Prefixing Database Tables With 'tbl'
#24Earlier quoted context omitted.
So you're recommending writing a stored procedure for this? SELECT COUNT(*) FROM tblposts
Yes. Or alternately, creating a set of views to alias the base tables, and only allowing direct access to the views. Either way gets you some degree of surface area management. Procedures have some added benefit--it's a lot easier to inspect the flow of data when everything is routed through procedure calls. It's a lot easier to put that flow in context when you have a procedure name as a label, provided that your pr…
Coupling queries to the database (stored procedures) instead of to the code that uses them (inlined queries) seems like a recipe for deployment headaches. Especially if you're deploying new code (with new queries) every 2-4 days without downtime.
This approach only makes sense in one of two situations:
1. Ivory Tower DBAs run your company and tell developers "no" at every turn. (sad)
2. Your engineering team makes changing queries hard because they can't hire any developers who know anything about your underlying database platform internals. (also sad)
Re: Use What Works: Prefixing Database Tables With 'tbl'
#25What about when you're not reviewing code or it simply doesn't matter which it is? In my experience that's most of the time. It's just something that's going to return data in a tabular fashion. I'd rather have a clean name all the time and have a little frustration sometimes. Views and stored procedures likely have more detailed and predictable names anyway, 'People' is a table, 'PeopleWithResidentCounty' is a view, 'GetPersonById' is a stored procedure. The practice also hurts what little support SMSS has for quick searching the db object list, instead of staring to type 'Pe' for 'People', I have to type 'tblPe' quickly before the timeout. Also, if 1 person breaks the convention or misunderstands exactly how to use it and it ends up in production, the whole plan falls apart, worse if some code expects the convention to be used a certain way. It's obviously not about machine performance issues, at that level It's about other developers perceptions. If it's just going to be you working on it, fine, whatever, but if you want to collaborate, the practice will cause many developers to question the quality of the entire project and it might reduce their enthusiasm for working on it at all. Note, I have to work on a system using this convention a lot, so it's a bit of a sore point for me, especially since I enthusiastically helped reinforce the practice many years ago :)
Re: Use What Works: Prefixing Database Tables With 'tbl'
#26adjHungarian nounNotation verbHurts detThe nounReadability prepOf possYour nounCode.
[deleted]
Re: Use What Works: Prefixing Database Tables With 'tbl'
#27Seeing a big MySQL schema where all the tables look like "tblUsers", "tblAccounts", etc. typically lights up the amateur lights for me - because pretty much when I see that, only about 58% of the tables will actually be named that way. Another 32% will be the other way, "accountsTable", "profileTable"; 10% will not have prefixes or suffixes; about 60% of the tables will be named with a plural name (i.e. "users") and…
Do you have anything to say about the practice itself, rather than simply listing a lot of other practices you have associated with it?
Re: Use What Works: Prefixing Database Tables With 'tbl'
#28A 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).
Re: Use What Works: Prefixing Database Tables With 'tbl'
#29Earlier quoted context omitted.
Yes. Or alternately, creating a set of views to alias the base tables, and only allowing direct access to the views. Either way gets you some degree of surface area management. Procedures have some added benefit--it's a lot easier to inspect the flow of data when everything is routed through procedure calls. It's a lot easier to put that flow in context when you have a procedure name as a label, provided that your pr…
Coupling queries to the database (stored procedures) instead of to the code that uses them (inlined queries) seems like a recipe for deployment headaches. Especially if you're deploying new code (with new queries) every 2-4 days without downtime. This approach only makes sense in one of two situations: 1. Ivory Tower DBAs run your company and tell developers "no" at every turn. (sad) 2. Your engineering team makes ch…
Please explain to me how "SELECT * FROM tblposts", as an "inline query" used by code, has less coupling than "EXEC GetPosts()"?
Re: Use What Works: Prefixing Database Tables With 'tbl'
#30While at the end of the day being consistent is always the most important thing, I don't agree with prefixes in database names.
If you're not going to be accessing the data using stored procedures your table names are part of the API for your data. Don't reveal your implementation details in your API don't depend on those details in your application. Why forget all the good stuff you know about encapsulation just because the database is involved.