problem designing database

Question

hi, im using sql server 2008. my scenario is as follows.

there is a user table, with userid, password, ....rest of the fields

it is something related to insurance, the cases are as follows

1. logged-in user is insured (there is only one person here)
2. logged-in user is insured and there is one more insured under the same user (there are two persons here, one is logged-in and other is insured, its a 0-many relationship)
3. logged-in user is not insured and there is one insured under the same user.

here my problem is in saving the personal data of logged-in user and insured. i mean there are same data fields for logged-in user and insured. i mean

if the logged-in user and insured are same then, the personal data will be same. (i dont want to repeat the same data in two different tables)

if the logged-in user and insured are different, then personal data will be different for both.

how do i design the database tables

please help...

Answer 1

Assuming you have a Policy table, holding different insurance policies, I would create a many-to-many table linking the User table to the Policy table using their respective IDs.

This will allow you to simply update user details once and link policies to users in any way you want.

For you scenarios:

1. Logged in user with policy - the table will have the user id with the policy id.
2. Logged in user without policy, other users have policy - the table will hold the other user IDs and the policy id per each.
3. Logged in user with a policy and other users have policy - the table will hold the all of these user Ids and the policy id per each.