Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Use What Works: Prefixing Database Tables With 'tbl'

thomaslarock.com

Post reply on HN
Print view View original

31–40 of 47 posts

Re: Use What Works: Prefixing Database Tables With 'tbl'

#31

Post by usea »

slurgfest wrote: post #18
SQLRockstar wrote: post #7

Earlier quoted context omitted.

Only when you don't use prefixes that are somewhat standard. At the end of the day, however, consistency is key. For example, you used a capital letter to distinguish the start of a word, making it easier for me to understand what you were saying.

I don't see how capitalizing all words makes it easier to understand English sentences (Can You Hear Me Now)?

Your parent comment is referring to the use of camelCase when smashing words together, not to capitalizing every word of a sentence.

Re: Use What Works: Prefixing Database Tables With 'tbl'

#32

Post by pradocchia »

I'm sympathetic to this argument, to a point.

For database code, executing in the database (ie, stored procedures and the like), table prefixing does make the code more searchable, and reduces the amount of context one needs to grok a single block of code. When you need to refactor, you can quickly find all references.

But as a public face to clients, I find that tbl-prefixing exposes too much implementation detail. Tables and views are both relations, and there's a continuum from physical relation to virtual relation. If I use the same naming convention for both, I can keep my public API constant while iterating on the physical schema. This is very useful to me. Consequently, within the API, I try to keep views simple, and write them to provide table-like performance.

Re: Use What Works: Prefixing Database Tables With 'tbl'

#33

Post by mjwalshe »

borlak wrote: post #28

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 wa…

Complex 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

Re: Use What Works: Prefixing Database Tables With 'tbl'

#34

Post by jcoby »

borlak wrote: post #28

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 wa…

Maybe 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.

Re: Use What Works: Prefixing Database Tables With 'tbl'

#35

Post by bunderbunder »

naelyn wrote: post #24
pradocchia wrote: post #20

Earlier 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…

There's two huge reasons to keep procedures in the database, neither of which has anything to do with ivory towers. They're really about the most basic tenets of good coding practice:

1. Maintainability. It's necessary to put your SQL on the server if you want to keep it well factored. Just like for any other language, oft-repeated bits of SQL code should be factored out into separate procedures and functions. If you're relying on inline SQL, you're forced to choose between habitually violating the DRY principle or resorting to an unmaintainable mishmash of server-side and client-side queries.

2. Testability. The good unit testing frameworks for SQL code are written in SQL, and designed to be used from an SQL development environment (i.e., the database). And just like for any other language, your SQL code should be covered by good tests.

There are plenty of tools out there to help with deployment if it's causing difficulties for you. I recommend using them if that's what it takes for you to be comfortable with the platform.

Re: Use What Works: Prefixing Database Tables With 'tbl'

#36

Post by mgkimsal »

emp_ wrote: post #10
SQLRockstar wrote: post #4

Earlier quoted context omitted.

Fair point. But how much time have I wasted trying to root out a performance problem caused by nested views disguising themselves as tables? The tools have been failing us. Using a prefix is a way to get around those failings.

This is the same as the "don't use aliases but instead tablename_id for Id fields because at 3am this will help you" type of argument (a real one, unfortunately) you should not model things around exceptions.

and... if debugging live code on production machines at 3am is the norm, you've got much bigger problems than table prefixes.

Re: Use What Works: Prefixing Database Tables With 'tbl'

#37

Post by zzzeek »

usea wrote: post #27
zzzeek wrote: post #21

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…

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

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

Re: Use What Works: Prefixing Database Tables With 'tbl'

#38

Post by pradocchia »

naelyn wrote: post #24
pradocchia wrote: post #20

Earlier 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…

I try to be the guy who interops between the reactionary DBAs and the happy-go-lucky developers--two parties that optimize for different ends.

So most recently, we had an app that started off with direct table access via an ORM. Once the data access paths stabilized somewhat, I started replacing them with stored procedures. Those stored procedures gradually coalesced to form an API. The ORM-like functionality is still there, if need be, but the stored procedures now provide a contract, much like a service.

In retrospect, I'm not sure the ORM was even that useful. Besides encouraging certain bad habits on the consumer side (eg, most instances of lazy loading), its one more level of indirection to grapple with. Why not drop down to the database and write your implementation there? It can be tested right there and then, and directly in terms of the data flow: input -> output.

Re: Use What Works: Prefixing Database Tables With 'tbl'

#39

Post by pavedwalden »

borlak wrote: post #28

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 wa…

That 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".
Post reply on HN