Unique Fields - Some Thoughts

Hi -

An old topic Im sure - but one that is important when thinking about
real world systems - in particular invoicing situations.

============================================
	This is an overview of the situation..
============================================
1. one table stores many types of transaction (Quotes,Invoices,
credits etc..)

2. Some of these will require a unique reference/number (Invoices and
Credits for example)

3. One field is set to hold that uniqiue reference (call this TRANSID
for the sake of argument).

4. System must allow the facility for users to enter their own
reference/ number (some people have their own numbering systems based
on their own business rules). The system however must still police the
unique constraint (cane rely on the users descretion)

============================================
	Here is my proposed solution..
============================================
1. Assume The table already has a KEY field  - the value of which is
always unique.

2. Assume there is a field called TRANSID that has the unique
constraint assigned to it. This will store all unique numebrs/
references (for invoices/ quotes etc)

3. When a unique reference number needs to be generated by the system 
- use triggers and stored procedures to Generate this value and store
it in the TRANSID field.

4. When a user has elects to enter their own nunber / refernce into
the TRANSID field - they can do so - and the system will ensure its
unique (because of the unique constraint already defined)

5. When a transaction is created where a unique reference or nunber is
NOT t needed - fill the TRANSID field with the value of the primary
key (that satisfies the Null problem and gives it a value we know to
be unique.)
============================================

Phew!

Anyway, any thoughts on this solution would be much appreciated.

Cheers..

Paul.


OS : Windows XP Service Pack 2
Delphi : 2005 Service Pack 3 Installed
Version 9.0.1935.22056
CPU : AMD 3500+

Paul wrote:

> 5. When a transaction is created where a unique reference or nunber is
> NOT t needed - fill the TRANSID field with the value of the primary
> key (that satisfies the Null problem and gives it a value we know to
> be unique.)

The PK value is unique in the PK field. It is not necessarily unique in
the TRANSID field. A user could have already entered a value in TRANSID
that is identical to the PK of a new record that is being inserted.

-- 

>> 5. When a transaction is created where a unique reference or nunber is
>> NOT t needed - fill the TRANSID field with the value of the primary
>> key (that satisfies the Null problem and gives it a value we know to
>> be unique.)
>
> The PK value is unique in the PK field. It is not necessarily unique in
> the TRANSID field. A user could have already entered a value in TRANSID
> that is identical to the PK of a new record that is being inserted.

Or, a future TRANSID value might crash into a previously used PK value.

Have you considered storing the user value in a separate lookup table? 

Good point.

Had a verbose response - but just lost connection to news servser and
lost it -so here is an abbreviated response; ).

Possible solutions...

1.  Use the old "Select COUNT(TRANSID) from TABLENAME..." and is >
0
then generate an error.

2. This is prefered. Prefix the TRANSID field with a value unlikely to
either generated by the system or the user. Say -1 ?.

Using the second approach - you could present the TRANSID field to the
user as a culcuated field (removing the -1 prefix.)

This is not ideal i know as it has implications with regards
performance.

Clearly - a work in progeess.

Any suggesions ?




On 10 Apr 2008 13:13:23 -0700, "Bill Todd [TeamB]" <no@no.com>
wrote:

>Paul wrote:
>
>> 5. When a transaction is created where a unique reference or nunber is
>> NOT t needed - fill the TRANSID field with the value of the primary
>> key (that satisfies the Null problem and gives it a value we know to
>> be unique.)
>
>The PK value is unique in the PK field. It is not necessarily unique in
>the TRANSID field. A user could have already entered a value in TRANSID
>that is identical to the PK of a new record that is being inserted.

OS : Windows XP Service Pack 2
Delphi : 2005 Service Pack 3 Installed
Version 9.0.1935.22056
CPU : AMD 3500+

The problem is Mike that it has to allow for a single-user situation. 
Thanks for your feedback though.



On Mon, 14 Apr 2008 17:57:19 -0600, "mike cherven"
<no@spam.com>
wrote:

>
>>> 5. When a transaction is created where a unique reference or nunber
is
>>> NOT t needed - fill the TRANSID field with the value of the
primary
>>> key (that satisfies the Null problem and gives it a value we know
to
>>> be unique.)
>>
>> The PK value is unique in the PK field. It is not necessarily unique
in
>> the TRANSID field. A user could have already entered a value in
TRANSID
>> that is identical to the PK of a new record that is being inserted.
>
>Or, a future TRANSID value might crash into a previously used PK value.
>
>Have you considered storing the user value in a separate lookup table? 
>

OS : Windows XP Service Pack 2
Delphi : 2005 Service Pack 3 Installed
Version 9.0.1935.22056
CPU : AMD 3500+