Wednesday, February 18, 2009

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?

No comments: