updating personal names; parent vs child table?

[dlazenby]

This must be a common issue.
My medical practice db obviously revolves around patients.
A core table is the patient table (pt_t). Initially, I thought the fields would be name (first, middle, last, suffix, etc), date of birth, social security number, address, city, state, phone.
I then thought that I would trim the patient table by putting those things that change (address, phone) to a demographics table that would be a child table in the set. (patient table would be parent table). This would be updated with each visit.
I then started thinking, well, people change their names all the time (marriage/divorce/personal reasons). So I thought maybe, I should put that also in a child table. However, that would essentially leave me with just 3 fields: pt_id (key/unique field), ss#, and DOB. Also, occasionally, people don't have a ss# (e.g., illegal aliens, young children).
The alternative to having a child table would be that the patient table would be editable and with each new office visit/interaction the data for those fields could be "updated". However, I assume that would mean a record from the past would show up with the person's new name and not their previous name. This wouldn't be good either.
I guess my patient table could have the name fields there that would just draw from the child table, but I thought that was the purpose of relational databases- you don't have to have redundant fields.
I would appreciate other's experiences, advice.
Thanks.

[rmcgaffic]

Re: updating personal names; parent vs child table?

Doug,

You are right, this is a common problem. Fortunately, it has been well analyzed and documented in this reference which you may find a useful addition to your library:

http://www.amazon.com/Data-Model-Resource-Book-Vol/dp/0471380237/ref=sr_1_3?ie=UTF8&s=books&qid=1261141644&sr=1-3

Take a look at Chapter 2: People and organizations.

Unfortunately, Alpha Five can not fully model Silverton's recommendations, but you can come acceptably close. The problem is not with the database but Alpha's desktop form design approach precludes its direct application.

To respond to your particular problem, you might do something like this:

tblPerson
Person_Id
Title (Mr./Mrs./Ms)
First name
Middle initial
Last name
Suffix
Nickname
Social security number
Previous name (assuming need only to track one name change)
Date of name change (assuming need only to track one name change)

tblAddress
Address_Id
Person_Id
Address1
Address2
City
State
Zip
Type (Home, Office, Shipping, etc.)
Comments

tblTelephone
Telephone_Id
Person_id
Telephone_number
Extension
Type (Home, Work, Mobile, Fax, etc.)
Comments

The benefits of this approach to design is that you are unlikely to paint yourself into a corner.

In Alpha you define a set which links the child tables Address and Telephone to the parent table Person using Person_Id as the linking field.

Given the wide variety of e Communications methods in use today, you may want to read several different approaches that the author outlines and decide which one best suits your needs.

Many beginning Alpha users simply start to build their databases without careful thought, and unfortunately Alpha's own Contact manager examples do not qualify as best practice, whether you use the "free" one or shell out bucks for the one in the Alpha Store.

Having modeled the data correctly, you will soon come face to face with a equally perplexing issue: How do I present this informaton on a form?

You might find the attached white paper useful. Although I wrote it with examples based on MS Access, you can easily adopt them to Alpha Five.

If you spent any amount of time with Alpha, you are going to find that a lot of features that are taken for granted in MS Access and other development tools are not present in Alpha Five and workarounds are necessary.

Writing this from a client project site, I just checked my laptop and can't find the my file for "Display alternatives for 1:M relationships". I have a paper copy at home, which I will be happy to mail you a copy of, if you will send me a private forum message with your mailing address.

Bob McGaffic
Pittsburgh

[CALocklin]

Re: updating personal names; parent vs child table?

This is one of the biggest things that new db developers overlook - how to set up their table structure - even though there is plenty of information out there. So, Doug, it's good that you are thinking about it and good that you asked when things didn't seem quite right.

I addition to Bob's link you can find plenty of free stuff on the internet. In particular, do a search on "database normalization". It's also been discussed on some of the message boards here for earlier versions of A5 so you might try searching them as well.
A COUPLE WARNINGS
(1) Don't worry much about anything beyond Level 3 normalization. Most of the stuff beyond that is theoretically nice but very difficult to apply in reality and
(2) Even levels 1-3 may only be 95% applicable. Sometimes the situation demands doing something that doesn't meet the standard rules - just make sure you realize that you are ignoring the rules and are doing so for a valid reason that won't get you in trouble in the future.

[Peter.Greulich]

Re: updating personal names; parent vs child table?

Previous name (assuming need only to track one name change)
Date of name change (assuming need only to track one name change)

Or, you could just have a Comments field, memo type to note that info. If Jane Jones gets a divorce and changers her name back to Jane Smith, what difference does it make? The db structure is hinged upon the Person_ID, not the last name. Next time she makes an appt, your secretary looks her up by her new name, if it has already been changed in the db. I don't see this as a real issue. Plus if you use a memo field, if she is a serial divorcee, you can track all her name changes. Seems totally irrelevant to me.