System Design

Dynamic Rates Management Module Research

Posted by thisara on
Rates maintenance is a common requirement in today’s business applications. This solution allows systems to maintain rates in more dynamic approach where the administrative or the operational user could define rate formulas.
The solution is based on rate bases where a rate base could be any artifact that to be considered when defining a rate for a service or good. And these rate bases could be combined together in order to create dynamic rates. And at the time of combining rate bases additional information such as measuring unit, operating currency, taxes etc. could be defined. And the rate bases could be created, deleted and updated any time where the values are not hard-coded in table formats within the data store.
The approach could be elaborated using following example. Rate bases could consider as building blocks in a Lego system and the templates are built using the building blocks. And the template in the below example is a blue print of a house built out of selected building blocks.
Blank Flowchart - New Page(4)
Further each building block has its own attributes such as color, line thickness, dimensional view (2d, 3d). A rate base with specific attributes could be identify as a real artifact from the universe.
Blank Flowchart - New Page(2)
And the rate template in the above example is a blue print, not a real world artifact. Same example could be extended to below in order to express the real artifacts.
Blank Flowchart - New Page(5)
The implementation if this research could be elaborate in deep using the following example where it express the application of this solution for a vehicle parking system. Rate Bases within the vehicle parking system could be Parking Location, Vehicle Type, Parking Day etc. And each Rate Base will have its own attributes as below;
Blank Flowchart - New Page(7)
Once the rate bases are combined in to templates, they could be used to record operational information against the actual attribute combinations of the template as below;
Blank Flowchart - New Page(8)
As per this operational information could be recorded as in below example where the billing operation could be conducted at any given time including consumed services.
Blank Flowchart - New Page(9)
When there are multiple Rate Templates are being used, there will be more templates with different number of Rate Bases. Since the values are not hard-coded in to a specific data structure as table, modifications of the rate combinations or introduction of new rate combinations will not require any structural changes in the system.
Information of the Rate Templates could be used to generate customer specific quotations and this solution could be further extended such as to incorporate advance tax configurations.
The database design of the proposed system could be presented in a form of Entity Relationship Diagram as below;
rate
Further the role of each entity within the above design could be elaborated along with the entity and the functional descriptions.
  • Customer Group
Grouping of the customers are done in this table where the data could be usable in the event of generating reports, querying, bulk invoice generation etc.
CUSTOMER_GROUP
Field name data type length key is null default
cg_id int – pk false –
cg_name varchar 512 unique false –
cg_is_on_workflow boolean – – false false
cg_close_by_workflow boolean – – false false
cg_description varchar 2048 – true –
cg_date_created date – – true current date
cg_time_created time – – true current time
cg_date_modified date – – true current date
cg_time_modified time – – true current time
cg_created_by varchar 256 – true current user
cg_modified_by varchar 256 – true current user
cg_status int – – true 1

Functions:- Basic CRUD

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Customer

Maintain details of each customer is maintained over this entity.

CUSTOMER

data type length key is null default
c_id int – pk false –
c_reg_number varchar 512 unique false –
c_business_type varchar 1024 – true –
c_business_reg_number varchar 64 unique true –
c_business_reg_copy_revised boolean – – true –
c_is_credit_customer boolean – – true –
c_is_credit_application_revised boolean – – true –
c_company_name varchar 512 unique false –
c_company_address varchar 1024 – true –
c_invoice_delivery_name varvhar 512 – false –
c_invoice_delivery_address varchar 1024 – false –
c_Quotation_delivery_name varchar 512 – true –
c_quotation_delivery_address varchar 1024 – true –
c_contact_number_1 varchar 16 – true –
c_contact_number_2 varchar 16 – true –
c_fax_number_1 varchar 16 – true –
c_fax_number_2 varchar 16 – true –
c_email_address varchar 512 – true –
c_web_site varchar 512 – true –
c_vat_reg_type varchar 4 – false VAT
c_vat_svat_number varchar 32 – true –
c_contact_person varchar 512 – true –
c_is_on_workflow boolean – – false false
c_close_by_workflow boolean – – false false
c_description varchar 2048 – true –
c_date_created date – – true current date
c_time_created time – – true current time
c_date_modified date – – true current date
c_time_modified time – – true current time
c_created_by varchar 256 – true current user
c_modified_by varchar 256 – true current user
c_status int – – true 1
cg_id int – fk false –

Functions:- Basic CRUD

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Currency Rate

Maintenance of the periodic updates of currency rates are maintained over this entity.

CURRENCY_RATE

field name data type length key is null default
cr_id int – pk false –
cr_currency_name varchar 512 unique / ck false –
cr_currency_abbreviation varchar 8 false –
cr_currency_rate double – false –
cr_currency_rate_date date – false –
cr_is_on_workflow boolean – – false false
cr_close_by_workflow boolean – – false false
cr_description varchar 2048 – true –
cr_date_created date – – true current date
cr_time_created time – – true current time
cr_date_modified date – – true current date
cr_time_modified time – – true current time
cr_created_by varchar 256 – true current user
cr_modified_by varchar 256 – true current user
cr_status                               int – – true 1

Functions:- Basic CRUD

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Unit Of Measure

Measuring units of the services are being maintained within this entity. E.g.:- Kg, cm, Each, Feet;

UNIT_OF_MEASURE

field name data type length key is null default
uom_id int – pk false –
uom_uom_title varchar 512 unique false –
uom_abbreviation varchar 512 unique false –
uom_is_on_workflow boolean – – false false
uom_close_by_workflow boolean – – false false
uom _description varchar 2048 – true –
uom _date_created date – – true current date
uom _time_created time – – true current time
uom _date_modified date – – true current date
uom _time_modified time – – true current time
uom _created_by varchar 256 – true current user
uom _modified_by varchar 256 – true current user
uom _status int – – true 1

Functions:- Basic CRUD

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Rate Base

Rate bases where the rates are to be defined for services or goods are maintained within this entity E.g.:- Day;

RATE_BASE

field name data type length key is null default
rb_id int – pk false –
rb_rate_base_name varchar 512 unique false –
rb_rate_base_abbr_name varchar 16 unique false –
rb_is_on_workflow boolean – – false false
rb_close_by_workflow boolean – – false false
rb _description varchar 2048 – true –
rb _date_created date – – true current date
rb _time_created time – – true current time
rb _date_modified date – – true current date
rb _time_modified time – – true current time
rb _created_by varchar 256 – true current user
rb _modified_by varchar 256 – true current user
rb _status int – – true 1

Functions:- Basic CRUD

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Rate Base Value

Values of each rate base are maintained within this entity E.g.:- Sunday, Monday, Holiday;

RATE_BASE_VALUE

field name data type length key is null default
rbv_id int – pk false –
rbv_rate_base_value varchar 512 ck / unique false –
rb_id int –
rbv_is_on_workflow boolean – – false false
rbv_close_by_workflow boolean – – false false
rbv_description varchar 2048 – true –
rbv _date_created date – – true current date
rbv_time_created time – – true current time
rbv_date_modified date – – true current date
rbv _time_modified time – – true current time
rbv_created_by varchar 256 – true current user
rbv_modified_by varchar 256 – true current user
rbv_status int – – true 1

Functions:- Basic CRUD

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Rate

This is the central entity which acts as the central hub between Rate Base, Rate Base Values and Rate – Rate Base mappings.

RATE

field name data type length key is null default
r_id int – pk false –
r_abbreviation varchar 512 unique false –
r_rate_title varchar 512 – false –
r_is_on_workflow boolean – – false false
r_close_by_workflow boolean – – false false
r _description varchar 2048 – true –
r _date_created date – – true current date
r _time_created time – – true current time
r _date_modified date – – true current date
r _time_modified time – – true current time
r _created_by varchar 256 – true current user
r _modified_by varchar 256 – true current user
r _status int – – true 1

