Cars, License Plates, And Using The Wrong Identifier


More Database Design Mistakes To Avoid
Architecture Mistakes To Avoid

I’ve now moved to a few different states, and registered my car in the new state.

Even though I was only a customer, and never saw the back end database, I could quickly tell which identifier the states were using for the car.   Each identifier had huge implications on the whole process, the consumer, the insurance companies, and the DMV.

In Connecticut, the process (in 1999) I followed was as follows.

Still using the insurance and license plates from your previous state, go to the government car inspection facility to get your car inspected.  The inspection is good for a number of months.

If government inspection points out problems with the car,

- Are Problems repairable?
- Yes.  Fix car first.  Reinspect.  Get inspection sticker.
- Else (Problems Not Repairable)
- Decide what to do with car: Trade it in, sell to a junkyard, give to your relative in another state, etc.

end if

Buy insurance

Register, get license plate

Mail previous plate back to the old state so that you are no longer taxed on the car.

—–

In Massachusetts (in 2000), the process was different.

First, buy insurance for car.  Don’t even bother going to DMV without it.
- Register car, get new Massachusetts license plate,
- Remove old plate, attach new MA plate,
- Have car inspected at independent garage within 7 (?) days.

If inspection points out problems with the car,

- Problems repairable?
-  Yes. Fix car.  Reinspect. Get inspection sticker. Mail previous plate back to the old state so that you are no longer taxed on the car
-
- Else (problems non-repairable, or too expensive to be worthwhile.)
- Decide what to do with car: trade in, sell, scrap, give to your relative in previous state, etc.
- Rollback registration
- Rollback insurance.  Probably have fees to pay.
- Now the car would be unregistered in MA and the previous state.  What would happen at this point?  Could you put your old plates back on long enough to dispose of the car?  Are you allowed to drive the car on streets with old plates? Are you in limbo?

End if

Mail previous plate back to the old state so that you are no longer taxed on the car.

In Connecticut, it was easy to see that the identifier was based on the VIN (Vehicle Identification Number).  The car was inspected using the VIN.  The license plate could be out of state.  After the license plates were changed, the inspection was still good, and still applied to the car via the VIN.

In Massachusetts, it was easy to see that the registration process was based on the license plate.  You couldn’t get the car inspected without first getting a Massachusetts plate.  That’s what the inspector wanted to see.  Although, the VIN was recorded, the new license plate was the prerequisite to getting an inspection.  (Or so I was told.)

Results:

If you look at the two systems, it should be obvious that Connecticut had the superior system.  Before you even bothered going to get your insurance or register the car, you knew that the car had passed inspection.  There would be no surprises in that area.  It was just a case of getting the insurance and registration.  It would save time and effort for the consumer, the DMV, and the insurance companies.

Massachusetts, however, had lots of potential for problems.  The system assumed that everything would be fine, and there would be no serious problems with inspection. And I’m sure the vast majority of new out of state registrations fit into this category.

But what if it did not pass inspection?  What do you do at that point?  Would you rollback the whole process, and get a (partial) refund for your insurance, and all the hours of work that would entail?  Buy another car, and transfer the registration?

While I’m sure that they have worked out their policies over time, in systems, we know it’s much better to prevent problems from occurring in the first place.  Ie.  Stolen credit card numbers.

I have to wonder about what the thought process was in designing this system.  How did they come up with it?  And what kind of expertise designed it?  The politicians sitting in session?  A manager?  A business analyst?  A programmer?  I did hear that the Massachusetts government typically paid their database people less than what was common in private industry.

This is somewhat related to the concept of using surrogate keys.  The VIN perfectly identifies the car, is unique, uses many more digits (17) than a license plate (6 in Massachusetts), and it never changes.  Did a data warehouse specialist have an idea that he needed to come up with another identifier (license plate) for the car?

Contextual License Numbers:

Actually, it gets even worse in Massachusetts.  Once a lady made me so angry I went down to police station to complain.  I gave the license plate of the car.  The policeman wasn’t making sense of my description of the car and driver, and the license plate.  Finally I told him that the car was a taxi, and the plate had “Livery” written on it.  Now it made sense.

So, in addition to not using the VIN to identify the car, when Massachusetts uses the license plate to identify the car, it’s contextual.  You also have to know what type of license plate it is.  In this case, there was actually an overlap of license numbers!  The license number by itself, in fact does not uniquely identify the car!  What if I had instead been reporting a kidnapping and get away?  You don’t want to waste any time at that point.

Correction:

Consider the implications of the key that you are using.  Walk through the whole process.  Not just for your organization, but for the customer too.  Consider the odd ball and outlying cases -before- finalizing the design, and the process.   Think of the order or operations.

If you already have a unique identifier, use it.

When you use a unique identifier, make sure it’s unique, and not contextual.

Disclaimer:   These processes were told to me by the Connecticut and Massachusetts DMV officials in those timeframes, when I called them.  The systems and policies may have totally changed since then.  Call the state’s DMV for their current policies and procedures.

About these ads

One Response to Cars, License Plates, And Using The Wrong Identifier

  1. bet365 says:

    how are you!This was a really magnificentsuper Topics!
    I come from roma, I was luck to approach your subject in yahoo
    Also I learn a lot in your website really thank your very much i will come daily

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: