Database Design Questions...

[John Dodson]

Hello All,

I have some database design questions and hopefully someone can point me in a good direction. I�ll try to be brief.

I work at a Children�s Center and I�m trying to improve/re-write our client tracking system which is now a hodge-podge of databases and redundant data entry. I need to track different types of changes or movements by clients so we know how and what to bill at the end of the month.

There is a master Clients database and here are the types of changes I need to track.

1)Admission and Discharges to our Center (usually just 2 dates, but can happen more than once)
2)Placement changes within our Center (for example from one
Foster Home to Another), effective date, destination.
3)Temporary Absences (for example, Home Visits, Respite Care, Hospital, Runaway), from_date, to_date, where.
4)Level of Care Changes (this affects our billing rate), effective date, LOC

Does this look like a logical breakdown of relational databases? I'm thinking there must be a better way.

The object of the exercise is to at least print out a list of changes with which to update a utilization database (or ideally update a utilization database from the above �transaction� databases) for a month of service.

Any tips on how to organize this would be much appreciated. Thanks.

John

[Tom Cone Jr]

RE: Database Design Questions...

John,

You are right to spend time thinking about how things might best be organized. In my opinion this is also a good place to get professional help.

In my own work it's useful to study each report I need the system to generate when it's finished. I make a list and think about the data sources required for each and every one of them. In doing this I begin making lists of tables, and how they might be linked to each other. Within each table I list the fields within each record.

One purpose of this exercise is to spot redundancies. If the same field shows up in multiple tables, I look for a way to simplify the design. I do not want to have to change the client phone number in lots of different places each time the client moves!

In your case a fundamental question is whether your system is oriented around 'clients', or 'admissions'. If you're billing clients directly you may want the universe of tables to revolve around a clients table. Otherwise, if you're billing a third party, you might want the universe of tables to revolve around an 'admissions' table. The choice would be based primarily on what's the easiest way to view the information you most often need to see?

In general it's better to work with lots of simpler tables than with fewer more complex tables. Ditto for sets.

For example, you may want the 'data entry' portion of your system to revolve around clients, while you may want the reports to revolve around admissions. It's not necessarily an either or situation.

-- tom

[ROBERT SULLIVAN]

RE: Database Design Questions...

I agre with Tom in that I look at what i want /need in reports. Then I look at the logical progression of inputting that data.
I also do plan on some redundancy in that if an important table corrupts, I can use the redundant fields to rebuild that table. Redundant fields are kept to a minimum to rebuild tables. Some would suggest that restoring the table from a backup may be quicker or better, and in many instances they would be correct. But in a large multi user real time environment a crash in the middle of the day would cause a great loss of data and would include tremendous time delays to regenerate the data not already backed up during that day.
An example would be a customer table and a sales table. If you are inputting a large number of customers in a day and performing a large number of sales I would bring over in the sales table the customer information such as address, phone ect. I would not bring over all the customer information. This way if your customer table crashes you can rebuild a good portion of the customer table from the sales table.
This approach only works or is needed if you are inputting large amounts of data and your system can support the added overhead.

Just food for thought

Bob Sullivan

[Robert Adler]

RE: Database Design Questions...

John:

I agree with Tom and Robert. But redunancy should be kept at a minimum. It can create a maintenance horror. Backups should be scheduled throughout the day in high usage environments.

After identifying the output requirements you should then look at the source of your data. Identify the fields that match the output and any calculations that may be needed to attain the output.

Your process and tables and their relations should flow more easily. Your ability to normalize the database(eliminate redundancy) will take some time, but you will find it quite rewarding. All of this can be done without considering the programming language.

You will make some design decisions based on Alpha's capabilities.

I hope this helps.

bob adler

[John Dodson]

RE: Database Design Questions...

Tom, Robert and Robert,

Thanks for your feedback. Very helpful.

Yes, I am trying to spend time up front to think about the design. I've worked with Alpha 5 a number of years and developed numerous relational applications but none as ambitious or as complicated as the current project. Tom, I'll keep the professional advice in mind, but thought I'd first see if I could eeek a little professional advice out of your guys. ;-)

I do have output reports to look at, quite a few of these are spreadhseets - which is what I'm trying to get rid of - so will take a hard(er) look at these. And thanks for the advice on using lots of simpler tables/sets as opposed to trying to do too much with one table.... that was really the question in my above example, I had started to group 'like data' in numerous smaller tables and was wondering if this is the best way to go. (And yes I want data entry to revolve around clients and reports to revolve around 'admissions' or 'services' might be more accurate.)

Which brings me to an data entry question. Since I'm going to be building many sub-tables related to the main table, any suggestions on how to best present this to a data entry person? On the main data entry form, I'm thinking of placing buttons for each of the sub-table functions in order to pass the client number to the sub-table (e.g entry person will first find the client and then select appropriate function/form to fill out), sound logical?

Rober A., Yes, I've plugged XBasic written by others into some of my applications, but would like to avoid as much as possible custom XBasic code (I have other duties besides application development! :)). I know enough to try and keep redundancy to a minimum, although I do sometimes bring some data over from a main table. It is really not a high usage environment. We may have 3 users updating the data on 50 or more clients.

Thakns again, nice having someone to bounce design ideas off of.

John

[Tom Cone Jr]

RE: Database Design Questions...

John,

Concerning your data entry question, I'm a big believer in lookups. In fact, I organize my design into two separate categories.

In the first I use one or more tables to collect the information I need for a single transaction. Then I study that data entry form and search for potential 'lookup' fields. Fields that I prefer my user to pull from a list, instead of keypunching by hand each time. Could be zipcodes & states, could be transaction codes, could be pricing information, whatever. I use as many of these as possible. Especially wherever I will be doing filters or queries for reports, and I darn well want to know that the same item is spelled the same way each time.

For example, I collect the college name for each graduate member in my database. If I let the user keypunch the college name from scratch, for the University of Florida College of Law I'll get:

UF College of Law
Univ. of Fla. College of Law
University of Florida
UF
Univ. of Floria
Univ. of Florida
Univ. of Fla
University of Florida College of Law


and many others

It's darn near impossible to collect all these variations in one query. So... I insist the user fill in the college field using a lookup.

I regard this 'lookup' type data as being in a second category. I provide separate data entry routines (forms) so that the user can keep these 'supporting lists' current. These routines are different from those used in the primary, category one, data entry forms.

-- tom

[Tom Cone Jr]

RE: Database Design Questions...

John, one final note. I prefer to access the 'supporting lists' from the menu bar of my main or opening form. I think it helps the user keep this type of data mentally separate from the primary data for a particular transaction.

-- tom

[Edward F. Schulz]

RE: Database Design Questions...

Hi John,

Tom is right on the money when using lookups wherever possible, it not
only prevents various ways of entry, but the all important typo. As
far as how to present the various forms for data entry, my approach is
this; First I hate cluttered forms or any type of cluttered screen,
users tend to look at the forest instead of the trees, hence my
approach is this for the start-up menu.
1. Enter data
2. Edit data
3. Print reports
4. Utilities
5. Others you may need
6. Exit (All other sub-menus have a return to main menu)

BTW, when the user is an accomplished typist, they will appreciate a "hot letter" on each selection so he/she doesn't have to use the mouse for the menu selection.
Most like the idea of previewing a report before printing, so that choice should always be available to the user.

When Enter data button is selected, it brings up another form that
provides the opportunity to enter data in the various tables. All in
plain english, no computerese. Obviously Edit, Print and Utilities
follow with the same approach.

Rotsa O' Ruck,
efs

[John Dodson]

RE: Database Design Questions...

Thanks guys,

Yes, I do use Lookups wherever possible to maintain integrity - I was trying to think of a better way to enter data to the log files - but instead of my cluttered screen approach above, maybe whay I need to find a better/faster way to lookup the desired client record with which to make a log entry.

I see that the learn alpha.com site has some lookup techniques --- you guys have any favorites?

Thanks again for the feedback - I think I've just about got
enough of the design down on paper to make a start.

cheers,
John