Functions:- This will be a background process where the creation will commit upon mapping of Rate Bases and Values in to Templates.

Algorithms:- N/A

Security:- Logged user security scope

Validation:- N/A

  • Rate Rate Base

This will be maintaining mapping between Rate and Rate Bases.

RATE__RATE_BASE

field name data type length key is null default
rrb_id int – pk false –
r_id int – ck / unique false –
rb_id int –
rrb_is_on_workflow boolean – – false false
rrb_close_by_workflow boolean – – false false
rrb _description varchar 2048 – true –
rrb _date_created date – – true current date
rrb _time_created time – – true current time
rrb _date_modified date – – true current date
rrb _time_modified time – – true current time
rrb _created_by varchar 256 – true current user
rrb _modified_by varchar 256 – true current user
rrb _status int – – true 1

Functions:- This will be a background process where the creation will commit upon mapping of Rate Bases and Values in to Templates.

Algorithms:- N/A

Security:- Logged user security scope

Validation:- N/A

  • Order Type

Different order types could be maintained for the purpose of grouping and it will be convenient at the time of generating bills.

ORDER_TYPE

field name data type length key is null default
ot_id int – pk false –
ot_order_type_name varchar 512 unique false –
ot_is_on_workflow boolean – – false false
ot_close_by_workflow boolean – – false false
ot_description varchar 2048 – true –
ot_date_created date – – true current date
ot_time_created time – – true current time
ot_date_modified date – – true current date
ot_time_modified time – – true current time
ot_created_by varchar 256 – true current user
ot_modified_by varchar 256 – true current user
ot_status int – – true 1

Functions:- Basic CRUD

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Customer Rate Profile

This entity will be maintaining customer specific rates defined using the pre-created rate templates. And the Rate-RateBase value mapping will be isolated with Rate Rate Base Value entity.

CUSTOMER_RATE_PROFILE

field name data type length key is null default
crp_id int – pk false –
crp_rate_value double – – false 0
crp_is_on_workflow boolean – – false false
crp_close_by_workflow boolean – – false false
crp_description varchar 2048 – true –
crp_date_created date – – true current date
crp_time_created time – – true current time
crp_date_modified date – – true current date
crp_time_modified time – – true current time
crp_created_by varchar 256 – true current user
crp_modified_by varchar 256 – true current user
crp _status int – – true 1
c_id int – fk false –
r_id int – fk false –
ot_id int – fk false –
cr_id int – fk false –
uom_id int – fk false –

Functions:- CRUD, upon creation Rate – Rate Base Value entity will be updated in background.

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Rate Rate Base Value

This will be maintaining customer specific rate base value configurations.

RATE__RATE_BASE_VALUE

field name data type length key is null default
rrbv_id int – pk false –
crp_id int – ck / unique false –
rbv_id int –
r_id int –
rrbv_is_on_workflow boolean – – false false
rrbv_close_by_workflow boolean – – false false
rrbv _description varchar 2048 – true –
rrbv _date_created date – – true current date
rrbv _time_created time – – true current time
rrbv _date_modified date – – true current date
rrbv _time_modified time – – true current time
rrbv _created_by varchar 256 – true current user
rrbv _modified_by varchar 256 – true current user
rrbv _status int – – true 1

Functions:- This will be a background process where the creation will commit upon committing of the Customer Rate Profile.

Algorithms:- N/A

Security:- Logged user security scope

Validation:- N/A

  • Quotation Template

This entity will be maintaining customer specific or generic templates of quotations where they could be re-used.

QUOTATION_TEMPLATE

