#SBPro question
6 messages in this thread
I've recently purchased SuperBase Pro 4 and am attempting to learn how to
use it. I've gone through the tutorial, and am fairly confident with
simple stuff…the rest will come with time.
I do have a question, though.
In the database I am creating, on of the fields is the subject's social
security number. Since no two people are supposed to be issued the same
number, I've indexed the field as unique. Here's my problem. While the
field is unique, it is not required since the SSN won't always be known.
When the number is bypassed, SBPro complains that the entered data is not
unique (if there is more than one subject w/o a SSN). Is there any way to
disable the unique index on null fields? If I enter data, I want the
system to check for duplication, but if I DON'T enter data, I want it to
just go on.
Can this be done?
Chas
This is a problem I have met several times before … SBase treats "null"
fields as containing a blank rather than not containing any data.
The only colution I found to the problem (and I'd be interested if Tim can
come up with a better way) is to have the data field and a calculation
field … if the data field contains data then the calc field is set to the
contents of the data field … if it doesn't, then a would generate a
pseudo reference number which was totally out of the range of the normal
data field.
I don't know the format of US social security numbers, but I will give a
numeric example:
Say you have your SSN numeric field … create another field called SSNix
(SSN index) which is a calculation field as follows:
(SSN = 0) ? SER("filename"): SSN
This will place the value of SSN in SSNix (which is your unique index
field), but if it doesn't exist, it will create a psuedo SSN as a serial
number (which, of course, will be different for every record). There are
many variants on this … you could, for instance, change SSNix into a text
field (converting SSN to a string) and prefixing the non-SSN entries with
"AAA" or whatever.
The proper handling of null entries is an important feature of how a
relational database works … an excellent article in BYTE some time back
explained the formal definition of a relational database, and this included
the proper handling of null entries.
Mike (Whapping from the UK)
Hmmmm, that sounds like a viable workaround. On a side note, the U.S.
social security number format is 123-45-6789 with the first set being a
group assigned to the state where the number was issued; the second set is
the sequence; and the third set is serialized. Thus, my number:
213-02-6334 mean that the 213 was a Maryland number; using the 02 series
(about halfwa through) and number 6,334. Silly, but it works and they are
unique.
I'll try your suggestion.
-Chas
Charles, I had/have exactly the same problem, but with my personal
checkbook program. I also use it to record cash machine withdrawls, in
which case I just want the 'checkNumber' field left blank. No way to test
for the check number to be unique in that case. 🙁
I did solve it once by putting all my entry on a form, and using DML
commands to test the 'uniqueness' of the check number when the user exits
the record. That way the test was under my control. If a duplicate
occurred I just told myself about it.
A real kludge, however. And certainly not 'failsafe' when you really need
to guard the integrity of the DB. Tim's reply sounds interesting, but I
don't know that I want a bunch of garbage data in the field when it
*should* be blank. Oh well.
–jp–seattle–
The thing to note about the definition of primary keys (which I assume is
what you want the SSN to be) is that they must be unique *and* they must
not be NULL. Usually, if you want to violate this rule, then you haven't
well defined your entities in your database. Also, from what I've seen of
Superbase's relational modeling capabilities, you can create most models,
but you have to be very scrupulous in your model (because of the lack of
true NULL support).
In your problem, it sounds like what you have is a list of all people (call
it TableA) and a list of people with SSNs (call it TableB). The primary
key of TableA would be generated by SER() (with a unique index) and would
have a new entry added whenever a new person is added to the database. The
primary key of TableB would be the primary key value from TableA whenever
the person is given an SSN. You would then probably generate a form that
combines the two tables appropriately and you might want to have a LOOKUP
function on TableB's PK to ensure that it exists in TableA.
My problem is that new records are entered every day and it is not known
beforehand who has a SSN and who doesn't. Eventually the record would be
updated and an SSN entered when known. It *must* be an SSN and not just a
serial number for identity (I've got those, too.)
I think I've solved the problem, though. I've changed the index so the SSN
isn't unique, thus allowing multiple unknown SSNs. When the data is input,
it is stored in one database. At the end of the day it is merged into the
main daabase. At that time blank SSN fields are changed to
"Unknown"+InmateNumber, where InmateNumber is a serial number. This
prevents duplicates and makes it easy to search for subjects w/o SSNs for
later correction.
In the main database the SSN field is unique. Duplicate errors on merging
flag existing files for update as opposed to creating a new record.
Thanks for all the help…it is really useful.