Multi-part composite serial number for items

[Gabriel G]

Greetings! This is my first post on the forum, and I'm also new to FM.

I have a situation where I have a few ideas, but I was hoping to get better advice and direction from others more experienced with this at the start.

Preface:
The numbering scheme in use may be crazy, but it is established. I am seeking the better way(s) to handle what's in place.

Situation:
I have an ITEM table, and the items have a unique identifier key which are part of a serial number. A LOG table records each time that one of the items is used. This "serial number" is kept on the item's logsheet.

Example:
In August of 2014 an item with the ID 46 is used. The serial number would be something like (without underscores): 46_14_08_3456.

The next time that item is used, let's say February of 2015, the serial number would be 46_15_02_3457.

So the last part is a number which is auto-incremented, but each item has a different (it's own) sequence.

Considerations:
1) Do away with the single LOG table and create a table based on each item and populate a field using auto-incrementing serial each time it is used. Use another field in this table to store the composite number.

This seems like it would be the easiest, but it would have a lot of overhead with extra tables. There are only about 15 different items for now... this number *might* increase once every 3-5 years at most if ever.

2) Use a field in the product's table to keep the sequence number that was last used for that item and a script for incrementing. Then the composite number could be calculated and stored in the LOG table.

3) Get better ideas, articles, etc. from more experienced users...

Conclusions:
I'm leaning towards option two for now...

Thanks, GG

Edited August 28, 2015 by Gabriel G
Removed the fixed line breaks

[comment]

Do you really need the numerator part (the last four digits) to be consecutive per item? This is not easy to implement reliably (without duplicates). It would be much simpler to increment the numerator per record, e.g.:

46_14_08_3456
65_14_09_3457
88_14_12_3458
51_15_01_3459
46_15_02_3460

 

[Gabriel G]

Hello comment, thank you for your response and all the help you provide to everyone on these forums. I have learned quite a bit from your contributions here!

Yes, I agree with you. It would be much easier to increment on a single table rather than each product. However, since it is an old system, I am striving just to bring it away from its current paper state. This is mostly to help maintain the history of previous entries as well.

I would also like to refocus my previous question a bit. I am more interested in how you, or anyone else, would handle structuring this unusual numbering scheme the way it is? If you were given this challenge, would you choose either of my considerations- or would you do something even different than what I have listed?

Thanks, GG

[comment]

how you, or anyone else, would handle structuring this unusual numbering scheme the way it is?

I don't see anything "unusual" in this numbering scheme. This question comes up quite often: you have a parent-child relationship, and you want to number the children of each parent with its own series (prepending the other digits representing the parent ID and the year/month is trivial).

Usually, the idea is to take the maximum existing value in the series (available either from the point-of-view of the parent, or through a self-join relationship of the child table based on matching parent ID), and add 1 to that. Which will work 99.9% of the time - until two users create records roughly at the same time and end up with duplicate numbers. So the first "challenge" is to prevent such situation - and it could be accomplished by (a) forcing the users to create new child records by script only, and (b) having the script lock the parent record first and, failing to do that, abort the task.

However, the "challenge" doesn't end there: you must also consider what will happen if user decides that the parent selected initially is not the right one and needs to be changed to another. Instead of dealing with this, let me also point out that if any record is deleted, you will have a gap in the series anyway. So the real question I would ask here is this: is the goal worth the means required to achieve it?

[Gabriel G]

Great!

This is exactly what I was looking for- some insight to the pros and cons of going down this road with a nice scenario of how to address it. It also stresses to me, the importance of insisting on doing it this way up front. I was not completely convinced of this until now.

Thank you kind sir!