Category Archives: Database

Points to consider for creating new tables

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