No DDL in Production Environment - II

Part I.

I said I was going to flesh that out a bit more. After some more thought about it, it could be a nice feature for DBAs.

I'll start with what needs to be stored.

DEPLOYMENTS

• DEPLOYMENTID - Surrogate key
• NAME - Name to give the deployment. Could be a ticket number or a change control number. Your choice.
• DESCRIPTION - What are you doing? Maintenance, the description from the change control ticket, etc.
• START_DATE - Defaults to SYSDATE. Used to gather the AFFECTED_OBJECTS at the end.
• STARTED_BY - DEFAULTS to USER
• END_DATE - Date the deployment ends.
• ENDED_BY - Will typically be the same as the STARTED_BY, but we'll capture it at the end just in case.

INVALID_OBJECTS and STAGE_CODES (not to be confused with stage coach).


STAGE_CODE

• STAGECODE - Nothing really special about this one here. Just a way to maintain data integrity without throwing it into a CHECK CONSTRAINT.

INVALID_OBJECTS

• INVALIDOBJECTID - Surrogate Key
• DEPLOYMENTID - Foreign Key from the DEPLOYMENTS table.
• STAGECODE -
• OWNER - Self explanatory
• OBJECT_NAME - Self explanatory

And finally, AFFECTED_OBJECTS:


AFFECTED_OBJECTS

• AFFECTEDOBJECTID - Surrogate key.
• DEPLOYMENTID - FK from DEPLOYMENTS
• OWNER - Self explanatory
• OBJECT_NAME - Self explanatory

And here's how it all fits together:


Any ideas on what else I can capture here?