SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    san diego
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database structure

    Hi,
    I'm stuck with an issue how to best design the tables for our purposes. I hava a submission table including: submission id, name insured, fein number, effective date, date recieved, date submitted, producer, contact, class code.
    and a producers table including producer code, producer name, adresses, zip etc.
    Now my intension is to implement other tables including a quotation part and a payroll history and premium/loss history. My first question is if it's wise to put the payroll and premium/loss history together with the rest of the applicants info on the submission table. Or should I have them on sepatarate tables one for the payroll history and one for premium/loss history.

    My next issue is for the quotation table I've a form to fill in with the applicants name and adress, zip and policy period and binder period. The only new information on this sheet is the binder and policy period. Applicants adress,zip contacts is already in the submissions table and I don't want to save the same information saved on the quotation table aswell. I guess best approuch here is to join them, but in my case we need to retrieve the information back on a form and be able to print it out. I hope that maked sence.
    I would appreciate if someone could give a couple of good advices regarding this. Thanks in advance
    /rz

  2. #2
    Non-Member
    Join Date
    Apr 2004
    Location
    Morristown
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by rogerzebra
    Now my intension is to implement other tables including a quotation part and a payroll history and premium/loss history. My first question is if it's wise to put the payroll and premium/loss history together with the rest of the applicants info on the submission table. Or should I have them on sepatarate tables one for the payroll history and one for premium/loss history.
    Separate - this will allow you add features/tables without affecting existing applicants table

    Quote Originally Posted by rogerzebra
    My next issue is for the quotation table I've a form to fill in with the applicants name and adress, zip and policy period and binder period. The only new information on this sheet is the binder and policy period. Applicants adress,zip contacts is already in the submissions table and I don't want to save the same information saved on the quotation table aswell. I guess best approuch here is to join them, but in my case we need to retrieve the information back on a form and be able to print it out. I hope that maked sence.
    I would appreciate if someone could give a couple of good advices regarding this. Thanks in advance
    /rz
    Simply select from both tables - below our tables are called employee and info

    SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
    WHERE t1.uniqueID = t2.uniqueID;

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    san diego
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    This will probably be this weeks biggest nut to crack

    Okey, I realized that my recent explanation was not accurate at all and I'm apologizing for my lousy explanation(to tired). Anyway, I'll give it another shot now when I'm better off so here is my problem.

    I'm trying to find a way of how to design the database, so it suits our needs. I can't see a good design structure for my problem, so if anyone could have a look at this and come up with good suggestions would be greatly appreciated. In order for you to understand my problem, I've to show you the workflow and how certain information is dependent on each other. Okay, so here it goes...

    I have following tables,
    Submissions: submission_id(pk), name_insured, effective_date, producer...etc
    producers: producer_code, producer(pk), adress…etc
    Payroll:submission_id(pk), 2004-2005, 2003-2004, 2003-2002 ..etc
    Premium: submission_id(pk), policy_period, valuation_date, earned_premium
    Quote: submission_id(pk), total_premium, policy ...etc
    Carrier_ID ???
    Carrier1_Class_Code_ID ???
    Carrier2_Class_Code_ID ???
    Carrier3_Class_Code_ID ???
    Carrier4_Class_Code_ID ???
    Carrier5_Class_Code_ID ???


    Description of workflow:
    The Quote Form grabs information from the submission table and the producer table. It also suppose to grab information from the tables Payroll, Premium and from possible tables Carrier_ID and a Class_code_Carrier tables which I still haven’t figure out how to structure yet.
    The Quote table row "total_premium", gets its value from row [estimated_annual_premium] in table Premium. The [estimated_annual_premium] gets its value from row [rate] from table Carrier_Class_Code which is a variable and calculates its value from row [payroll_employee] in the table Payroll.

    No... We are not there yet, so what we got so far is:

    PAYROLL_EMPLOYEE * RATE = ESTIMATED ANNUAL PREMIUM

    Each calculation has a class code connect to it and each applicant can have 1 or more class codes. Each class code has its own static rate value. If I add all the applicants’ different [estimated_annual_premium] together I get the result value for row "total_premium", in the table Quote. See the example beneath were the applicant have two class codes:

    CLASS_CODE(8854)
    [PAYROLL_EMPLOYEE($$$)] * [RATE(%)] = [ESTIMATED_ANN_PREM]

    CLASS_CODE(8844)
    [PAYROLL_EMPLOYEE($$$)] * [RATE(%)] = [ESTIMATED_ANN_PREMIUM]


    [ESTIMATED_ANN_PREM] + [ESTIMATED_ANN_PREM] = [TOTAL_CLASS_PREMIUM]

    That's how the work flow goes. To get back to my problem and further structure issues.
    As I mentioned earlier an applicant can have more than one class code connect to their business. The problem is that there are 5 different Carriers and they are using same class code numbers and of course all class codes rate value differs between the 5 Carriers.
    So, in other words an applicant’s submission can only contain one Carrier but several class codes were each one of them has its own variable a % value.

    WOW …hhm getting dizzy when I describe my problem…did any of you get that??… I hope it makes some sense and that I’m capable to explain my issues in a way so you are able to understand it.

    Anyway, here are my thoughts and they are not a final or functional solution by this stage. So, I’m thinking along these lines, first to make a table with Carrier ID’s and then for each one of the Carrier a table shown beneath;

    Carrier_ID including the rows [submission_id], [carrier1_id], [carrier2_id]...etc
    and then make a table for each one of the 5 Carriers, table as following
    Carrier1_Class_Code including the [submission_id], [class_code_1], [class_code_2]…etc
    Carrier2_Class_Code including the [submission_id], [class_code_1], [class_code_2]…etc
    Carrier3_Class_Code including the [submission_id], [class_code_1], [class_code_2]…etc
    Carrier4_Class_Code including the [submission_id], [class_code_1], [class_code_2]…etc
    Carrier5_Class_Code including the [submission_id], [class_code_1], [class_code_2]…etc


    I hope, I’m on right track here and that some of you perhaps can help me solve this chess game(at lest in my mind). The structure is even more complicated when a submission can have more than one class code and there is rate value connected to each one of them. It doesn’t make it easier when you have 5 Carriers using the same class code numbers and each of the 5 using a different value for same class codes.

    So, my question is how to best structure this and still have some flexibility to make changes if I need to? I’m not sure if my solution is functional, so please if anyone have a better idea or a functional solution on my problem, share your knowledge. Thanks for reading my post and helping me out on this one.
    /rz
    Last edited by rogerzebra; Mar 11, 2005 at 15:00.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •