How to design/manage tables for 'exception' data??

[tlyczko]

Hello,

I work in a non-profit agency, and one database covers clients, staff,
and locations (we have many locations, programs, facilities, etc.)

The problem is similar in all three categories: how to structure the
table or field design to accommodate 'exceptions' or 'nuances' in the
data??

For example, for staff, normally it would be last name, first name,
such as Jane Doe, John Smith, etc. Same for clients.

However, for various reasons, sometimes the 'staff' is something like
'John Doe Family', or 'Physician,' or 'Company Name'.

Obviously those don't fit into something like a 'staff name.' We have
similar problems with client data, where the client's name may not be
known (could use 'Unknown') or there is no client name available at all
but the client name is a required field, so we type something like
'Facility, Reportable.'

What is the best way in terms of table/field design to handle these
recurring exceptions/nuances in the data??

Should I allow only specific types of phrases (my solution so far),
create new/additional fields in the table to hold entries like this
(makes queries way more complicated!), create new tables (makes for a
lot of union queries etc.!), what??

Should the data entry form be modified to have the user do something
different when one of these data entry cases occurs?? (This is not
ideal because the data entry is already complicated enough, smile!)

How have you solved similar problems??

Can anyone point me to a website, book, anything, that discusses these
kind of table/field design problem?? (Most db design books I've seen
don't talk about stuff like this.)

Thank you, Tom

 

[Barry Gilbert]

Tom,

Is there any reason you couldn't keep this simple? Make it a single field,
call it something generic rather than name, make it nullable, and let them
put in whatever they want. Is there some reason this wouldn't work?

Barry

 

[mnature]

Let's say you often have random bits of information on patients:

tbl_Patients
PatientID (PK)

tbl_PatientInformation
PatientInformationID (PK)
PatientID (FK)
PatientInformationLabel
PatientInformationText

Use PatientInformationLabel to describe what the information is, and use
PatientInformationText for the actual information. Then put this table into
your form as a subform, datasheet view, to show as a list of all of this
random information for the patient. Then, if you don't know the client name,
you put "See Note" as the name, and then add a note that says you didn't know
the name, and perhaps why you didn't know the name. That would work for any
of your normal text fields.

 

[tlyczko]

Hi Barry,

Thanks for writing.

We need last, first as separate fields plus a calculated field which
concatenates them as Last, First.

If I do something like you suggest, how do I make it EASY for the users
to know what to do when such that they can use this sort of thing
automatically?? That is, how to know which field(s) to use or not??
Also doing this would require not entering anything for last or
first...

"Putting in whatever they want" makes that field hard to search because
they could enter the same thing several different ways. Databases like
"relatively uniform" data.

I might could do something like "If it's not a person's name, type
something HERE" and make sure one or the other is not null...I'm not
sure...the staff and clients tables are *separate* tables in various
one-to-many relationships and this method might throw things off in
that regard...

Thank you again for replying, hopefully your idea and anything others
suggest will get us onto a proper/reasonable solution.

Thank you, Tom

 

[tlyczko]

Hello mnature

Thanks for replying, maybe this will be the way to do it...One
difference is that these three tables are to be used in different parts
of a larger agency-wide database, of which only certain parts have
these 'exceptions,' so your idea may work.

Usually these client, staff, location tables are tied to another table,
so the problem becomes how do the 'main' tables on the 'one' side of
the one-to-many relationship know about this extra information?? I
guess that would be just a 'logistical' sort of problem, though, once
the main concept and method like you suggest (or something similar) is
implemented.

Thank you, I hope more replies come in, eventually everything will jell
and things will work themselves out.

Thank you, Tom