Data base design...the biggest nut to crack this week..

[rogerzebra]

Hi,
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][PAYROLL_EMPLOYEE][RATE] [ESTIMATED_ANN_PREM]
8854 $$$$$$$ *% = $$$

[CLASS_CODE][PAYROLL_EMPLOYEE][RATE] [ESTIMATED_ANN_PREM]
8264 $$$$$$$ *% = $$$

[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. 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

 

[rogerzebra]

I know this is much to ask for, but I'm really lost with this one and could need a couple of good advice from a sharp DBA. I had to edit the equations they should read like this.

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

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

[ESTIMATED_ANN_PREM(8854)]+[ESTIMATED_ANN_PREM(8264)] = [TOTAL_CLASS_PREMIUM]

 

[TonyGroves]

Sorry rogerzebra, but it's just too much information to sift through and think about. I'm not going to even start reading it. If you could isolate the main problem and describe it reasonably briefly, you would most likely get a better response.

 

[rogerzebra]

Thanks TonyGroves,
I see what you're saying. I'll trim it down to make it easier to comprehend.

 

[rogerzebra]

A second try..
I'm building an ER database and I'm not sure if my database model is functional as it is or logical buildt for my purposes. So I would like for someone to look over my first draft and perhaps give me some answers of my concerns.

So here is part of my tables.

Submission table
SubmissionID(pk)
Fein (fk)

Carrier table
SubmissionID(fk) CarrierID (fk)
xxxx 1
xxxx 2
xxxx 3

Classcode_rate table
CarrierID(pk) 1001 1002 1003 1004 1005 1006
1 0,34 0,45 0,56 0,43 0,67 0,54
2 0,35 0,54 0,32 0,46 0,57 0,34
3 0,26 0,47 0,57 0,39 0,34 0,67

Payroll 2004 table:
SubmissionID (fk)
SubmissionID 1001 1002 1003 1004
15001 $$$$ $$$$ $$$$ $$$$
15002 $$$$ $$$$ $$$$ $$$$
15003 $$$$ $$$$ $$$$ $$$$

The payroll table has the same difficulty as the Classcode_rate table. We want to be able to see the payroll for each classcode. That's why each payroll table it's separated for each year and we only keep track 5 years back at the time. As the sample shows above were the classcodes in this example is the numbers 1001,1002... the $$$$ is the payroll amount for each classcode and submissionID is the clientID.

Questions:
1. Is there any rules of how to use the same forrein key in several tables? Can a table have two forrein keys and no primary key? Can a table just have one forrein key and be without a primary key?

2. In my classcode_rate table I have been using floating point values which is a classcode values for the different carriers. Someone told me that the data in the classcode_rate table is not normalized. But what about in my case I don't want them to be normalized when a classcode can have same values between the carriers. What to do?

I just tried a test to buildt the classcode_rate table and if the CarrierID as a (pk)and no(fk), it allows me to save a floting point value for each classcode.
Is there anything wrong to solve it this way?

I'm open for suggestions or if someone can point out a good example or tutorial of the same issue?

Believe me I have tried to read up on and to understand normalisation on various webpages, but I haven't been able to find anything on how to solve a situation similar like mine.

Thanks in advance.
/rz

 

[TonyGroves]

You can have any number of foreign keys in a table, and a primary key is totally optional.

Your classcode_rate table in its current structure is an unnormalised cross-tab table. It should really be converted to a table with records in the form (1001, 1, 0.34), (1002, 1, 0.45), etc. The same applies to your payrool_2004 table.

 

[rogerzebra]

Thanks TonyG...
I have already changed them. I don't know what I have been doing lately..to tired I guess my brain could need a vacation..Thanks anyway I really appreciate it.
/rz