Database Table/Column rules 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,


Each table and column name should be meaningful. It should give information about the table or the column's purpose.
Table names should not exceed 27 characters and column names should not exceed 30 characters.
Table names are plural. For example: COMPANIES, EMPLOYEES, PERSONS, GENDERS, RELIGIONS, COUNTRIES, ...
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.
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.
Tables that keep transactional data should end with _TRANS. Example: COMPANY_TRANS is the table that keeps company related transaction data.
Each table has 4 WHO column as :

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

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