Unique Number Run...

demon8991 · 9 Jan 2009 at 18:23

Right I need to pick your brains on a mathematical dilemma!!

I work for an investment bank so am dealing with numbers all the time, I need to generate a unique number run that can never have a duplicate but there are a lot of variables and that is what I am struggling to get my head round. The number can be big or small but it must be unique to any other number. References for deals get reset at the beginning of every year and that is where the numbers start to duplicate.

The variables are

Client: x,y,z etc
Counterparty: a,b,c (a counterparty can be paired with any client)
Value Date: (a date in the calendar year)
Year: so 09 would give a constant of +9 (possibility but I don’t think it would be essential)
Reference number 1,2,3….600,601 etc

Any help would be greatly apreciated

Berserker · 9 Jan 2009 at 18:33

Sounds like the reference number would be unique within a given year, in which case <year><reference> should be sufficient.

To avoid confusion (and the risk things might break next year), pad out the reference number with zeros - e.g. 2009000427 = Year 2009, reference 427. That would allow up to reference 999,999. If you have even the slightest doubt that will be enough references, add more zeroes now.

titchard · 9 Jan 2009 at 18:35

Berserker has it pretty much - maybe do <year><day><ref> for added uniqueness?

Rich

Berserker · 9 Jan 2009 at 18:37

<year><month><day><ref> or <year><julian-day><ref> would be better than <year><day><ref>.

You can also shorten <year> to two digits if you believe your bank won't exist in 2099, but if they do still exist then, you'll have given them a Y2.1K problem.

demon8991 · 9 Jan 2009 at 20:24

Thanks for the help guys.

Just been thinking i dont know if this would change anything but if the number runs had to be unique for each client with counterparty would this confuse matters?

Ie

A with X
B with X
C with X

A with Y
B with Y
C with Y

A with Z
B with Z
C with Z

Zogger · 9 Jan 2009 at 20:44

I guess you mean each counterparty can use the same id as another counterparty? I'm guessing something like a client order id eh?

Difficult to say without more details of what you're doing but in our databases we normally have a separate table which maps from client / cpty / order id to an internal reference id which is a separate number alltogether. Or you could assign a number to each cpty and client and include it with the date, e.g if client A was 0041 and counterparty X was 0003 you could have 200900410003000123, although that's getting pretty long with all that zero padding

Berserker · 9 Jan 2009 at 23:24

demon8991 said:
Just been thinking i dont know if this would change anything but if the number runs had to be unique for each client with counterparty would this confuse matters?

You would need to design a database schema that defines the record structure you intend to store and exactly what it is you need a key for, and then pick the minimum number of fields required to make that key unique.

Are you tracking references, or clients, or counterparties, or something else entirely that you haven't mentioned?

In your above example, what happens if the same client and counterparty enter into two transactions, with two references. Is that possible in your specific situation, and would that count as two records in your database table, or just one?

There is no shortcut to creating a good database schema. Do it once, do it right. I've had to 'fix' bad database design before, and it's not pretty.

demon8991 · 10 Jan 2009 at 17:42

Right basically we are a commodities house of sorts and i need to allocate each deal a unique number to the commodity being used ie 40963 PB (PB being lead) but the number has to be completely unique across all clients and counterparties so that our client can say '40963' refers to that lot of metal that they have purchased from us.

Berserker · 10 Jan 2009 at 21:56

So, are the existing references not unique then (within a given year, or month, or day)?

Do you not have some sort of database table that records deals? If you do, all you need is a big enough autonumber field in that table and the database will take care of it. If not, then use the technique I suggested - write down every column in the relevant database table(s) and find the least number that make sense and give you a unique key.

PS - it won't be client + counterparty. I suspect you could have more than one deal between the two in which case that's not unique.

OSB · 10 Jan 2009 at 22:52

Just have a string of numbers going <date> <client id> <counter party id> <order ref> no?

So eg 20090110-00034-00104-00001

So every date is unique and my set would allow 99999 clients, 99999 counterparties and 99999 transactions between each client and counter party EACH DAY!

Berserker · 10 Jan 2009 at 22:55

You don't want the numbers getting too long if you're expecting the client to quote them back to you. The longer a number gets, the higher the risk of error.

OSB · 10 Jan 2009 at 23:01

Berserker said:
You don't want the numbers getting too long if you're expecting the client to quote them back to you. The longer a number gets, the higher the risk of error.

True, dont' know how many clients / counterclients he has and i doubt there would ever be 99999 transactions between a pair in a day. Just depends on what info he wants to store and what capacity he needs.

mangobreeder · 10 Jan 2009 at 23:05

<Letter> <Letter> <Number> <Number> <Number> <Number> <Number> <Number> <Number>

e.g.

DF1234567

when you reach DF9999999 filp to DG0000001

that way it easy to quote and has 6,500,000,000 unique entires

i doubt your bank will do that many deals in a decade

i work for a investmetn bank and thats what we use.

gareth.e · 10 Jan 2009 at 23:17

why not just use the reference number? :s

Berserker · 10 Jan 2009 at 23:20

mangobreeder said:
6,500,000,000 unique entires

[pedant]6,760,000,000[/pedant]

However, if you go with this, it's worth thinking about what letters you use. I'd recommend considering the omission of B, O, and possibly I and Z, for example. That'll reduce the unique combinations a bit, but also reduce the risk of human error.

mangobreeder · 10 Jan 2009 at 23:29

Berserker said:
[pedant]6,760,000,000[/pedant]

I did it not allowing for repititions eg, no AA BB

given if you did allow repititions then you would be correct,

To be honest, human error will not be much of a factor,

Given that everything will be transmitted electronically

only time humans will get in contact will be over the phone, when quoting a d-ref, and that error will get pick up every quickly

demon8991 · 11 Jan 2009 at 12:04

Thanks for all you help guys this has been a really good help for me i had so many ideas in my head about this but didnt actually know whether what i was thinking would be a unique run.