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.
You have kind of contradicted yourself. "Seeing a big schema where all the tables look like ..." and "only about 58% of the tables will actually be named that way..."
So which is it? "All the tables" or "58%".
What you're trying to say is that inconsistency lights up the amateurs lights.
The collection of names that you set up for "relations" (in the Codd sense) in a schema is the primary "surface" of that schema. Whether they are tables, views, or synoynms to those (i.e. Oracle), each of these names presents the same interface - a collection of rows. Prefixing or suffixing these names detracts from the perspective of the database as a collection of relations, it gets in the way and makes it more difficult to make names interchangeable. The "tbl" prefix is a defensive decision that is unnecessary and detracts from the notion of the schema as a collection of relations.
It is more common to have qualification for objects that aren't collections of rows, like sequences, constraints, and indexes. These are qualifications like "fk_", "pk_", "uniq_", etc. and they serve the purpose of being able to distinguish between "relations", which are the primary API of the database, and "supporting" constructs. These names need to be distinguished from table names as well as from each other and with the exception of sequences are also not present in SQL statements, only DDL.
Consider why none of the other names in relational databases are prefixed as to their type. We all use functions like "current_timestamp", "count", keywords like "CAST", fixed system tables and views like "pg_catalog". Why aren't these named "fnCurrentTimestamp", "fnCount", "keywordCAST", "tblPgCatalog" ?
We suffix views with '_vw' and materialized views with '_mv'. This makes it easy to not have to consult the DDL every time we need to know what a table is. I've also run into several instances of:
object_name_mv
object_name_vw
I can only assume that at some point the view was non-performant for user-facing application and was turned into a snapshot.
In general, I'm a fan of suffixing views with '_vw' to basically be a red flag saying "there's another query behind me."
