Conceptual data model for a billing system

Question

I am working on an existing MySQL database. My task is to create a new data model for a billing system on that existing application.

I have created a conceptual data model (click to enlarge):

##Requirements

1. Multiple Payment types (such as Recurring, One-time, Instalment).

2. Multiple Payment modes (cash, cheque, internet banking).

3. Contract between Vendors (Company) and our company (Marketing executive)

4. Vendor can add amounts to their accounts by recharging from their page (similar to prepaid recharge).

5. Different packages are used, depending on the packages. Vendor total balance will be divided by the value of (package value / 365).

6. If balance becomes 0, they will not use their service, such as the following:

   set  t1 = package_value / 365  
   if        
   total_balance / t1 > 0
   set
           service = active  
   else
   set
           service = inactive
   

##SQL Code

use database_name;
/*==============================================================*/
/* table: company_account                                       */
/*==============================================================*/
create table company_account
(
   account_id           int not null auto_increment,
   company_id           int not null,
   date_created         date not null default now(),
   date_replaced        date not null default '9999-12-31',
   created_by           int not null,
   is_active            bit(1) default 1,
   primary key (account_id)
);

/*==============================================================*/
/* table: contract                                              */
/*==============================================================*/

create table contract
(
   contract_id          int not null auto_increment,
   account_id           int not null,
   contract_number      int(6) not null default '0',
   contract_date        date not null default '9999-12-31',
   initial_payment      float(5,2) not null default '0.00',
   date_created         date not null default now(),
   date_effective       date not null default '9999-12-31',
   date_replaced        date not null default '9999-12-31',
   created_by           int not null,
   is_active            bit(1) default 1,
   primary key (contract_id)
);


/*==============================================================*/
/* table: packages                                              */
/*==============================================================*/
create table packages
(
   package_id           int not null,
   package_name         varchar(30) not null default "No Packages Selected",
   package_value        float(5,2) not null default '0',
   date_created         date not null default now(),
   date_replaced        date not null default '9999-12-31',
   created_by           int not null,
   is_active            bit(1) not null default 1,
   primary key (package_id)
);


/*==============================================================*/
/* table: payment_mode                                          */
/*==============================================================*/
create table payment_mode
(
   payment_mode_id      int not null,
   payment_mode         varchar(30) not null default "No Mode Selected",
   description          varchar(100) not null default "No Description Available",
   date_created         date not null default now(),
   date_replaced        date not null default '9999-12-31',
   created_by           int not null,
   is_active            bit(1) not null default 1,
   primary key (payment_mode_id)
);


/*==============================================================*/
/* table: payment_status                                        */
/*==============================================================*/
create table payment_status
(
   payment_status_id    int not null,
   payment_status       varchar(30) not null default "No Status Selected",
   description          varchar(100) not null default "No Description Available",
   date_created         date not null default now(),
   date_replaced        date not null default '9999-12-31',
   created_by           int not null,
   is_active            bit(1) not null default 1,
   primary key (payment_status_id)
);



/*==============================================================*/
/* table: payment_type                                          */
/*==============================================================*/
create table payment_type
(
   payment_type_id      int not null auto_increment,
   payment_type         varchar(30) not null default "No Type Selected",
   decription           varchar(100) not null default " No Description Available",
   date_created         date not null default now(),
   date_replaced        date not null default '9999-12-31',
   created_by           int not null,
   is_active            bit(1) not null default 1,
   primary key (payment_type_id)
);



/*==============================================================*/
/* table: payments                                              */
/*==============================================================*/

create table payments
(
   payment_id           int not null auto_increment,
   payment_type_id      int not null,
   payment_mode_id      int not null,
   payment_status_id    int not null,
   package_id           int not null,
   contract_id          int not null,
   payment_date_time    timestamp not null default current_timestamp,
   payment_amount       float(5,2) not null default '0.00',
   date_created         date not null default now(),
   date_replaced        date not null default '9999-12-31',
   created_by           int not null,
   is_active            bit(1) not null default 1,
   primary key (payment_id)
);



