Technical Document¶

Title: CRUD Operations for Billing Scheme Management

---

Tables Involved¶

tblBillingScheme¶

• Id: INT (Primary Key, Identity)
• SchemeName: NVARCHAR (Not Null)
• CreatedBy: NVARCHAR (Not Null)
• CreatedOn: DATETIME (Not Null, Default GETDATE)
• LastModifiedBy: NVARCHAR (Nullable)
• LastModifiedOn: DATETIME (Nullable)

tblBillingSchemeDetail¶

• SchemeId: INT (Foreign Key → tblBillingScheme(Id))
• MCode: NVARCHAR (Not Null)
• Rate: DECIMAL (Not Null)
• BillingInterval: NVARCHAR (Not Null, Allowed: 'Month' or 'Annual')
• BillingMonth: NVARCHAR (Nullable, Required only if BillingInterval = 'Annual')

Primary Key: (SchemeId, MCode)

---

Business Rules¶

1. BillingInterval can only have values: 'Month' or 'Annual'.
2. BillingMonth must be NULL if BillingInterval is 'Month'.
3. BillingMonth must be a valid month name (January-December) if BillingInterval is 'Annual'.

---

Check Constraint¶

ALTER TABLE tblBillingSchemeDetail
ADD CONSTRAINT CK_BillingInterval_Values
CHECK (BillingInterval IN ('Month', 'Annual'));

ALTER TABLE tblBillingSchemeDetail
ADD CONSTRAINT CK_BillingMonth_If_Annual
CHECK (
    (BillingInterval = 'Annual' AND BillingMonth IS NOT NULL)
    OR
    (BillingInterval = 'Month' AND BillingMonth IS NULL)
);