field name data type length key is null default
qt_id int – pk false –
qt_salutation varchar 512 unique false –
qt_first_ paragraph varchar 2048 – true –
qt_second_paragraph varchar 4096 – true –
qt_third_paragraph varchar 4096 – true –
qt_optional_paragraph varchar 4096 – true –
qt_helpdesk_contacts varchar 2048 – true –
qt_conclusion_clause varchar 2048 – true –
qt_validity_paragraph varchar 4096 – true –
qt_detention_paragraph varchar 4096 – true –
qt_insurance_paragraph varchar 4096 – true –
qt_payments_paragraph varchar 4096 – true –
qt_weight_restrictions varchar 1024 – true –
qt_other_details varchar 4096 – true –
qt_is_on_workflow boolean – – false false
qt_close_by_workflow boolean – – false false
qt_description varchar 2048 – true –
qt_date_created date – – true current date
qt_time_created time – – true current time
qt_date_modified date – – true current date
qt_time_modified time – – true current time
qt_created_by varchar 256 – true current user
qt_modified_by varchar 256 – true current user
qt_status int – – true 1

Functions:- Basic CRUD

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Quotation

Maintenance of Quotations generated for customers will be maintained within this entity.

QUOTATION

field name data type length key is null default
q_id int – pk false –
q_quotation_number varchar 9 unique false 112365001

(1yydddxxx)
q_valid_days int – – false 0
q_extra_free_waiting_hours double – – false 0.0
q_additional_waiting_hour_fee double – – false 0.0
q_is_approved boolean – – false false
q_is_on_workflow boolean – – false false
q_close_by_workflow boolean – – false false
q_description varchar 2048 – true –
q_date_created date – – true current date
q_time_created time – – true current time
q_date_modified date – – true current date
q_time_modified time – – true current time
q_created_by varchar 256 – true current user
q_modified_by varchar 256 – true current user
q_status int – – true 1
c_id int – fk false –
qt_id int – fk false –

Functions:- Basic CRUD

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Quotation Line

Quotation lines will represent the Rate Profiles created for customers. And those information will be useful in the phases of operations and invoicing.

QUOTATION_LINE

field name data type length key is null default
ql_id int – pk false –
ql_is_on_workflow boolean – – false false
ql_close_by_workflow boolean – – false false
ql_description varchar 2048 – true –
ql_is_extra_charge boolean – – false false
ql_date_created date – – true current date
ql_time_created time – – true current time
ql_date_modified date – – true current date
ql_time_modified time – – true current time
ql_created_by varchar 256 – true current user
ql_modified_by varchar 256 – true current user
ql_status int – – true 1
crp_id int – fk false –
q_id int – fk false –

Functions:- This will be a background process where the creation will commit upon committing of the Quotation. Or lines of the quotation could be handled as separate CRUD operation.

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Order Job Status

Status of the Jobs conducted from the Customer Rate Profile will be maintained in mater level.

ORDER_JOB_STATUS

field name data type length key is null default
ojs_id int – pk false –
ojs_status_title varchar 512 unique false –
ojs_display_on_dropdowns int – – false 1
ojs_is_on_workflow boolean – – false false
ojs_close_by_workflow boolean – – false false
ojs _description varchar 2048 – true –
ojs _date_created date – – true current date
ojs _time_created time – – true current time
ojs _date_modified date – – true current date
ojs _time_modified time – – true current time
ojs _created_by varchar 256 – true current user
ojs _modified_by varchar 256 – true current user
ojs _status int – – true 1

Functions:- Basic CRUD

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Order Job Status Order Type

Mapping of Job status / steps to each Order Type is maintained on this entity.

ORDER_JOB_STATUS__ORDER_TYPE

field name data type length key is null default
ojsot_id int – pk false –
ojsot_is_on_workflow boolean – – false false
ojsot_close_by_workflow boolean – – false false
ojsot_description varchar 2048 – true –
ojsot _date_created date – – true current date
ojsot _time_created time – – true current time
ojsot _date_modified date – – true current date
ojsot _time_modified time – – true current time
ojsot _created_by varchar 256 – true current user
ojsot _modified_by varchar 256 – true current user
ojsot _status int – – true 1
ojs_id int – unique / ck false –
ot_id int –

