PapayaSoft Phuket web development

@JeremyKendall recently asked a great question on Twitter:

Do you have a DB naming convention? camelCase? under_scores? Singular or plural table names? I can never settle on anything.

He got some great answers. Since I will certainly need to reference these opinions at some later date – and Twitter will not keep the history forever – I am recording them here.

@konrness
I find it’s easier to follow my PHP code’s naming convention, so: camelCase for table names and columns. Under_score for DBs. Oh yeah, I usually do plural table names.

@stuardo_str
namespaced_under_scored

@cancehgarcia
i once read that “best practice” is singular and underscores. but as long as you’re consistent in the db, you should be good.

@cryptographite
CamelCase (ucfirst) for tables, and pre_desc for columns. User.u_id, UserProperties.up_value… It’s not perfect but it works

@calevans
single table names, camleCase attributes and PK is always id

@maphpia
namespaced_underscore_name, singular, and ‘id’ as PK

@guice
for us; id is always tablename_id to avoid name conflicts between joins. We use underscore (oracle). Singular names.

@akrabat
Plural table names; lowercase, underscore separated field names; PK is always id.

@avalanche123
lowercase underscore plural table names, id is the PK, <singular table name>_id is the FK to table name

@h (which is an awesome Twitter handle)
Singular Table names, first char upper case. Allows you to create models that match exactly.

@tommygeorge
Depends on the project, but I hate camelcase, and if I’m doing models/objects, I generally do plurals for table names.

@brodrigu
plural under_scores ftw!

I actually chimed in with my own support for @avalanche123′s suggestion: lowercase underscore plural table names, id is the PK, <singular table name>_id is the FK to table name. Semantically, I find this compelling since you end up with SQL queries of like “SELECT * FROM users” which reads better to my eye than “SELECT * FROM user”.

The only downside to this is that dealing with irregular plurals can sometimes be a pain. Consider a table containing properties. As noted above, I prefer the semantics that follow from calling that table “properties”. But a convention for a FK into that table “_id” would yield a field named “property_id”. The disconnect between the singular and plural can sometimes be problematic. Sure, there are Inflector classes (like this one) that know enough about English pluralization rules and their exceptions to do the mapping, but it’s one more hassle. So there are times when I bite the bullet, abandon the superior semantics, and simply hold my nose and go with singular table names.

What are your preferences and rationale?

Cheers!

Bumped into an interesting feature today. Well, whether it’s a feature or a gotcha appears to be in the eye of the beholder. And, I’ll tell ya, for this particular beholder, it was a gotcha.

I’ve got some code that builds a SQL query from a variable set of parameters. Most parameters are involved with building a dynamic WHERE clause. Other parameters are for pagination, setting a LIMIT clause on the query that eventually gets built. Another parameter is whether to make the query a COUNT() query or whether to actually pull rows from the db.

In the case where I build a COUNT() query, I was carelessly allowing a zero-based LIMIT clause – something like LIMIT 0,10 – to be appended to the query. No sweat, I figured, since I really only need the single-row containing the count I am requesting.

However, it turns out that this LIMIT was actually be applied *before* the COUNT()-ing, not after. So, my COUNT() was completely wrong, often returning 0.

There’s an hour of my life – spent in furious frustration – that I’ll never get back.

Onward!

I recently upgraded my local database server from MySQL 4.1 to MySQL 5.1 on Windows XP. Got stuck in a jam involving root passwords and MySQL error 1045 that took hours to chase and fix. As a way for me to remember, and in the hope that it helps someone, else in a similar fix, I want to detail what happened and the silver bullet that did the job.

Read the rest of this entry »