A good way to define an article/item number?

[d94dsj]

I don't know if my question fits properly into the "Define fields" section, but it was the closest I thought of.

I have a discussion with a client on how to define a standard for article/item identifier. (They will not be used as primary keys).

In this particular case it's a company who sells light sources, overhead projectors, and data/video projectors and accessories to these.

I thought that some of you guys might have worked with systems where you deal with a lot of articles/items and might have some good examples?

Some of my thoughts:

It would be nice to have some kind of logical grouping within the identifier, maybe separated with dashes for easy "human" parsing?

For example "X-Y-N-Z", where each letter represents a string of letters or numbers.

X could then be an article group, maybe "LIB" for light bulbs or "OHP" for overhead projectors.

Y could be the manufacturer of the article/item, maybe "OSR" for "Osram" or "LIE" for "Liesegang".

N could be a just a sequential number that is basically unique within that manufacturer's articles/items.

Z would be a "revision" of an item/article. This would mean that if there is an overhead projector called "Super Duper 600" and this get a successor with a small set of updated features, called "Super Duper 600i", Z would for the first projector be 1, and for the other 2.

So, for a projector from the manufacturer "Liesegang" this article identifier could be:

"OHP-LIE-4711-1" or

"OHP-LIE-4711-2" for a similar updated model.

The benefits of having some kind of system as I see it, is that it makes the sales person handling the order recognize the article/item numbers and can find them more quickly in a system with many thousands of articles/items. This identifier will also be displayed on printed order forms and invoices. Of course there will also be support for searching for articles belonging to a certain article group, as well as other criteria, so it's not a replacement for that.

Thanks for any input,

Daniel

[danjacoby]

My only experience of this type is the way I number my invoices (they do have a seemingly esoteric number system, at least until you receive enough of them to "crack the code"). My advice is to create a system that works for the specific need, and it seems to me that you're on the right track with the system you're creating.

One thing I'd add: Have the data entered in separate fields for "X", "Y", etc., and then create a calc field to put the whole thing together. That way, you can use editbale popup lists for each section, cutting down on typos.

[BobWeaver]

If you are going to create a numbering system like this, DO NOT, and I repeat, DO NOT use this as the key field in a relationship. Create another unique identifier field for that purpose. The reason is, that it becomes too easy for the user to decide to change the item number for a product, and then all the relationships break.

Personally, I think it's a bad idea to code too much info into an item number anyway. It just locks you into a lot of extra work when creating them. Besides, that is what the item description field and the manufacturer field and the product type field is for. The product code just becomes redundant.

[d94dsj]

>> Dan

quote:

---

One thing I'd add: Have the data entered in separate fields for "X", "Y", etc., and then create a calc field to put the whole thing together. That way, you can use editbale popup lists for each section, cutting down on typos.

---

Yes, that's a good idea!

>> Bob

quote:

---

If you are going to create a numbering system like this, DO NOT, and I repeat, DO NOT use this as the key field in a relationship

---

I won't, I promise I would never do that (as I wrote it will not be the primary key).

quote:

---

Personally, I think it's a bad idea to code too much info into an item number anyway. It just locks you into a lot of extra work when creating them. Besides, that is what the item description field and the manufacturer field and the product type field is for. The product code just becomes redundant.

---

I disagree. This article/item identifier is what customers will see on their invoices and delivery notes (exept for a general description of the item). So consider the follwing quite realistic example:

A spare part A is shipped to a customer. The item is not the correct one, it does not fit properly.

The customer calls my client, telling them it doesn't fit. Then my client may ask the customer for the item/article identifier on the invoice/packaging slip. If this is reasy to read out, then there's no problem finding it quickly in the database, or even for the support person to, by experience, recognize the item/article identifier and minimizing the time to find the correct part. "Aha! You've got the old one with -1 at the end, you should have the -2 one".

I think it might make it easier for the persons working with the system, as well as for customers to communicate, and remember items.

You are right in that there is some redundancy, but the manufacturer and type part could be filled in automatically based on what the user have chosen in those fields!

Do you see my points?

[BobWeaver]

I had one very bad experience with these type of ID fields many years ago at the office where I worked. They were setting up new employee timesheets, and decided to revamp all the 4 digit work codes into 25 character codes. This would now include 4 characters for a client ID, 3 characters for the particular department that the employee belonged to, and on and on... It was idiotic because it required the employee to enter a lot of stuff that was already in the system that the database program could easily look up. Ever since then, I've had a great dislike of DNA type ID fields (that is a number that has so much info coded into it that you can reconstruct the entire database from that single field value). Having said that, I guess it's not too bad if you don't get carried away with the idea. (i.e., keep it fairly small).

[Keith M. Davie]

danjacoby suggests, "Have the data entered in separate fields for "X", "Y", etc., and then create a calc field to put the whole thing together."

BobWeaver cautions, "...it becomes too easy for the user to decide to change the item number for a product, and then all the relationships break."

If the field being presented to the user is the calc field which danjacoby suggests would that not be a field which is not modifiable? Wouldn't it follow that the user will be unable to change the item number?

[d94dsj]

quote:

---

danjacoby suggests, "Have the data entered in separate fields for "X", "Y", etc., and then create a calc field to put the whole thing together."

BobWeaver cautions, "...it becomes too easy for the user to decide to change the item number for a product, and then all the relationships break."

If the field being presented to the user is the calc field which danjacoby suggests would that not be a field which is not modifiable? Wouldn't it follow that the user will be unable to change the item number?

---

Not if you use drop-down lists to enter parts of the article/item number/identifier. Then it would be modifiable. But you might want to prevent any further modification to any of those fields, which of course can be done.

But no relationships will be based on these numbers anyway, I have a separate primary key for that.

Daniel