Functions:- Basic CRUD

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Order

This entity will be maintaining customer orders created out of respective quotations. Operational details will be recorded against the created orders.

ORDER

field name data type length key is null default
o_id int – pk false –
o_order_number varhcar – unique false 512365001

(5yydddxxx)
o_order_date date – – false –
o_deadline_date date – – true –
o_deadline_time time – – true –
o_is_completed boolean – – false false
o_completed_date date – – true –
o_is_started boolean – – false false
o_started_date date – – true –
o_is_invoiced boolean – – false false
o_is_jobs_created boolean – – false false
o_is_on_workflow boolean – – false false
o_close_by_workflow boolean – – false false
o_description varchar 2048 – true –
o_date_created date – – true current date
o_time_created time – – true current time
o_date_modified date – – true current date
o_time_modified time – – true current time
o_created_by varchar 256 – true current user
o_modified_by varchar 256 – true current user
o_status int – – true 1
q_id int – fk false –

Functions:- CRUD Operation

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Order Extra Charges

Ad-Hoc expenses / charges which are not identified in advance, are to be recorded on this entity.

ORDER_EXTRA_CHARGE

field name data type length key is null default
oec_id int – pk false –
oec_qty int – – false 0
oec_from_date date – – true null
oec_to_date date – – true null
oec_is_on_workflow boolean – – false false
oec_close_by_workflow boolean – – false false
oec_description varchar 2048 – true –
oec_date_created date – – true current date
oec_time_created time – – true current time
oec_date_modified date – – true current date
oec_time_modified time – – true current time
oec_created_by varchar 256 – true current user
oec_modified_by varchar 256 – true current user
oec_status int – – true 1
o_id int – fk false –
ql_id int – fk false –

Functions:- CRUD Operation

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Order Job

Multiple jobs are could be conducted over a single order. And each job details are maintained over this entity.

ORDER_JOB

field name data type length key is null default
oj_id int – pk false –
o_id int –
oj_number int – – false –
oj_is_completed boolean – – false false
oj_is_aborted boolean – – false false
oj_is_on_workflow boolean – – false false
oj_close_by_workflow boolean – – false false
oj _description varchar 2048 – true –
oj _date_created date – – true current date
oj _time_created time – – true current time
oj _date_modified date – – true current date
oj _time_modified time – – true current time
oj _created_by varchar 256 – true current user
oj _modified_by varchar 256 – true current user
oj _status int – – true 1

Functions:- This will be a background process where the creation will commit upon committing of the Order with the required numbers of jobs. Or this could maintain as a direct CRUD operation based on the requirement.

Algorithms:- N/A

Security:- Logged user security scope

Validation:- N/A

  • Order Job Status Audit Trail

Status tracking of each job is maintained on this entity where it will be an audit trail of each job.

ORD_JOB_ST

field name data type length key is null default
ojs_id int – pk false –
ojsot_id int – unique / ck false –
oj_id int –
ojs_date int – – false 0
ojs _is_on_workflow boolean – – false false
ojs _close_by_workflow boolean – – false false
ojs _description varchar 2048 – true –
ojs _date_created date – – true current date
ojs _time_created time – – true current time
ojs _date_modified date – – true current date
ojs _time_modified time – – true current time
ojs _created_by varchar 256 – true current user
ojs _modified_by varchar 256 – true current user
ojs _status int – – true 1

Functions:- This will be a background process where the creation will commit upon committing of the respective Order Status changes.

Algorithms:- N/A

Security:- Logged user security scope

Validation:- N/A

Specific modifications required based on business requirement could be modified over the above solution design. And the dynamic rates will not make any impact on the system structure as per the expected outcome of this research.

~ By Thisara

One Comment Add yours

  1. Pingback: Dynamic Rates Management Module Research | thisarame

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s