/*==============================================================*/
/* table: transaction                                           */
/*==============================================================*/
create table transactions
(
   transaction_id       int not null,
   payment_id           int not null,
   transaction_number   char(15) not null default '000000000000000',
   date_created         date not null default now(),
   date_replaced        date not null default '9999-12-31',
   created_by           int not null,
   is_active            bit(1) not null default 1,
   primary key (transaction_id, payment_id)
);




/*==============================================================*/
/* table: vendor_invoice                                        */
/*==============================================================*/
create table vendor_invoice
(
   invoice_id           int not null auto_increment,
   transaction_id       int not null,
   payment_id           int not null,
   invoice_date_time    timestamp not null default current_timestamp,
   date_created         date not null default now(),
   date_replaced        date not null default '9999-12-31',
   created_by           int not null,
   is_active            bit(1) not null default 1,
   primary key (invoice_id, transaction_id, payment_id)
);



/*==============================================================*/
/* table: vendor_basket                                         */
/*==============================================================*/
create table vendor_basket
(
   v_basket_id          int not null,
   account_id           int not null,
   date_created         date not null default now(),
   date_replaced        date not null default '9999-12-31',
   created_by           int not null,
   is_active            bit(1) not null default 1,
   primary key (v_basket_id, account_id)
);



/*==============================================================*/
/* table: payment_basket                                        */
/*==============================================================*/
create table payment_basket
(
   basket_id            int not null,
   v_basket_id          int not null,
   account_id           int not null,
   last_credited_value  float(5,2) not null default '0',
   last_credited_date   date not null default '9999-12-31',
   amount_utilized      float(5,2) not null default '0.00',
   total_balance        float(5,2) not null default '0.00',
   days_remaining       smallint not null default '0',
   date_created         date not null default now(),
   date_replaced        date not null default '9999-12-31',
   created_by           int not null,
   is_active            bit(1) not null default 1,
   primary key (basket_id)
);


alter table company_account 
add constraint fk_company_account 
foreign key (company_id)
references company (company_id) 
on delete restrict 
on update restrict;

alter table contract 
add constraint fk_company_contract 
foreign key (account_id)
references company_account (account_id) 
on delete restrict 
on update restrict;

alter table payment 
add constraint fk_contract_payment 
foreign key (contract_id)
references contract (contract_id) 
on delete restrict 
on update restrict;

alter table payment 
add constraint fk_payment_mode 
foreign key (payment_mode_id)
references payment_mode (payment_mode_id) 
on delete restrict 
on update restrict;

alter table payment 
add constraint fk_payment_packages 
foreign key (package_id)
references packages (package_id) 
on delete restrict 
on update restrict;

alter table payment 
add constraint fk_payment_status 
foreign key (payment_status_id)
references payment_status (payment_status_id) 
on delete restrict 
on update restrict;

alter table payment 
add constraint fk_payment_type 
foreign key (payment_type_id)
references payment_type (payment_type_id) 
on delete restrict 
on update restrict;

alter table payment_basket 
add constraint fk_company_paymaent_basket 
foreign key (v_basket_id, account_id)
references vendor_basket (v_basket_id, account_id) 
on delete restrict 
on update restrict;

alter table transactions 
add constraint fk_payment_transaction 
foreign key (payment_id)
references payment (payment_id) 
on delete restrict 
on update restrict;

alter table user 
add constraint fk_user_company 
foreign key (company_id)
references company (company_id) 
on delete restrict 
on update restrict;

alter table vendor_basket 
add constraint fk_company_basket 
foreign key (account_id)
references company_account (account_id) 
on delete restrict 
on update restrict;

alter table vendor_invoice 
add constraint fk_transaction_invoice 
foreign key (transaction_id, payment_id)
references transactions (transaction_id, payment_id) 
on delete restrict 
on update restrict;

Note: I have skipped some tables already present in the existing database.

##Questions

1. How to perform calculations on the records without using external programming languages?
2. How to set the logic in stored procedures, while inserting and updating the payment_basket table?
3. Does my conceptual data model satisfy my requirements?
4. What are things required for me (DBA - beginner) to implement these kinds of tasks in the future?

I am a beginner, so some references will be useful.

Answer 1

I'm probably stating the obvious but you are missing a lot of foreign keys from the Payment table (ContractID, PaymentTypeID, PaymentModeID, PaymentStatusID, PackageID) and avoid putting the actual text/values in the Payment table which slows the response of the database.