MySQL: Custom Auto-Generated Key (AUTO_INCREMENT / multiple-column index)

MySQL is used as database.

As part of inventory system, I need generate stock number, that is unique identifier for asset. Client has requirement that this number is not just autoincremented integer, but follows pattern: @BusinessUnit@YYYY@Number

, where

@BusinessUnit = string representing business unit; @YYYY = current year; @Number=n = Unique number for this BusinessUnit & This Year: n-th item asset registered in system this year and ready for sale.

For example,lets say we have we have various users entering assets for 2 Business Units = {NY, CA}. Stock numbers would be expected as follows: NY201100001 NY201100002 CA201100001 NY201100003 CA201100002

So far based on manuals avail开发者_StackOverflow社区able, first thought would be using AUTO_INCREMENT and have separate table for each business unit with trigger on insert, where after insert from numberic auto-generated id update inventory table containing all business unit assets with generated id with concatenated business unit and year in front. Also as first thing in new year reset AUTO_INCREMENT = 0 - alter all tables.

Is there any better way and ability avoid need create multiple tables, can I just create somehow multi-column index? If yes, could you please provide appropriate table definition sample?

---

DISREGARD THE CLIENT (partially).

Create your tables with auto-increment "InventoryID" for purposes of guaranteeing simple, disconnected context to anything else on the record. Create a SECOND column that is their "InventoryIDUnit" which can be a "candidate" key matching the business rules you are responsible for keeping. When a search is done on the "InventoryIDUnit" (specifically formatted field), internally and through the rest of your system, you'll have the INTERNAL numeric for joining the rest of the way down through the system.

Think of a customer order system. If you had it based on a person's name, how many "Jane Doe" versions out there... are they the same or not... Internally, customers have an ID and all orders go back to that common ID. Then, Jane gets married and is now "Jane Smith"... Are you going to go back through the data and rename all the entries to the new name??? That's the whole purpose of a surrogate key.