Part numbers as keys

[barkingsheltie]

I have a table that contains part numbers for contacts, connectors from hundreds of manufacturers. Via a line items table (called cross-ref), we use a relationship to match tooling and accessories we manufacture. Unfortunately, there is no consistent nomenclature scheme for these, eg, Company A has A445-2cX13.0-2, and another NSN-5120-00-23-5441 and so forth.

Initially, the primary keys in the various tables used record id's, of the type that were auto-enter.

A few design challenges got me to wondering if things might be easier if instead of record id as key, I made use of the part numbers in both tables as keys.

I realize the main argument against is the ability to ensure uniqueness. The 10k numbers we currently have (surveyed over a period of twenty years), all are unique - as well as our own product part numbers.

Appreciate input, experience and or slap downs here!

[T-Square]

I can't imagine what design challenges you would encounter with an established serial number system that would cause you to re-think them, and so I can't think why you would scrap that.

Frankly, I find that so-called mnemonic ID schemes make my head hurt. Furthermore, unless you can ***absolutely, without fail, forever*** guarantee that your part numbers won't overlap, I wouldn't go that route. It really sucks when you accidentally crosslink two entries, and you can't just delete the second entry because your cascading delete will take out the *other* entry's children...

If you're desperately seeking to use those part numbers, you always could just use them in display calculations (with the real linking done behind scenes with your numeric IDs). That way, the user gets the thrill of seeing part number IDs!

David

[aldipalo]

I totally agree. I have such a system and it is an accident waiting to happen. Stay with the tried and true method. I wish I had when I first developed my DB. I would be sleeping much better. Now I spend my spare time thinking about ways to reverse it. The problem is I have 22,000+ companies and 18,000+ contacts. It's a massive job and open to all kinds of problems.

[barkingsheltie]

Thanks!

By design challenges, I should rephrase - as I plan, continue to extend and implement this database, I wondered whether there would be any advantage(s) of using pn's as primary keys down the road.

The primary challenge faced right now, is coming up with a search algorithm/process that can account for misspellings, partial matches, flawed input from customer, etc.

Im more versed in command-line c programming, and I know I need to get away from methods I might use there, and learn how to do this the *filemaker* way.

In any case, I appreciate the advice & experience regarding proper key usage, and I will in fact heed both of your suggestions.

shelley