-3

I am designing a database with a table to store vehicles and since the vehicle identification number is a 17 digit alphanumeric serial number my idea is to use it as the primary key, with a datatype of char(17).

Numerous other tables will then have the VIN as a foreign key.

A number of queries/searches will run with the VIN number as parameter since it's how we would like to track the vehicles as well as other data related to it.

The VIN number will never change, but I'm unsure if it would cause any serious performance degradation (or other complications I'm not aware of) since some queries will use joins and others not :/

By using the VIN as primary key I do not have to create a unique constraint / additional index - BUT it has to be char(17) a data type other than int for which primary keys are supposedly optimized...

What I'm also not 200% sure of is that every VIN number out there is the same length (very unlikely) but in that case how would using a varchar(17) affect the whole situation... if at all.

Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
George Albertyn
  • 267
  • 4
  • 11
  • **primarily opinion-based** _Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise._ – emerson.marini Jan 20 '15 at 10:36
  • Mostly some integer type is preferred for primary key or if you want VIN number as primary key make as compositeprimary key or put it as unique – koushik veldanda Jan 20 '15 at 10:42
  • What about data errors, for example a) you will have two vehicels with same VIN b) someone needs to change VIN c) you have vehicle without VIN? These fall under other complications. – Arvo Jan 20 '15 at 10:45
  • @Arvo VIN numbers will never change and are universally unique – George Albertyn Jan 20 '15 at 10:49
  • @MelanciaUK - care to have a go at an answer ? – George Albertyn Jan 20 '15 at 10:51
  • `char(17)` is **good** because it's a **fixed-length** column (that would be a downside for `varchar(17)`), but it's **bad** because it's over four times larger than an `int` (4 byte), so the overhead if you have additional non-clustered indices (which always include the clustering key) could be sbustantial (if you have lots of data rows, and several nonclustered indices). So *if* you decide to stick with the VIN - make it `char(17)` rather than `varchar(17)` for sure! – marc_s Jan 20 '15 at 11:10
  • @marc_s Thanks dude, that's just what I needed to know ! – George Albertyn Jan 20 '15 at 11:20
  • @Terminator - I have seen any kind of data errors in any kind of databases; even my daughter has had two supposedly unique and correct identity codes. What I wanted to say that using natural unique keys (like VIN) has some downsides while working with real, possibly erroneus data. – Arvo Jan 20 '15 at 12:53
  • @Avro Sir I appologise, you are 100% correct - a further peek at company data revealed as much, or as they say "assumption is the mother of all screw-ups" Basically I learned that VIN numbers should never be used as a Primary key irrespective of the underlying data structures ... – George Albertyn Jan 21 '15 at 08:37
  • A few years later I'll add this: Do **not** use the VIN as the primary key because it is clustered. The data length is irrelevant. The problem is the clustering because that is the *physical* order of the records on disk. Every time you add or change (if it was entered wrong) you're going to be causing a physical reordering of the entire table. Add it as just a regular column, and then have a standard `int` Id column with auto increment which will always be added to the end of the table during additions. – Gup3rSuR4c Sep 04 '19 at 19:52

2 Answers2

0

In my opinion, regarding performance, it indeed is not a good idea. It very much depends how many cars you will store in the database though. On the other hand, if your applications and queries use the VIN as parameter then it is the best option as the column is indexed and must be unique.

hope this helps

ps: akward seeing other people's suggestions on this topic!

DoubleJ
  • 26
  • 3
0

Just a personal opinion..

I always use int as a primary key. Primary key is in most cases always a clustered index. It's 4 bytes vs. 17 bytes and you can always put a non-clustered index on your VIN column. Keep things simple and clear. It's just my opinion though.

Ivan Sivak
  • 7,178
  • 3
  • 36
  • 42