The physical data model should represent exactly the way the tables and columns are designed in the in the database management system. I recommend keeping storage, partitioning, indexing and other physical characteristics in the data model if at all possible. This will make upkeep and comparison with the development, test or production database much much easier.
Some characteristics of the physical data model may include:
- The physical data model is as close to 3rd normal form as possible – base this on usage and performance.
- Table and column names are meaningful to our business users, and easy to import into our front-end tools (reporting tools).
- Any abbreviations used in table or column names conform to corporate naming standards (if you don’t have this – create it)
- Depending on the database foreign key indexes may or may not be used. Base this on an indexing strategy that should be created for this conversion. Tuning is an art, find someone good who understands the documentation involved to maintain this application over time.
- Constraints, indexes and any keys are named according to corporate naming standards.
- Timestamps and date columns are used correctly, based on corporate standards.
- Audit columns (insert date, update date, delete date, etc.) are added to the physical table as required by the application.
If the conversion is from one software application to another without extensions, just documentation may be required to complete this project. How much required data modeling should be determined early in the project, as it can be time consuming.
