4 ms·

Do you have anything to say about the practice itself, rather than simply listing a lot of other practices you have associated with it?

by usea 14y ago

zzzeek 14y ago
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" ?
- pyre 14y ago
  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."

Do you have anything to say about the practice itself, rather than simply listing a lot of other practices you have associated with it?

by usea 14y ago

Do you have anything to say about the practice itself, rather than simply listing a lot of other practices you have associated with it?

zzzeek 14y ago

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" ?

pyre 14y ago
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."