Hi everyone,
I have been going back and forth with the design of a database for an application I am building, including having built the database two different ways. The client performs consultations for an individual's pension at retirement. The individual visits the office and based upon the individual's work history and their retirement date, an estimate of the individual's pension is created. Here is where it gets tricky, maybe it is just me - the individual can have multiple retirement dates to see which scenario works best for him/her, and they could retire under one of three categories. So the user of the application can perform a pension estimate numerous times with the same base data of the retiree many ways. I allow the user to save any calculation for a retiree at any time. So in essence, I could have numerous consultations saved in the db for an individual, but the criteria for each record is different for each consultation.
Here are my current tables -
Members
Counties
Positions(Title)
Pension_Categories
Consultations(Orders)
My other database has the same tables except there is an additional table titled Consultations_Details. I abandoned that approach because this db is not similar to a trasaction db that will have the products and details listed in a single order(transaction) that would be listed in an order details table, for instance. Plus the composite key for the Consultations Table was the OrderID and the Retirement Date, which would not work, because multiple records could be calculated with the same retirement date but with a different Pension Category.
So under my current design there is a new Consultation record generated in the db for each record saved even if the retiree is present during one consultation. Right now I am designing reports with dummy data and one report with records sorted by County will have a retiree listed multiple times since I have two records for one retiree because one pension estimate was made with one pension category and one was made with another pension category. So my data is a bit redundant.
It seems easy to say to create a Consultation_Details table that has a composite key made up of the ConsultationID, the PensionCategoryID and the Retirement Date. Would this be the right design for this database or should the Consultation_Details table be broken down into two tables?
Thanks in advance,
Rob [Vader2]
I have been going back and forth with the design of a database for an application I am building, including having built the database two different ways. The client performs consultations for an individual's pension at retirement. The individual visits the office and based upon the individual's work history and their retirement date, an estimate of the individual's pension is created. Here is where it gets tricky, maybe it is just me - the individual can have multiple retirement dates to see which scenario works best for him/her, and they could retire under one of three categories. So the user of the application can perform a pension estimate numerous times with the same base data of the retiree many ways. I allow the user to save any calculation for a retiree at any time. So in essence, I could have numerous consultations saved in the db for an individual, but the criteria for each record is different for each consultation.
Here are my current tables -
Members
Counties
Positions(Title)
Pension_Categories
Consultations(Orders)
My other database has the same tables except there is an additional table titled Consultations_Details. I abandoned that approach because this db is not similar to a trasaction db that will have the products and details listed in a single order(transaction) that would be listed in an order details table, for instance. Plus the composite key for the Consultations Table was the OrderID and the Retirement Date, which would not work, because multiple records could be calculated with the same retirement date but with a different Pension Category.
So under my current design there is a new Consultation record generated in the db for each record saved even if the retiree is present during one consultation. Right now I am designing reports with dummy data and one report with records sorted by County will have a retiree listed multiple times since I have two records for one retiree because one pension estimate was made with one pension category and one was made with another pension category. So my data is a bit redundant.
It seems easy to say to create a Consultation_Details table that has a composite key made up of the ConsultationID, the PensionCategoryID and the Retirement Date. Would this be the right design for this database or should the Consultation_Details table be broken down into two tables?
Thanks in advance,
Rob [Vader2]