I'm putting together a DB to record information on the insurance agencies with which our company does business. These agencies have contracts that allow them to write policies in one or all of the following lines:
1. Standard
2. Governmental
3. Specialty Workers Comp
4. Specialty Auto
Since an agency can have any combination of the above four options, I was curious if it is better to:
1. Have four Yes/No fields (one for each line) in a table or
2. Create a child table where a record would exist for each line an agency was contracted for. For example:
AgencyCode Line
CC1234 Standard
CC1234 Specialty Auto
CC9876 Standard
The catch in my scenario is that an ageny can gain and lose the privilege to write different lines during their contract with us. For example they my write only standard business with us, but we later agree to write a specialty business with them. The opposite can occur. As a result I will need termination dates for each line.
In scenario one, we would need to add a termination date for each insurance line and many of the records would be undoubtedly left blank which seems not to be very well normalized. So I'm leaning toward senario 2.
The question is admittedly elementary, but I've been known to over analyze the obvious. Perhaps there is another angle I'm missing. Thanks for any feedback.
- Matt
1. Standard
2. Governmental
3. Specialty Workers Comp
4. Specialty Auto
Since an agency can have any combination of the above four options, I was curious if it is better to:
1. Have four Yes/No fields (one for each line) in a table or
2. Create a child table where a record would exist for each line an agency was contracted for. For example:
AgencyCode Line
CC1234 Standard
CC1234 Specialty Auto
CC9876 Standard
The catch in my scenario is that an ageny can gain and lose the privilege to write different lines during their contract with us. For example they my write only standard business with us, but we later agree to write a specialty business with them. The opposite can occur. As a result I will need termination dates for each line.
In scenario one, we would need to add a termination date for each insurance line and many of the records would be undoubtedly left blank which seems not to be very well normalized. So I'm leaning toward senario 2.
The question is admittedly elementary, but I've been known to over analyze the obvious. Perhaps there is another angle I'm missing. Thanks for any feedback.
- Matt
