5.4.9 Recommendations When Using Quick SQL

Learn about recommended approaches to creating effective data models.

Although there are many valid approaches to creating an effective data model, Oracle recommends the following:

• Table Naming – Define your tables as plural and columns as singular. Since tables are designed to store many rows it makes sense to have them plural. For example Employees.

• Column Naming – Define your column names as singular. For example First Name.

• Primary Keys – Every table should have a primary key, primary keys are used to uniquely identify each row and can be used to associate child table data.

• Primary Key Column Naming – Every table will have a primary key and it should be called ID and it should be numeric.

• Table Constraints – The best way to ensure data integrity is to have the database only allow proper data. For example, if a column is required ensure you have a not null constraint. If a column is unique ensure you have a unique constraint.

• Referential Integrity – Most relational database models have parent child, also known as one too many relationships. They frequently also have many to many relationships. Using declarative primary key and foreign key syntax you can have the database enforce these relationships to ensure the integrity of your data. By defining the referential integrity you ensure all child tables do in fact relate to parent tables.