Database, Table and Column Naming Conventions? [cl

Question

Whenever I design a database, I always wonder if there is a best way of naming an item in my database. Quite often I ask myself the following questions:

1. Should table names be plural?
2. Should column names be singular?
3. Should I prefix tables or columns?
4. Should I use any case in naming items?

Are there any recommended guidelines out there for naming items in a database?

Answer 1

I hear the argument all the time that whether or not a table is pluralized is all a matter of personal taste and there is no best practice. I don't believe that is true, especially as a programmer as opposed to a DBA. As far as I am aware, there are no legitimate reasons to pluralize a table name other than "It just makes sense to me because it's a collection of objects," while there are legitimate gains in code by having singular table names. For example:

1. It avoids bugs and mistakes caused by plural ambiguities. Programmers aren't exactly known for their spelling expertise, and pluralizing some words are confusing. For example, does the plural word end in 'es' or just 's'? Is it persons or people? When you work on a project with large teams, this can become an issue. For example, an instance where a team member uses the incorrect method to pluralize a table he creates. By the time I interact with this table, it is used all over in code I don't have access to or would take to long to fix. The result is I have to remember to spell the table wrong every time I use it. Something very similar to this happened to me. The easier you can make it for every member of the team to consistently and easily use the exact, correct table names without errors or having to look up table names all the time, the better. The singular version is much easier to handle in a team environment.

2. If you use the singular version of a table name AND prefix the primary key with the table name, you now have the advantage of easily determining a table name from a primary key or vice versa via code alone. You can be given a variable with a table name in it, concatenate "Id" to the end, and you now have the primary key of the table via code, without having to do an additional query. Or you can cut off "Id" from the end of a primary key to determine a table name via code. If you use "id" without a table name for the primary key, then you cannot via code determine the table name from the primary key. In addition, most people who pluralize table names and prefix PK columns with the table name use the singular version of the table name in the PK (for example statuses and statusId), making it impossible to do this at all.

3. If you make table names singular, you can have them match the class names they represent. Once again, this can simplify code and allow you to do really neat things, like instantiating a class by having nothing but the table name. It also just makes your code more consistent, which leads to...

4. If you make the table name singular, it makes your naming scheme consistent, organized, and easy to maintain in every location. You know that in every instance in your code, whether it's in a column name, as a class name, or as the table name, it's the same exact name. This allows you to do global searches to see everywhere that data is used. When you pluralize a table name, there will be cases where you will use the singular version of that table name (the class it turns into, in the primary key). It just makes sense to not have some instances where your data is referred to as plural and some instances singular.

To sum it up, if you pluralize your table names you are losing all sorts of advantages in making your code smarter and easier to handle. There may even be cases where you have to have lookup tables/arrays to convert your table names to object or local code names you could have avoided. Singular table names, though perhaps feeling a little weird at first, offer significant advantages over pluralized names and I believe are best practice.

Answer 2

--Example SQL

CREATE TABLE D001_Students
(
    StudentID INTEGER CONSTRAINT nnD001_STID NOT NULL,
    ChristianName NVARCHAR(255) CONSTRAINT nnD001_CHNA NOT NULL,
    Surname NVARCHAR(255) CONSTRAINT nnD001_SURN NOT NULL,
    CONSTRAINT pkD001 PRIMARY KEY(StudentID)
);

CREATE INDEX idxD001_STID on D001_Students;

CREATE TABLE D002_Classes
(
    ClassID INTEGER CONSTRAINT nnD002_CLID NOT NULL,
    StudentID INTEGER CONSTRAINT nnD002_STID NOT NULL,
    ClassName NVARCHAR(255) CONSTRAINT nnD002_CLNA NOT NULL,
    CONSTRAINT pkD001 PRIMARY KEY(ClassID, StudentID),
    CONSTRAINT fkD001_STID FOREIGN KEY(StudentID) 
        REFERENCES D001_Students(StudentID)
);

CREATE INDEX idxD002_CLID on D002_Classes;

CREATE VIEW V001_StudentClasses
(
    SELECT
        D001.ChristianName,
        D001.Surname,
        D002.ClassName
    FROM
        D001_Students D001
            INNER JOIN
        D002_Classes D002
            ON
        D001.StudentID = D002.StudentID
);

These are the conventions I was taught, but you should adapt to whatever you developement hose uses.

1. Plural. It is a collection of entities.
2. Yes. The attribute is a representation of singular property of an entity.
3. Yes, prefix table name allows easily trackable naming of all constraints indexes and table aliases.
4. Pascal Case for table and column names, prefix + ALL caps for indexes and constraints.

Answer 3

Table Name: It should be singular, as it is a singular entity representing a real world object and not objects, which is singlular.

Column Name: It should be singular only then it conveys that it will hold an atomic value and will confirm to the normalization theory. If however, there are n number of same type of properties, then they should be suffixed with 1, 2, ..., n, etc.

Prefixing Tables / Columns: It is a huge topic, will discuss later.

Casing: It should be Camel case

My friend, Patrick Karcher, I request you to please not write anything which may be offensive to somebody, as you wrote, "•Further, foreign keys must be named consistently in different tables. It should be legal to beat up someone who does not do this.". I have never done this mistake my friend Patrick, but I am writing generally. What if they together plan to beat you for this? :)

Answer 4

Take a look at ISO 11179-5: Naming and identification principles You can get it here: http://metadata-standards.org/11179/#11179-5

I blogged about it a while back here: ISO-11179 Naming Conventions

Answer 5

I work in a database support team with three DBAs and our considered options are:

1. Any naming standard is better than no standard.
2. There is no "one true" standard, we all have our preferences
3. If there is standard already in place, use it. Don't create another standard or muddy the existing standards.

We use singular names for tables. Tables tend to be prefixed with the name of the system (or its acronym). This is useful if the system complex as you can change the prefix to group the tables together logically (ie. reg_customer, reg_booking and regadmin_limits).

For fields we'd expect field names to be include the prefix/acryonm of the table (i.e. cust_address1) and we also prefer the use of a standard set of suffixes ( _id for the PK, _cd for "code", _nm for "name", _nb for "number", _dt for "Date").

The name of the Foriegn key field should be the same as the Primary key field.

i.e.

SELECT cust_nm, cust_add1, booking_dt
FROM reg_customer
INNER JOIN reg_booking
ON reg_customer.cust_id = reg_booking.cust_id

When developing a new project, I'd recommend you write out all the preferred entity names, prefixes and acronyms and give this document to your developers. Then, when they decide to create a new table, they can refer to the document rather than "guess" what the table and fields should be called.

Answer 6

I think the best answer to each of those questions would be given by you and your team. It's far more important to have a naming convention then how exactly the naming convention is.

As there's no right answer to that, you should take some time (but not too much) and choose your own conventions and - here's the important part - stick to it.

Of course it's good to seek some information about standards on that, which is what you're asking, but don't get anxious or worried about the number of different answers you might get: choose the one that seems better for you.

Just in case, here are my answers:

1. Yes. A table is a group of records, teachers or actors, so... plural.
2. Yes.
3. I don't use them.
4. The database I use more often - Firebird - keeps everything in upper case, so it doesn't matter. Anyway, when I'm programming I write the names in a way that it's easier to read, like releaseYear.