Bryan’s Oracle DB Design Style Standard

1. No underscores or any other non alpha numeric characters in database object names.
2. Use upper CamelCase for all database object names in SQL scripts
   
   NOTE: Under the covers oracle on-the-fly converts all object names in SQL statements (tables, cols, etc) into all uppercase format. Object names are stored in all uppercase in the DB engine by default too, so this behavior is usually a good thing. Avoid quoting your table/column names in SQL scripts - that forces oracle into case sensitive name matching, which usually fails unless you use all uppercase object names in your scripts.
   
3. Always use numeric sequence triggered id's (an oracle sequence controlled by a trigger) for primary key values to ensure uniqueness.
4. Use simple, short and hopefully readable table names as well as column names. The shorter you can make the names, the better. When creating names for complex constraints, having short column/table names will help greatly.
5. Do not repeat the table name within a column name. (a good generic primary key column name to stick to for all your tables would be something like "ID")
6. The name of a column containing a foreign key value should include the name of the table, appended by the key name. For example, the foreign key column that points to "TableA.ID" would be named TableAID.
7. Comment all tables and columns. Force yourself to add comments that will be meaningful to those who will maintain the database/application after you have moved on to another project.
8. Use constraints. At a minimum - Primary key constraints are required (and should be easy to identify using the naming convention above) for primary key columns in all tables as well as foreign key constraints for any foreign key columns in all tables. Mistakes will happen either via code or by manual manipulation of the data in your tables, use constraints to ensure primary keys are not duplicated (primary key constraints) and that child records do not get orphaned by accidental updates/deletes (foreign key constraints).
9. Even if you use a tool to create initial database objects on the fly, be sure to generate readable SQL script that you can archive to a version control system when youre ready to go "live".

--- slap SQL script example here ---

CREATE TABLE Car (
    Id              INT NOT NULL,
    Name            VARCHAR2(100) NULL,
    Make            VARCHAR2(100) NULL,
    Model           VARCHAR2(100) NULL
);

--- Primary Key Constraint
CREATE INDEX CarIdIdx ON Car(Id);
ALTER TABLE Car ADD CONSTRAINT CarIdPk PRIMARY KEY (Id);

COMMENT ON TABLE Car IS 'A list of cars.';
COMMENT ON COLUMN Car.Id IS 'Primary Key';
COMMENT ON COLUMN Car.Name IS 'A common name for this car.';
COMMENT ON COLUMN Car.Make IS 'The make of this car.';
COMMENT ON COLUMN Car.Model IS 'The model of this car.';

--- A list of car upgrades, if any.
--- this is a 1 (Car) to many (CarUpgrade) relationship
CREATE TABLE CarUpgrade (
    Id              INT NOT NULL,
    CarId           INT NOT NULL,
    Name            VARCHAR2(100) NULL,
    Description     CLOB NULL
);

--- Primary Key Constraint
CREATE INDEX CarUpgradeIdIdx ON CarUpgrade(Id);
ALTER TABLE CarUpgrade ADD CONSTRAINT CarUpgradeIdPk PRIMARY KEY (Id);
--- Foreign Key Constraint
CREATE INDEX CarUpgradeCarIdIdx ON CarUpgrade(CarId);
ALTER TABLE CarUpgrade ADD CONSTRAINT CarUpgradeCarIdFk FOREIGN KEY (CarId) REFERENCES Car(Id);

COMMENT ON TABLE CarUpgrade IS 'A list of car upgrades assigned to a a car.';
COMMENT ON COLUMN CarUpgrade.Id IS 'Primary Key';
COMMENT ON COLUMN CarUpgrade.CarId IS 'Foreign key to the car this upgrade is assigned to.';
COMMENT ON COLUMN CarUpgrade.Name IS 'The common name of this upgrade.';
COMMENT ON COLUMN CarUpgrade.Description IS 'A long description of this upgrade.';