3 ms·
There is actually a very good reason NOT to do this. At some point you might need to denormalize, replacing a view with a materialized view. Now you either nee
by yummyfajitas 14y ago
There is actually a very good reason NOT to do this.
At some point you might need to denormalize, replacing a view with a materialized view. Now you either need to change every instance in your code, or you need to have a table prefixed with "vw". (Similar troubles apply if you replace a table with a view.)
The entire purpose of views is that from the perspective of the client, it doesn't matter whether it's a table or a view.
- SQLRockstar 14y ago...or, I could use "mvw" to designate a new type of object... I agree, the client shouldn't care about the data being a table or a view. But as a DBA, if I need to quickly find a solution to a problem, the use of a prefix can be a benefit. There are costs and risks for all design choices. If you are working with a system that is growing and prone to the need of denormalizing frequently then perhaps using a prefix isn't the right choice. But not every system has that issue. I see more cases these days of smaller databases...think "one database per customer" type of architecture. Denormalization isn't an issue often, and prefixes seem to work just fine.
- naelyn 14y agoI don't agree with prefixing tables with "tbl" to distinguish them from views, but I do agree with the general notion of using the prefix for any "table-like" entity in a database. If you work with a growing codebase that slowly falls into the (popular) antipattern of DatabaseAsIntegrationPoint, you will end up with dozens of programs spread out over multiple repositories all interacting with some shared tables (not ideal, but it happens). If you named your tables something like "posts" then I _guarantee_ that if you grepped your entire repository for "posts" that you are going to find an awful lot of false positives in variables and class names. OTOH if you named it "tblposts" then it's far more likely to be a globally unique string identifier. Why would you need to grep the codebase for a table name you ask? * prerequisite to a non-additive table ALTER that might have unintended side effects * prerequisite to trying to undo the carnage of DatabaseAsIntegrationPoint
- bunderbunder 14y ago> If you named your tables something like "posts" then I _guarantee_ that if you grepped your entire repository for "posts" You shouldn't have to do that in a well-factored appliation, because you're using stored procedures instead of inline SQL.
- naelyn 14y agoSo you're recommending writing a stored procedure for this? SELECT COUNT(*) FROM tblposts
- pradocchia 14y agoYes. 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 procedures implement a batchful interface.
- naelyn 14y agoCoupling 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)
- alphadogg 14y agoPlease explain to me how "SELECT * FROM tblposts", as an "inline query" used by code, has less coupling than "EXEC GetPosts()"?