Database Design Process

In general the database tables will be directly copied from OMNI using the process described below. The following changes will be made when copying the tables:

• Columns that are not being used will not be copied.

• In many cases there are two columns with the same meaning for instance “amount” and “value” – this will be changed to a single column.

• Columns that are computed fields will be stored in the table as computed fields for instance:

  • Value = Rate * Quantity – this will be stored as a computed field.

• Spelling mistakes in column names will be corrected.

• Tables that are not being used will not be copied.

• Tables where the relationship is ONE-TO-ONE will be merged.

• ONLY parent tables for core entities will contain inserted_on, inserted_by, updated_on and updated_by all other tables WILL NOT contain these columns. Inserted_on, updated_on will not be Foreign Keys to the User table – they will simply contain a reference. This will be done to reduce the linking to the DNN core tables.

• All DNN core tables are prefixed with 'dnn_'

• Columns that are unique according to the business logic – for instance customer code, job code etc. must have a UNIQUE Key constraint added to it.

• Primary Keys for each table should always be of INT and be a Identity. These primary keys must NOT be passed in the query string instead UID fields should be passed.

• Every Core Entity table should contain a UID field – every UID field should be named like:

  • Prospect_uid, job_uid etc.

  • Each UID field should be of data type unique identifier

  • It should be a NOT NULL, RowGuid and UNIQUE KEY

  • The default value of the field should be newid()