Avoid repeat without primary key

  • Thread starter Thread starter Ramesh
  • Start date Start date
R

Ramesh

Hi,

I have a table with Cert No, Cert Name and Reg No.

I have made Cert Name and Reg No the primary key so that the same
Certificate is not issued to the same student more than once.

But problem is some Cert Nos. are cancelled for misprinting or misspelling,
etc. How can I keep record of the cancelled certificates?

Thanks for any help.

Ramesh
 
Add a third field, CertCancelled, to the table and to the index.

This could be a Yes/No field, or sometimes I find it useful to use a
Date/Time field for this kind of thing, where a Null value in the Date/Time
field means no, the record has not been cancelled, while a non-Null value
means yes, the record has been cancelled. The advantage of using the
Date/Time field is that I can also do things like find all records that were
cancelled within a specified date range.
 
Thanks very Brendan for your response.

My problem is that I cannot leave the Reg No. and Cert Name blank without a
unique combination since they are the primary key.

Any solution for that?

Thanks for any help.

Ramesh
 
I don't understand why you would want to leave them blank?

Come to think of it, though, I do see a problem with part of my earlier
suggestion. The Date/Time field would not work as part of the primary key,
because it could not be Null. A Yes/No field would still work, though.
 
Thanks again Brendon.

The reason I would need to leave the fields Reg No and Cert Name blank is
because I have a requirement that only one record can contain a unique
combination of Reg No. and Cert Name. Like a student can get a particular
certificate only once. That s why I have made these two fields the primary
key.

If I enter a particular dummy entry into these fields, then I cannot have
the same duplicate entry in another record.

Hope I have been clearer this time.

Thanks again.

Ramesh
 
That's why I suggested adding a third field to both the table and the index
(by which I mean the primary key). If you add a Yes/No field, the table
could now include two records with the same Reg No. and Cert Name, provided
that one of those records had a value of True in the Yes/No field and the
other had a value of False. But I do see a difficulty there, in that it is
not impossible for the same certificate to be cancelled more than once.

The fundamental problem, as I see it, is that the database design says that
the same certificate can not be issued to the same student more than once,
but that is not the reality of the situation being modelled. The same
certificate *can* be issued to the same student more than once, provided all
previous instances of that certificate issued to that student have been
cancelled.

Off-hand, I can think of two possible solutions.

One would be to add the 'CertCancelled', Yes/No field that I suggested
earlier, but not to make it part of the primary key. The primary key might,
perhaps, be RegNo, CertName, and DateTimeIssued. This design would not
prevent the same certificate being issued to the same student more than
once, instead, code in the front-end application would handle that, checking
that no record existed with the same RegNo, CertName, and a value of False
in the CertCancelled field before allowing the addition of the new record.
The downside of this approach, of course, is that the validation would take
place at the application level rather than the database level.

The second possible solution would involve an additional table, say
'CertIssues'. The certificate can be issued as many times as necessary to
the same student, and each issue of a certificate is recorded in the
'CertIssues' table, but it remains the *same* certificate - the certificate
has been *issued* many times, but it has been *awarded* only once, if you
see what I mean. This is obviously a more complex design, but it more
closely models the real-world situation. The primary key of your existing
table would remain as it is, RegNo and CertName, as this table would contain
only one record for each certificate issued to that student. The multiple
'issuings' would be recorded instead in the 'CertIssues' table, with a
primary key on RegNo, CertName, and DateTimeIssued, and a foreign key on
RegNo and CertName.

Even with the second design, though, I can't think of a way of imposing the
'only one record not cancelled' rule at the database level without triggers.
(I'm assuming we're dealing with a JET, MDB database here, and not with an
Access front-end to a server database such as SQL Server, so triggers are
not available.) Unless someone else can suggest a way to do this at the
database level, I think you may need to depend on application-level code for
that.
 
Wow Brendan, I really appreciate your efforts in supporting me with the
problem.

Like you said the CertIssues table seems to be the closest solution. But
since I am not using a front end application, I guess that I ll go for the
original solution which I didnt understand the first time. Adding a Yes/No
field to the key. (I didnt know more than two fields could be the key).
Though the one time cancellation is a limitation, right now looks like that
will suffice. I dont normally expect a more-than-once cancellation in my
case.

Thanks a million again.

Warm regards
Ramesh
 
Back
Top