Use What Works: Prefixing Database Tables With 'tbl' • Hacker News Board

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

#41

Post by pradocchia » Wed, Oct 10, 2012, 4:34 PM UTC

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…

I have a love-hate relationship w/ column prefixes like you describe. One on hand, they make searching the codebase much easier, and if you can search the codebase, you can refactor w/ confidence.

On the other hand, it bakes the schema into every single column reference, and that makes schema changes more costly--either you cruft up your database with now-misnamed columns, or you fix all references, or you avoid changes in the first place, and the business drifts further and further from the database model.

My compromise position at places that do use column prefixes has been, column prefixes on base tables, views and/or stored procedures for client access, and no prefixes exposed to the client.

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

#42

Post by adaml_623 » Wed, Oct 10, 2012, 4:51 PM UTC

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…

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.

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

#43

Post by mcguire » Wed, Oct 10, 2012, 4:51 PM UTC

I'd probably take this more seriously if the author legally changed his name to "PersonThomasLaRock".

...and ditched the "rockstar" bit.

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

#44

Post by pyre » Wed, Oct 10, 2012, 4:55 PM UTC

zzzeek wrote: post #37

usea wrote: post #27

Earlier quoted context omitted.

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

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

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

#45

Post by mgkimsal » Wed, Oct 10, 2012, 5:10 PM UTC

Perhaps I should start prefixing all my files with file_ as well, so when I'm looking at stuff on the file system, I'll know it's a file vs a symlink. I'll always know when I sort by filename to start looking under "f" if I want a file.

What's interesting about the "use what works" mantra invoked here is it's not saying who this should work for. A database is a resource that's typically shared between multiple parties, and enforcing one somewhat arbitrary rule for the convenience of one party to the detriment of another party doesn't sound very amicable.

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

#46

Post by kevinh » Wed, Oct 10, 2012, 5:45 PM UTC

DrJokepu wrote: post #2

adjHungarian nounNotation verbHurts detThe nounReadability prepOf possYour nounCode.

I'd largely agree with you with regard to Systems Hungarian Notation, but Apps Hungarian Notation has made my job much easier when talking about whether data is sanitized/unsanitized (or other similar distinctions that fall under the same type) in the scope of a larger program. In that case, it can prevent programmers from mistakenly interpreting the state of the variable, which is much harder to easily glean than the type.

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

#47

Post by adamzochowski » Thu, Oct 11, 2012, 2:45 AM UTC

mongol wrote: post #40

My reason for prefixing: makes it possible to use a table called Order (plus prefixing, naturally). Orders are a quite common thing to have in databases.

1) you can use plurals, so "select from orders"

2) typically there is no single order table. Most systems will have order_summary / order_details / order_items / order_packages . Think of handling a single order that has multiple products each of multiple quantities. And to complicate, fulfillment of product quantity requires multiple warehouses / shipments.

3) you can have a table called order, you just have to ensure to escape it properly, either double quotes or square brackets. Similarly table names can have spaces in them.