how to design this relation in a DB schema

Question

I have a table Car in my db, one of the columns is purchaseDate. I want to be able to tag every car with a number of Policies (limited to 10 policies). Each policy has a time to life (ttl, a duration of time, like '5 years', '10 months' etc), that is, for how long since the car's purchaseDate the policy can be applied.

I need to perform the following actions:

• when inserting a Car, it will be set with a number of Policies (at least one is set)
• sometimes a Car will be updated to add/remove a Policy
• searches must be done taking into account date/policies, for example: 'select all cars that are not covered by any policy as of today'

My current design is (pol0..pol9 are the policies):

CREATE TABLE Car (
 id int NOT NULL IDENTITY(1,1),
 purchaseDate datetime NOT NULL,
 //more stuff...
 pol0 smallint default NULL,
 pol1 smallint default NULL,
 pol2 smallint default NULL,
 pol3 smallint default NULL,
 pol4 smallint default NULL,
 pol5 smallint default NULL,
 pol6 smallint default NULL,
 pol7 smallint default NULL,
 pol8 smallint default NULL,
 pol9 smallint default NULL,
 PRIMARY KEY  (id)
)

CREATE TABLE Policy (
 id smallint NOT NULL,
 name varchar(50) collate Latin1_General_BIN NOT NULL,
 ttl varchar(100) collate Latin1_General_BIN NOT NULL,
 PRIMARY KEY (id)
 )

The problem I am facing is that the sql to perform the query above is a nightmare to write. As I don't know in which column each policy can be, so I have to check all columns for every policy etc etc. So I am wondering wether it is worth changing this. My questions are:

1. The smallint as Policy id was chosen instead of an 'int IDENTITY' in order to save some space as there are going to be millions of Car records. It just adds complexity when creating a Policy as we must handle the id etc. Was it worth doing this?

2. I am thinking that maybe there is a much better design? Obviously we could move the policy/car relation to its own table CarPolicy, benefits would be:

   • no limit on 10 policies per car
   • adding/removing etc much easier
   • when only the default policy is applied (when no others are applied one called Default policy is applied), we could signal that by not having any entry in CarPolicy, now this is just done inserting the Default policy id in one of the columns.

   The cons are that we would need to change the DB, ORM classes etc. What would you recommend?

3. Maybe there is another smart way to implement this that we are not aware without using the CarPolicy table?

Answer 1

1. It's been a long time since it was worth using smallint to save space; you aren't going to see any significant difference in performance doing it that way, it just creates potential headaches.

2. A separate CarPolicy table is definitely the right way to do this, for the reasons you listed. There's an upfront time cost of making the change, but once that's done it'll be done, and it'll pay for itself many times over in trouble saved.

Answer 2

I would recommend giving the relation it's own table, containing a car id and a policy id.

Answer 3

I would recommend changing it so that you have a Policy table that has the Car Id in it. So instead of linking to a bunch of policies in the Car table, you link to a single Car in the Policies table. Then you include a TTL column and a Status column, so they can expire and you can query using the Car Id in the policies table.

As long as there can be only 1 Car per policy, and exactly 1 Car per policy (who has a policy without a car, right?) then this is a good way to do it.

UPDATE: Ahh, I misread it then. If it's a many to many then do as everyone else said and make the CarPolicy table.