Confused! Table Design

[kdlc]

I have a Table called CERTIFICATES:
* CertificateID
* StudentID
* CertificateNumber
* DateIssued

THE PROBLEM:
In some cases the customer may ask for another certificate because he lost the first one ... but the tricky part is that my client will issue a new certificate (not a copy) and he wants to know which certificate is being “duplicate”.

EXAMPLE:

* Fist Certificate:
Certificate ID: 1
StudentID: 100
Certificate Number: AA123456
Date Issued: 10/02/2004

* New Certificate:
Certificate ID: 20
StudentID: 100
Certificate Number: AA123499
Date Issued: 11/02/2004
Duplicate (Certificate ID): 1

So … I would like to know which would be the best way to do this … any suggestions??


Thanks in advance

[mendhak]

You could add a DuplicateID column to the table, but then you need to know whether

The original certificate's info is still valid, or if it will be deleted
Whether from the info for Original Certificate will tell you that Certificate 20 is it's duplicate.

[szlamany]

Where is your customer information in any of these tables? How do you know what customer has what certificate in the first place?

[kdlc]

I didn't put the whole thing, because i want to keep it simple just to explain the issue ... but, yes i have the customer information in another table.

[szlamany]

So, I should assume that the customer and the student are one in the same - right?

Here is something we do of a similar nature - dealing with REPLACEMENT CHECKS. These types of checks replace existing checks that were lost or destroyed - so they do not count as a new check issue - simply replacing the stock that the original check was issued on.

CHECK FILE:
CheckNum
CheckDate
PayeeId
CheckSource (will be P for original PAID check, R for the replacement check)
ReconDate
Status (I for Issued, V for Voided, S for Stopped, R for Replaced)
Replaced by (will be blank at first)

When a check becomes replaced, we flag the original with a STATUS of R for REPLACED and fill in the REPLACED BY value with the check number that replaces this dead check. We create a brand new row in the CHECK FILE for the REPLACEMENT check. That new row gets "R" in the CHECK SOURCE so we know where it came from.

We happen to have PAYROLL/VENDOR history files that refer to the CHECK NUMBER. We change all references in these files to the new "replacement" check number, since the old number is really a non-issue at this point.

[erickwidya]

if ur Certificates table is just had a few field..then u may consider mendhak's..
if u had a lot of field..then u should consider to save it at other table for CertificateID that being Duplicated..