Table: dbo.Scheduled_Transaction_Instance
This table stores the information we need to track transactions scheduled to be created from a template. It stores information for transactions already created and for transactions not created yet.
Columns
Name | Type | Constraints | Description
scheduled_transaction_instance_KEY | int (4) | NOT NULL | The primary key for this table.
scheduled_transaction_interval_KEY | int (4) | NOT NULL | The key of the interval in which we will (or did) create the transaction.
is_generated | bit (1) | NOT NULL | A flag indicating whether or not we have created the scheduled transaction. Some transactions generated from a scheduled template can be physically deleted. Without regard to whether or not a transaction generated from a scheduled template still exists, we need to know that we did in fact create it.
unadjusted_transaction_date | smalldatetime (4) | NOT NULL | The calculated transaction date, not adjusted for the must-be-on-a-weekday calculation option, or for the other business day options the analysts would like us to implement later. When a user changes scheduling frequency options, we use this date to map a generated transaction to its new interval.
transaction_date | smalldatetime (4) | NOT NULL | If we have created the scheduled transaction, then this is the date we used on it. If we have not created the transaction yet, then this is the date the transaction generation code will assign to the transaction when it does create it. We store this date for two reasons. First, a user can edit the date on a transaction generated from a template. Second, if we have not created the scheduled transaction yet, storing the transaction date when we create the schedule will spare the transaction generation code the trouble of using the numerous scheduling options to determine what it should be.
creation_date | smalldatetime (4) | NOT NULL | If we have created the scheduled transaction, then this is the date we on which we created it. If we have not created the transaction yet, then this is the date when the transaction generation code should create it. When using the option create-N-days-in-advance, and N > 0, this field will be N days before the transaction date. We store this date to enable the transaction generation code to quickly determine which transactions are due to be created.

Primary key
Name | Columns | Description
PK_Scheduled_Transaction_Instance | scheduled_transaction_instance_KEY |

Foreign keys
Name | Columns | Referenced table | Description
FK_Scheduled_Transaction_Instance__Scheduled_Transaction_Interval__scheduled_transaction_interval_KEY | scheduled_transaction_interval_KEY | Scheduled_Transaction_Interval (scheduled_transaction_interval_KEY) |