Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am working on 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, as given below,Conceptual Data Model of a billing system My requirements are given below,

  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 recharge from their page (similar to prepaid recharge).
  5. Different packages are used, depends 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 follows,

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

SQL Code is given below,

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 since it is already present in the existing database,

My questions are,

  1. How to do the calculation 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. Whether is my conceptual data model will satisfy my requirements?
  4. What are things required for me (DBA - beginner) to implement such kind of tasks in future?

I am beginner some reference will be useful. Thanks in advance.

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.