Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

grantm (164)

grantm
  (email not shown publicly)
http://www.mclean.net.nz/

Just a simple [cpan.org] guy, hacking Perl for fun and profit since way back in the last millenium. You may find me hanging around in the monestary [perlmonks.org].

What am I working on right now? Probably the Sprog project [sourceforge.net].

GnuPG key Fingerprint:
6CA8 2022 5006 70E9 2D66
AE3F 1AF1 A20A 4CC0 0851

Journal of grantm (164)

Tuesday April 22, 2008
04:19 AM

Database Naming Conventions

[ #36214 ]

A $cow_orker recently sparked a debate about conventions for naming database objects. Obviously this is a bit of a religious issue for many and we certainly uncovered a variety of opinions. One very basic question which many feel strongly about is the pluralisation of table names. I have a preference for singular but am happy to run with plural if that's the convention in an existing project.

Early in my development career I saw a colleague ridiculed for creating a database table with a pluralised name. His justification was (quite reasonably) "I called it 'widgets' because I want to store multiple widget records in it". The DBA's response was "Of course you want to store multiple records in it. If you didn't have multiple records you'd hardly go to the bother of creating a table, would you?". From this logic it comes down to a simple choice: make every table name plural; or, don't bother. I've standardised on "Don't bother".

The thing I don't get is the vast number of people who subscribe to this inseparable pair of rules:

  • Database table names should always be plural
  • Object class names should always be singular

It seems obvious to me that if you agree with the first statement then using the same logic you should disagree with the second. Apparently other people don't see it the same way.

It seems to me that a 'widget' table defines the characteristics of a widget record and serves as a container for such records. Similarly a 'Widget' class describes the characteristics of a widget object and serves as a template for such objects. I just don't get why so many people see these two issues in black and white as obvious opposites.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • I don't get the connection between table names and class names. I don't care whether a table uses a singular or a plural name, but I don't follow that if you use plural table names, you should use plural class names as well. A table is a collection - a class is a type. I think one should apply the same rules for naming tables as one should use naming arrays. If you use some reasoning that table names should always be plural (or singular), then the same reasoning should give you all plural (singular) array n
    • I don't get the connection between table names and class names.

      The english word class (in its generic non-CS use) derives from the same root as 'classification'. Dictionary definitions for class use words like 'a collection of things sharing a common attribute', 'A group of individuals ranked together as possessing common characteristics'. In this respect, a class is a collection in the same way that a table is.

      I agree 100% that class names should usually be singular. It seems obvious to me that the same logic behind that decision applies in exactly the same way

  • I'm also in favor of singularity, mainly because pluralization is not generally and uniformly possible:

    head vs. heads: simple
    foot vs. feet: not as simple

    Here, no generalized form is possible, thus it can't be done without explicit naming.
    That of course costs time as you need to think about it every time you encounter it, especially if your mother tongue isn't english.
  • In SQL, the minimum likely use case is:

    SELECT id, name, alignment FROM characters;
    With a class, the minimum likely use case is:

    object = Class.new;
    I don't think anyone who uses other than the pluralization that I use deserves a painful death, but I also don't see that the number of tables and classes needs to be tied.
    --
    rjbs
    • In SQL, the minimum likely use case is:

      SELECT id, name, alignment FROM characters;

      And each row you get back is made up of those attributes of a 'character'. Sure you get multiple rows, but I don't get how that's different from defining a class in order to instantiate multiple objects.

      With a class, the minimum likely use case is:

      object = Class.new;

      A common case in Rails is:

      @character_list = Character.find(:all)

      Which ultimately generates SQL along the lines of:

      SELECT * FROM CHARACTERS

      And the application will then instantiate one Character object for each row retrieved from the table. In this case there is a clear and deliberate one-to-

  • Plurals seem more natural to me, though I don't really care one way or the other. I do hate CamelCase names though, especially when it comes to ones like AccountPlanOptionLinkSeq...
  • I really hate plural table names. There are two common problems plural table names have. First, as janus mentioned, plurals in English can be irregular. A common real-world example is "people" and "person". If I have a table named "People" I'll probably end up referring to "person_id" as well, which just irritates me.

    Another problem is that it degrades badly when you have "linking" tables. Say I have Accounts and Users, then I might have an AccountsUsers table. Yuck! If I have Accounts, Users, and Roles, th
    • The proposed standard naming convention from $cow_orker also mandated the use of CamelCase. With the added wrinkle of a short suffix on every table name. E.g.: Account_act or ProductCommissionGroup_pcg. Any advance on Triple-Yuck ?

      • I just barfed on my keyboard...thanks.
      • I do like camel case for table names, as a way to distinguish them from column names. This is very similar to the the Perl standard of camel case for package names.

        That suffix thing is truly awful, though. What possible purpose could it serve? It's like naming everything twice.
        • The aforementioned proposed standard justified camel case for table names in the same way - to distinguish them from column names. I found that argument a bit spurious since a) I don't recall ever confusing column names for table names and b) the table names are easy to identify already - they're the words following the FROM and JOIN keywords :-)
        • It seems that most databases make table names case insensitive unless you go out of your way to use " everywhere. So using camelCase becomes an unenforced convention that is then not maintained. You also have the potential problem that a given name could be broken into English words in multiple ways, making capitalization ambiguous.

          This is why I prefer using _ instead.

          For the record, note that my job involves a lot of reporting. I spend more time writing SQL than Perl.
          • It seems that most databases make table names case insensitive

            Yeah, except for a particular free-of-charge database that seems to be quite popular. Tables are files on the underlaying filesystem, and if the underlaying filesystem is case sensitive, your tables are as well. Columns, OTOH, aren't case-sensitive.

            • Yeah, except for a particular free-of-charge database...

              ...and a certain well-known commercial database (*cough* Sybase *cough*). There is a global switch you (i.e. the DBA) can flip to change that, but then all havok breaks loose...