Combining ID numbers?

[RSW]

Hello,

I maintain an Access database at my workplace, and have been asked to bring in information from other company locations. Unfortunately, there are conflicts; e.g. part number 12345 might be something totally different at another facility than it is here. My first instinct was pretty obviously to add a Location column and all queries would include that as well as the ID. However, someone else is telling me we should build metadata into a new part numbering convention. An example:

PlantA.12345 (all one field) would be a totally different part than
PlantB.12345

He says it would be very beneficial to ensure that, seeing only a part number, you would know where it was from. I understand his point, but it just seems like strange database design.

Has anyone done something like this, or considered it??

Thanks in advance.

 

[pbaldy]

I've not created a parts system, but personally I'd keep them separate. You can always join them together on forms/reports, and view them as he's suggested. Best of both worlds type of thing.

 

[RoyVidar]

I agree with pbaldy.

In my book, this comes prior to normalization - ensure each attribute (field) is atomic - i e, you should not be able to read more than one information from it. In this case, you can read both the original partnumber, and the location from which it originated.

Imagine also that you later on is asked about which products originates from which location...

Keep a separate column, and join/concatenate them for display purposes.

If this is also the primary key of the table, then I'd probably make it a composite primary key consisting of both fields (there will always be some who insist on adding a surrogate field (autonumber) to the table for this purpose, though).

 

[The_Doc_Man]

I'm with Roy and PBaldy.

You are inviting trouble with a capital T and that rhymes with C and that stands for CONFUSION. (Right here in River City.)

If there is a reasonable way to completely redo your numbering scheme down the road, DO IT. Without hesitation. The issue, to be blunt, is that you are using baling wire and spit to stick things together that really don't go together. Those who have seen enough of my posts know that I'm a pragmatist. That is evidenced by the fact that I didn't say "Renumber NOW." But if there is even a miniscule opening SOON, take it.

The issue I see is that it is no longer enough to know the part number, it is necessary to also know the location in order to identify a part. You might be able to bang these items together, true, but as time passes you will hate yourself more and more for not renumbering everything IMMEDIATELY. In which case you can make the "old" part number and the location code as ordinary fields (possibly still indexed for "old" part number).

What is the problem down the road? So you have a part numbered 12345 and it doesn't have a location sticker on it and someone brings it back to another store for a refund. And the part number is wrong.

Now how do you account for it? You are faced with a nightmare long-term.

I could probably contrive a few more examples, but the point is that when an identifying number isn't enough to identify something... do you see it illogic of the situation?

 

[RSW]

Thanks Gentlemen,

Let me make sure I understand you correctly.

Let's say I have five locations, and let's say an example number from each looks like this:

1. 12345
2. 12345 (same convention)
3. ABC123 (different convention)
4. ABC1234 (different convention)
5. 23456 (similar convention, but no duplicates with the first two yet)

If I understand correctly, your recommendation (which you all seem to agree upon) is bring everything in as-is but include the location code in every query...

...but then renumber everything into a common system when possible, not reusing numbers between any locations.

Is this correct?

Thanks for your help so far.

 

[KKilfoil]

It may be easier to keep your former ID and location fields for 'historical reference', and generate a NEW PK field (such as an autonumber) to use from now on.

This way, you have simple, efficient, one-field joins and can still go back look at old paperwork, since your [OldPartNum] value is still available.

 

[The_Doc_Man]

If I understand correctly, your recommendation (which you all seem to agree upon) is bring everything in as-is but include the location code in every query...

...but then renumber everything into a common system when possible, not reusing numbers between any locations.

Is this correct?

Not quite. You keep the location code but not for EVERY query.

Unify your numbering system. Keep the old numbers for reference. Keep the location code so you can do queries given the old numbers. But using a new numbering system, don't EVER let the location code be part of the identifying key. Using the new system, that single number is all you need to find the part. And everyone in your system from any location is then guaranteed to find the SAME part. But you have to at least keep a translation table somewhere that tells you, if you have code XYZ123IJK and it came from Hoople, North Dakota, then it is a left-handed dumiflanger. But ... its NEW part number is 1143221. (Or something like that.)