- Comments ( Always add comments to columns and tables)
- I always find comments incredibly helpful, they could answer some of the business / content questions. You can add table and column comments.
- Add comments at the table level at the very least to indicate the contents of the table and the relationship between the two (other than simply knowing parent-> child by way of the Foreign Key).
- Column comments are useful, as long as they do not simply restate the column name
- There are undoubtedly some terms for which there is a Glossary of Terms etc. in which case repeating the definition given there is also of little value
- However if the contents may not be immediately obvious / there is no pre-defined term it is definitely useful.
2. Column naming convention
- While I like the underscores (they are natural for DB developers)
- As a general translation from camel case java attribute names, we have generally ended up without any underscores.
- Maintain consistency with your database tables.
3. NOT NULL Columns
- You have only defined the primary key columns as not null (because you have to)
- There are more columns that cannot be null(i.e The foreign key column for example)
- If a column cannot be null, we should have a not null constraint on the column. This will prevent an application error from going undetected if a null value is inadvertently inserted.
4. TIMESTAMP/DATE columns
- Timestamps are ‘interesting’ beasts in oracle, when you consider timezone and some arithmetic functions and implicit type conversions that take place
- When to use Date and when to use Timestamp?
- If you have milliseconds, then clearly you have to use timestamps
- If they are simply to the second precision (or worse a given day), then using a DATE column makes more sense
5. Surrogate Primary Keys and Compound Keys / Unique Constraints
- We seem to have a general pattern of using surrogate Primary Keys everywhere
- Sometimes there is a perfectly natural primary key, for example if you have a table defining enumerations, you might as well use the enumeration value as the primary key, there is no need to define a separate PKEY column as well
- The tables as they stand with the surrogate PKEY give no indication as to which columns should be unique.
- i.e. could we have multiple records for the same PKEY+SOME_COLOUMN
6 . Performance Considerations
- Indexes on Foreign Key Columns
- You are undoubtedly going to wish to query the child records for a given parent record. You are not allowing for this, so querying child table for a given parent table record will require a full table scan.
7. Caching on Sequences
- Create sequences to generate values for the PKEYs
- Is there is no order dependency on the PKEY column values then should allow caching of the sequence values.
- This improves the efficiency significantly, but at the cost of potentially having gaps in the sequence (which you could always have if a transaction rolls back) and ‘out of order’ inserts in the case of databases with multiple (RAC) instances.
8 . Partitioning Considerations
- Expected Data Volumes
- Is the data write time critical?
- Do we expect to read the data far more than we write the data?
- Do we have to retain the data indefinitely?
- Do we expect to read ‘ALL current’ records on a frequent basis?
- can we sacrifice some performance when writing the data in order to optimise the data read
80 total views, no views today