June 4, 2015 at 1:45 pm
Just be careful with using a VIN as a primary key. I have been told that the algorithm for validating a VIN has changed and that all VINs are unique, this wasn't always true. I don't know what year this changed but at one point in time the VIN was only guaranteed to be unique over a thirty year time span.
Just to be safe I would include an alternate key that can also uniquely identify each row.
June 4, 2015 at 1:53 pm
I think you're getting lost in the verbiage/usage of PO Number
Our customer issues a PO Number (their purchase order number) to the MFG
One unique PO Number with X number of ModelNum requested.
PONUM: 12345-BAR 5 units of ModelNum 23454
MFG uses that PO Number to fulfill the order with a X number of vehicles (VIN) Unique.
We, at the Dealer end, get the Vin with the same corresponding Customer PO Number.
VIN: VINNUMBER11111111 PONUM: 12345-BAR ModelNum 23454
VIN: VINNUMBER11111112 PONUM: 12345-BAR ModelNum 23454
VIN: VINNUMBER11111113 PONUM: 12345-BAR ModelNum 23454
VIN: VINNUMBER11111114 PONUM: 12345-BAR ModelNum 23454
VIN: VINNUMBER11111115 PONUM: 12345-BAR ModelNum 23454
1 order to many Vins, no dupes
Vin will never have more than one PO
PO will just about always have more than 1 VIN, sometimes hundreds.
June 4, 2015 at 1:57 pm
mwilliams4nc (6/4/2015)
This concerns me:...
so we are adding 2500+ vins every day and they are definitely all over the map in sequencing
Would VinID / OrderID be better?
If VinID and OrderID are identity columns, then they should probably be both the clustering key and primary key. Take time to review the following:
An excellent podcast by Kimberly Tripp covering the topic of clustered indexes, surrogate keys, and they're role data modeling from a performance perspective.
More considerations for the clustering key – the clustered index debate continues!
By: Kimberly Tripp
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 4, 2015 at 2:20 pm
It's my understanding that modern VINs are indeed unique. The year byte in the VIN code is reused every thirty years, but the 17-digit combination will still be unique.
Btw, for your own sake with SQL performance, forget the horrible myth that identity is a "default clustering key", because there is no such thing. The clustering key should always be carefully chosen based on that specific table's usage and requirements, not on a nursery-rhyme-like saying about keys.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 5, 2015 at 1:16 pm
OK I see the advantages of a numerical ID for both my Vins table and my Orders tables.
So is it like this then?
tblVins
-------
VinID -- numeric -- Primary Key
Vin -- 17 digits AlphaNumeric -- Unique
PoNum -- 15 digit AlphaNumeric
Other fields -- many other fields regarding that particular VIN
tblOrders
-------
OrdID -- numeric -- Primary Key
PoNum -- 15 digit AlphaNumeric -- Unique
Other fields -- many other fields regarding that particular PO
Foreign Key = 1 OdrID to Many VinID
Foreign Key = 1 PoNum to Many VIN
June 5, 2015 at 1:34 pm
mwilliams4nc (6/5/2015)
OK I see the advantages of a numerical ID for both my Vins table and my Orders tables.So is it like this then?
tblVins
-------
VinID -- numeric -- Primary Key
Vin -- 17 digits AlphaNumeric -- Unique
PoNum -- 15 digit AlphaNumeric
Other fields -- many other fields regarding that particular VIN
tblOrders
-------
OrdID -- numeric -- Primary Key
PoNum -- 15 digit AlphaNumeric -- Unique
Other fields -- many other fields regarding that particular PO
Foreign Key = 1 OdrID to Many VinID
Foreign Key = 1 PoNum to Many VIN
I'd suggest Int datatype not Numeric.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 5, 2015 at 2:40 pm
mwilliams4nc (6/5/2015)
OK I see the advantages of a numerical ID for both my Vins table and my Orders tables.So is it like this then?
tblVins
-------
VinID -- numeric -- Primary Key
Vin -- 17 digits AlphaNumeric -- Unique
PoNum -- 15 digit AlphaNumeric
Other fields -- many other fields regarding that particular VIN
tblOrders
-------
OrdID -- numeric -- Primary Key
PoNum -- 15 digit AlphaNumeric -- Unique
Other fields -- many other fields regarding that particular PO
Foreign Key = 1 OdrID to Many VinID
Foreign Key = 1 PoNum to Many VIN
VIN numbers are alphanumeric values. Should use CHAR(17).
June 5, 2015 at 3:26 pm
Lynn Pettis (6/4/2015)
Just be careful with using a VIN as a primary key. I have been told that the algorithm for validating a VIN has changed and that all VINs are unique, this wasn't always true. I don't know what year this changed but at one point in time the VIN was only guaranteed to be unique over a thirty year time span.Just to be safe I would include an alternate key that can also uniquely identify each row.
Prior to 1980 they were descriptive but not unique (i.e they could point to make and model, but not necessarily to a specific car.) In short different companies had different standards prior to 1980, and uniqueness wasn't required.
Also - the 17 character length wasn't always in effect. If you deal in collector/vintage/"power" vehicles, VIN's may not necessarily be guaranteed or consistent (i.e. a true "kit" car may not have a vin if it was never inspected to be used on the road).
Edit: incorrect adjective used - corrected.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 8, 2015 at 7:25 am
I can see a user looking up a [single] automobile using VIN, so it needs to be included, not null, and indexed; maybe even uniquely indexed. However, just like FirstName + LastName or SSN on a Customers table, I'm not seeing it as a particularly useful clustering key. To support aggregate multi-table join type reporting for the Sales or BI department, it needs to be clustered and foreign keyed on a sequential integer based surrogate key.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 9 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply