I have a question. In the database I'm currently working on a patient will have say 10 visits. However, these visits are not exactly the same. Some sessions are done through phone, some are home visits, and others are normal visits. They have some similar properties, such as the Date it happens. But also different properties. For example, for normal visit there is attendence and reschedule. For home visit the next normal visit should be scheduled. Does this mean these are three different entities and three tables needs to be created for them?

When I make the form to enter the info, would it be possible to construct three subforms for the same patient, and only make one subform visible depending on which visit the patient is currently having?

These are the same entity and should be treated as such. There are numerous ways to handle the structure. How you do so will depend on how much control over the attribute definition you wish to give the end user. In this particular case, the minimum you should do is treat each visit as a single entity type, for those attributes that are not common to the visit they should be in a one-to-one linked table to prevent creating "empty" fields for those visits that do not need that information.

Ahh, thanks. However, if there are many small tables created for the non common attibutes (such as one for home visit and one for normal visit) won't it slow the database when queries need to be constructed to extract full information for a patient?

No, speed of db access is not determined by the number of tables. In fact, more tables with fewer fields is faster than few tables with many fields. But, do not let this determine your structure. Let your data and business model determine your structure.

You mean I shouldn't let the concern of database speed determine my structure, instead the data and business model should determine it.

I haven't formally studied database myself, in stead, I leaned it by doing. I learned the normalizations rules and such by reading. So I know some basic operations, but I lack the theretical background. For example, I don't have a sense what it means by letting the business model determine the structure. Could you recommend a good book for me to read?

Regarding my current database, the purpose is to track the patient information when he goes through a set of visits. The visits are consequential. Currently I think the table structure should be something like this:

Main table
patient ID(PK), last name, first name, etc

Vist table
visit ID(PK), patient ID(FK), etc.

The little tables for storing uncommon attibutes

What I'm not sure is how this structure can reflect the sequential part of the business model?

The business model is the set of attributes that must be tracked and the rules that govern the attributes values (these are called constraints). There is a technique that I have found useful for helping me determine the entities and relationships defined by the attributes. This technique is called Fully Qualified Naming. There is an example of it's use in the Access FAQA's Forum.

Using this techinique the attributes will group themselves into (more or less) the entity they belong to and define the relationships between the entities. A pitfall that you may find yourself in when using this technique is defining an attribute that is not really an attribute but is a value of an attribute. (I know this sounds like gibberish, but once you've used the technique a few times you will understand what I mean).

As for the sequentiality of your business model, that is very easily handled using transactions. In fact, every DB should be structured around the transactional data model. All entries into the DB should be transactions. Each transaction will be sequential by its very nature. When designing your DB you should always follow one guiding principle - "Always complete a record when it is created and NEVER 'handle' a record more than once".

Ahh this is a good rule for me. I'll find the FAQ and read it. I'm sure it will take me time to fully understand all these though. Thanks very much!