Subject Serial numbers in a multi-client application
Author First L

Normally I develop an app for a single user and manage serial #'s (orders, invoices etc..) using a Sequence (aka Generator). I am rewriting an app for the Web and will have one database for all data. My primary key is now sysClientID, Recid for every table to allow users to totally customize their account. At issue is, do I declare a set of generators for each client for the visible serial #'s (OrderID, Invoice, JobID etc..) or is there a better way to assign the numbers.

Each subscriber will on average have 1 to 5 users using the app and I expect a range of 150 to 400 subscribers once build out is complete. Is this a good use for a stored procedure/function? Does anyone have any examples of this?

One requirement I do have is that I need keep a log of each serial # assigned, as it has been requested by auditors in the past to make sure every invoice is accounted for. I do this in my code already.

Thanks for reading this and I appreciate any suggestions.

- Lou