|
||||
|
ITSBITS Software Help Intranet Home Purpose/OverView Open ITSBITS SQL diagram Concepts Billing Calc. Methods Billing Types Chart Historic Data Storage Map Data Edit/Entry General Instructions Cancellations Getting Started w/Data Entry ITSBITS Portal-Of-Entry ITSBITS DB Tables Maintenance Historical Data Reports Reports Viewer Data Processing ITSBITS System Configuration User License System Requirements Installation Troubleshooting Guide
|
This system is built around what we call a SQL (pronounced "sequel") data base. Consequently, the best place for you to begin to understand what the system is all about is by studying and understanding the basics of how the database tables fit together. To let you best follow the description of the database, you should open a second browser window with the diagram showing in it. Once you do that, you can use the ALT+TAB key combination to toggle between this browser window and the diagram browser window as you study. To open the diagram browser window, simply click here. As you will see in the diagram, there are several tables involved. Here, with associated icons, is a thumbnail description of what is stored in each database table:
PolicyHolder Path
Let us assume that you sell insurance to all the employees of a company (or to a large group) through an agreement with the company (or large group) in question. For example, suppose Joe Blow Trucking has agreed to do payroll deductions from their employees and work with you to provide health insurance to all the Joe Blow employees. In such a case, this database table will store your information about the company. Again, in our example, we would store information about Joe Blow Trucking in this table. The information we would store would include such things as the company name, mailing address, telephone number(s), website URL, contact-person information, etc. We might also want to store some notes about the company or the company contact-person. Of real importance, we will store monthly premium information here which will apply to each of the company's insured employees. All of this information is accessable via the Company table of the database.
Now, let us assume that there is a "Unit" involved with a company's employees and that for each "unit" you are going to charge an administrative fee on each "main" insured. Two examples of this can be given. First, let us return to the Joe Blow Trucking company and say that each truck is a unit. In this case, you would charge an administrative fee for each "main" insured, i.e., the main driver, for each truck. Second, let us assume that you are selling to a company that will pay an administrative fee for each family you insure. Of course, there may be a spouse and one or more children in a family, so you may have 10 insureds in a single unit, but you can only charge one administrative fee for the "main" insured. In this table, we will store information about the unit and the administrative fee that is to be charged for it. In addition, you will note that an entire unit can be cancelled at one time and there is a field here where you can enter the unit-cancellation-date to accomplish this. If no such administrative fee is being charged, no AdminFees Table entry will be used.) As you might guess by the table's name, this is where we store information about each person who is insured. This information includes such obvious things as their name, address, Social Security Number, and other contact information. In the event we are using administrative units, we also store which other insured a given person's policy is "subordinate" to. (The "main" insured is the one all others in the unit are subordinate to.) To make this work, each Insured record includes a "UnitKey" which points back to the AdminFees Table record when an administrative fee is involved. (If no administrative fee is being charged, this field will simply be left blank and no AdminFees Table entry will be used.) Also stored in this table is a code that tells us which of our sales people is responsible for this person's account and how much commission they are to receive from this person's insurance. To help with this side of the business, there is a place for the sales person's notes to be stored. Additionally, there is a place for notes of a general nature to be stored. There is also a date where we can keep a date-cancelled and notes about the policy cancellation, e.g., why, when a notification was sent to the insured, the company, etc. 
This table is the heart of the entire system. Here we will first discuss the concept of "paths" as it applies to the software system. There is a record on this table for each active policy. On each record, there is an InsuredKey identifier that points back to the Insured Table, which points back to the AdminFees Table as well as the Company Table. This chain is called the PolicyHolder Path and by going up or down this path (with a dog-leg for the sales person) you can learn all we know about the policy in question as far as the insured part of the equation is concerned.
Also on this table is an entry called the PolicyKey which points back to the PolicyMaster Table, which points to the InsuranceCarrier Table. This chain is called the Carrier Path and by going up and down this path you can learn all we know about a given policy as far as the insurance carrier part of the equation is concerned. While we're here, a word is in order about the billable and payable premium fields. The billable field will normally be the total of the billable premium from the Company Table and, if applicable, the administrative fee from the AdminFees Table--this is the amount we will bill to the company each month for the policy in question. The payable field will be the same as the payable premium on the PolicyMaster Table and is the amount we have to pay the insurance carrier for the policy in question each month. (Also, remember that the policy's effective date must be past and that the policy must not have been cancelled as shown by a past cancelled date on the AdminFees, Insured, or ActivePolicy tables.) On this table there will be an entry for each sales person you have. Essentially, we will store the sales person's contact information and their Social Security number so that we can generate data for their 1099 IRS forms. Carrier Path
As the name implies, here we store information about the insurance company, or carrier. Essentially, this is where we store who the company is, how to contact them, and where to send their money. Each carrier is know by an ICCode which the PolicyMaster Table will use to point back to this table. This table contains information about the type of policy in question, including the billable premium (the normal amount you would charge the insured), the payable premium (the amount you must pay the insurance carrier each month to keep each of these polices in force, and the calculated commission (simply the billable premium less the payable premium) that we get for each policy of this type that is active. The ICCode field here points back to the InsuranceCarrier Table record. The PolicyKey field here identifies each record and is pointed to by the ActivePolicy records involving this type of insurance policy.
Other Tables
No matter how hard we try, there will always be billing corrections that must be entered to keep the system "balanced." This table is where such corrections, either negative or positive, will be entered, along with information about what caused the need for a correction, who made it, etc.
In like manner, no matter how hard we try, there will always be corrections that must be entered on the Carrier Path to keep the system "balanced." This table is where such corrections, either negative or positive, will be entered, along with information about what caused the need for a correction, who made it, etc. Here we will store data which shows a snapshot of how much was billed for the month and how much has been received. DO NOT confuse this with a normal accounting setup's Accounts Receivables report--it is not the same thing, though it is similar.
The Ball-Drop-Game Concept You need a Company table record before you can build a viable AdminFees table record. You need a Company table record and, if it is applicable, and AdminFees Table record before you can build a viable Insured Table record. In like manner, you need an InsuranceCarrier Table record before you can build a viable PolicyMaster record. Finally, you need a PolicyMaster record and an Insured Table entry before you can build an ActivePolicy table record. In like manner, you must have a viable Active Policy record before you can build a BillingCorrections record. Oh, yes, and we should mention that there must be a Salesmen Table record before you can use a sales peson's code on an Insured Table record. In fact, that is the key to the whole thing--you cannot "point" to a record on any other table if that record does not exist. Once you understand this simple truth, your use of the database will be far easier.
|
|||
|
|
Website by IHS, Incorporated See www.IHSsoftware.com Email: CustomerService@IHSsoftware.com |