Database Table/Column rules

Question

We are making a new database design where we are setting some rules upfront. I dunno how much it makes sense to have these rules in place,

1. Each table and column name should be meaningful. It should give information about the table or the column's purpose.

2. Table names should not exceed 27 characters and column names should not exceed 30 characters.

3. Table names are plural. For example: COMPANIES, EMPLOYEES, PERSONS, GENDERS, RELIGIONS, COUNTRIES, ...

4. Each table has a primary key as table_name_ID which is NUMBER(15). table name is used as singular in the Primary Key column. Example: the USER_REQUESTS table has a primary key as USER_REQUEST_ID.

5. If the table or column name exceeds the limits, a suitable abbreviation is used. Example: CMS_EXEMPTIONS is the table to keep content management system exemptions.

6. Tables that keep transactional data should end with _TRANS. Example: COMPANY_TRANS is the table that keeps company related transaction data.

7. Each table has 4 WHO column as :

   CREATE_USER        NUMBER(15)
   CREATE_DATE        DATE
   MODIFY_USER        NUMBER(15)
   MODIFY_DATE        DATE
   

8. Columns which are a foreign key relation are named as the primary key of the referenced table. Example: if a table has a reference to the COUNTRIES table, the referencing column is also named as COUNTRY_ID as the primary key of the COUNTRIES table.

9. If the meaning of the referencing column should be other than the 8th rule because of the first rule (meaningful names), meaningful name rule is applied. Ex: Both nationality and country columns have a relation to COUNTRIES table if it is used to keep a person nationality than it is named as NATIONALITY_ID.

10. If the table has more than one related to the same table they are given different names like BIRTH_COUNTRY_ID, PASSPORT_ISSUE_COUNTRY_ID

11. If a table is a relation table between to tables, the table name contains both table names.

To give meaningful names were always our first rule in naming.

Answer 1

This is the stuff of holy wars. There is no right answer. Further these are not "rules" of the sort that can be (easily) enforced in a CI tool chain. They are a style guide. Enforcement will be by code review and consensus, so make sure that all (or enough) of those affected agree, can live with the decision, and can justify them to the next generation and the unbelievers who will inevitably call foul.

Ask yourself why you want these rules. For me it is to impose a consistency which makes code easy to read. Very soon the eye gets used to reading a style and comprehension improves. When every query and table follows a unique style one starts from scratch on every reading. Further, rules remove the cognitive load of deciding how objects should be named. That decision's been made; done; move on to the important stuff. Right or wrong you never have to spend another second on this time-consuming low-value activity. And finally, a good style can uncover and make clear semantics within the structure without cruft.

By these measures I think your suggestions are fine.

There are a few suggestions I'd make, though. Forcing every table to have a numeric surrogate key may be unnecessary. I've touched on this here.

Extend the conventions to queries. There can be a lot of variation in select lists (leading or trailing commas?), joins (single line or clause per line?), sub queries, indentation. The team needs to think about this once and once only. A consistent layout can help make clear errors like missing join conditions.

I've seen the WHO column convention used many times. It's almost useless. What special significance does the original creator have over the most recent updater? Why is it important to know who updated the row a second ago but not who updated it three seconds ago?

If we saw a PERSONS table with columns FIRST_MAILING_ADDRESS and MOST_RECENT_MAILING_ADDRESS we, as data modelers, would have conniptions. The same applies to these WHO tables. Normalise them into their own table. If it's important to know how specific columns' values change over time these columns should be in the normalised table, too. Generally this pattern is know as temporal tables.

Answer 2

1. Each table and column name should be meaningful.

Hell, yes!

2. Table names should not exceed 27 characters ...

Why 27?

Oracle [up to 11.2] supports 30, so what are you going to do with the other three?

Whatever that purpose is, it might be worth adding it into your "Standard".

... and column names should not exceed 30 characters.

Column names cannot exceed 30 characters ... at least until Oracle 12. As such, this qualification might be redundant.

However, if you need to support older versions (or even other DBMSs) then you may still need this.

3. Table names are plural.

OK.

4. Each table has a primary key ... which is NUMBER(15). table name is used as singular in the Primary Key column. Example: the USER_REQUESTS table has a primary key as USER_REQUEST_ID.

If there is a Natural Key for the table, compound or otherwise, I would consider using that first. If there is no Natural Key for the table, then OK, use a surrogate.

5. If the table or column name exceeds the limits, a suitable abbreviation is used.

I would strongly recommend that you document or, better still, automate the creation of acceptable abbreviations. Fail to do so and everybody will do it differently.

6. Tables that keep transactional data should end with _TRANS.

Potentially conflicts with #1.

Keep the table name meaningful, rather than wrapping it up in conventions and mummery. If necessary, document your tables in an external, Data Dictionary tool.

7. Each table has 4 WHO column

That's fine.

For the really important tables, where you need to retain more than just the last change, implement an Auditing system that records every change at the database level. Don't do it at the application level - that lets people sneak in under the covers and get around it.

8. Columns which are a foreign key relation are named as the primary key of the referenced table.

9. If the meaning of the referencing column should be other than the 8th rule because of the first rule (meaningful names), meaningful name rule is applied.

10. If the table has more than one field related to the same table they are given different names

These are sensible conventions for foreign keys and accept that there might well be cases where the simple case breaks down.

Good planning.

11. If a table is a relation table between to tables, the table name contains both table names.

And this is where it all falls down.

Table names are limited to 30 characters. Applying this rule means that your table names are, therefore, limited to only 15 characters each and there's no way those are going to be meaningful. Having used a commercial CRM package that does exactly this, I can testify that the resulting table names are simply dreadful!

Stick with meaningful names for everything. It works [almost] very time.