We will create an entity-relationship diagram for an Auto Insurance company to keep the policy information and you can see the data dictionary of this E-R diagram in Figure 2, which tells the purpose of each column. This is just a sample. If you want to use it in the real world, you need to customize it, in another words, you need to add more tables like archiving the old data if customer renews the policy, etc.
Every application needs a normalized database and it should be designed before writing a code and developing UI. This gives a better picture of the application. Normalized database is one of the most challenging tasks in software development. If database is denormalized or could not support the future enhancement, then you need to re-build your project from scratch which is costly. These days, developers want to use ORM tools so database should be normalized.
Let's talk about our example. We would like to create a database for a small Auto insurance company what information we need to save in our database for example, Policy coverage, Policy holder's address, name, SSN, vehicle information, bill, payment, traffic violation records. These are some of the data that we need to store. Let's create a
Policy table to save these data. It's not a good practice to keep all of these information in a single table. These information should be kept in a different table so that we could reduce the data redundancy. Here, we don't want to keep the billing and vehicle information in the same table. Billing information should be kept in one table and vehicle should be kept in another table.
Whenever we design the database, we talk about the Normalization. It is the process of organizing the data to reduce the redundancy.
1st Form of Normalization
We remove the repeating group of columns in the separate table. Therefore, we will keep the Vehicle in
Vehicle table and Billing information in
Bill table. Each record should have a unique identify column so we will create Primary Key column in every table.
2nd Form of Normalization
We remove the repeating set of records into the separate table and make join between the parent and child table. Here, we will make the Join between
So, we will create a different table to make our database as a Normalized database. Below is the list of tables:
List of Tables
Policy: This table holds the general information about the policy like
Vehicle: This table keeps the information about the insured vehicle. There could more than one vehicle under a policy so all vehicles will go under this table. There is One-To-Many relationship between Policy and Vehicle table.
Driver: There could be more than one driver under a policy. Each drives Name, Date of Birth, SSN, Driver licence number will store under this table. There is One-To-Many relationship between
Vehicle_Driver: There is Many-To-Many relationship between
Vehicle and this is the bridge table for
DriverAddress: This table holds the driver's mailing and garage address, that's why there is One-To-Many relationship between
TrafficViolationCode: This table holds the Traffic Violation Code. There is Zero-To-Many relationship between this table and
Driver_TrafficViolation_Record. Similarly there is Zero-To-Many relationship between
Driver_TrafficViolation_Record. We have zero to many instead of One-To-Many because some drivers have never violated the traffic rule.
Driver_TrafficViolation_Record: There is Many-To-Many relationship between
TrafficViolationCode table and this table acts as bridge table between them.
Bill: There could be more than one bill for a policy so we will have One-To-Many relationship between
PaymentDetail: There is One-To-Many relationship between the
Customer could make multiple payment transactions or one.
Coverage: There are different type of coverage in the Auto Insurance like Liability, Collision, Comprehensive, Rental, Medical Payment, Towing, Mechanical Break Down. Some of them apply only to policy, some only to vehicle and some of them to both.
IsVehicleCoverage tell, is it applicable to
Policy and if it is applicable to both, then these two
bool columns will have
CoverageName column holds the title of coverage like "Property Damage Liability 100000/300000" or "Property Damage Liability 25000/50000" and
CoverageGroup holds information about which coverage this record belong to like "Medical Payment" or "Bodily injury and Property Damage Liability Coverage" or "Uninsured Motorist Coverage".
Policy_Coverage: There is Many-To-Many relationship between
Policy table and this is bridge table between them
Vehicle_Coverage: There is Many-To-Many relationship between
Vehicle table and this is bridge table between them.
PolicyEditLog: This table keeps the log information if
customer updates the
policy information like remove vehicle or add driver, etc.
Figure 1 is the E-R Diagram of Policy Database
Figure 2 Below is the Data Dictionary of Above ER diagram