Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2003-12-04 : 12:25:58
|
| Hi,I'm a little confused on the best way to set up a table structure. Here's the deal, I'm writing an application where users can apply for home and auto insurance (I've posted questions on this in the past). Although these are both types of insurance they don't have a lot in common. Here is my basic database structure. I have a set of tables which store client specific insurance data (the client can have a number of insurance quotes). I also have 2 sets of tables which store the insurance rating data. One set is for auto and one set for home (since they are so different it is a GOOD thing all the rating data is separated). For example, both types of insurances have payment plans (the method on how you want to pay your bill). There is a lot of rating data associated with payment plans which is completely different for home and auto. So I have "HomePaymentPlans" and "AutoPaymentPlans" tables. When the user selects the payment plan, the id is stored in the "QuotePaymentPlans" table (a user can have multiple insurance quotes each with a different paymentPlan).QuotePaymentPlansQuoteID int,PaymentPlanID intAutoPaymentPlansPaymentPlanID int... rating specific columnsHomePaymentPlansPaymentPlanID int,..... rating specific columns Each QuoteID is associated with one type of insurance so given the QuoteID it is known Which PaymentPlan table to join to etc. In an ideal world I think it would make sense to have 3 rating tables, one for common paymentPlan data and 1 each for home and auto specific data. This would actually be very cumbersome since the only commonality is the ID column. When the application is dealing with an auto quote it references the client and auto rating tables. When it is dealing with a home quote the app only uses the client and home rating tables. I realize that I can't create constraints with this setup but does the dual purpose quote table make sense?Nic |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-04 : 13:04:28
|
| I would create a QuotePaymentPlans for each of the types of insurance, that way you won't have problems with the IDs coming from multiple tables. You would also be able to create constraints in this method.Tara |
 |
|
|
nic
Posting Yak Master
209 Posts |
Posted - 2003-12-04 : 14:57:12
|
| Thanks, On a similar note dealing with table naming conventions. I know I can name my tables anything I want but I'm just curious how others (with more experience) would handle this. So there are a series of client quote tables for the home and auto products. Some tables are shared bewteen home and auto (ie. QuotePersonAddresses), some are completely defined for only one type of insurance (QuoteDriverViolations) and others are broken into two tables (i.e. QuoteHomePaymentPlan, QuoteAutoPaymentPlan) since they are unique for each insurance type but have similiar namesGiven this, would you include the words "home" or "auto" in the tables that are only used for one insurance type?for example: change QuoteDriverViolations to QuoteAutoDriverViolations.Or since DriverViolations inherently only deals with auto policies just leave as QuoteDriverViolations.I know this is kinda a dumb question but I always struggle with setting up good naming conventions.Nic |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-04 : 15:03:23
|
| For DriverViolations, I would leave it as is. But for those tables that you need to have two tables, then include home or auto. That's my opinion at least.Tara |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2003-12-04 : 22:14:55
|
quote:
On a similar note dealing with table naming conventions. I know I can name my tables anything I want but I'm just curious how others (with more experience) would handle this.I know this is kinda a dumb question but I always struggle with setting up good naming conventions
A good naming convention is as brief as possible. If half of your tables deal with Quotes, name them QtSomething. Likewise, Payments makes sense as Pmt. Did you ever write front-end code or T-SQL for your tables? You'll type the skin off your fingers with such long names, or some programmers out there will curse you till the wee hours of the night... Sarah Berger MCSD |
|
|
|
|
|
|
|
|
Table Design question