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:
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?
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?
Maybe there is another smart way to implement this that we are not aware without using the CarPolicy table?
