>
> Mike,
>
> The point I am trying to make is that you can work with non-normalized tables in a multi-user environment. How many times have you seen an employee table with
>

> CREATE TABLE Employee (RecordID I AUTOINC, Surname C(25), MiddleName C(25), Surname C(25), ...)
> 

>
> This is a VERY common scenario in multi user environments but one could argue that this table is not normalized. How many employees have the name "Smith" or "John". One could (and I would argue, should) normalize this data but it will nave no impact on the ability for multiple users to work with the data.

You can work with non-normalized data, but that means you cannot have a straight-forward design.

That table is sufficiently normalized. The surname, middlename and I presume you meant firstname are descriptive of the entity, in this case an Employee. That one could break out surnames and firstnames and middlenames and replace them with keys is taking normalization too far. There is a yin-yang to normalization. Normalization is keeping unlike entities apart.

The point in a table is to not have two REPRESENTATIONS of the same entity. One should also not have multiple representations of other entities in the same table.

A name is not really an entity. It's a description of an entity. An entry in a dictionary. I cannot grab a MICHAEL or KEN. A house is an entity. The address of the house is a representation of the house entity.

I could have a bank transaction table.

TransID i, Amount n(10,2),

Should I make an amount table and replace amounts with a key? No because the amount is not the entity - the raison d'etre (the reason for the existence) of the table.

Should I put address in the employee table? Technically no. It is not a description of the employee entity.

What you're suggesting requires much more complexity in the handling of the data.

I can make a simple form class. It can provide simple CRUD operations. I can have people adding/editing employees all day without stepping on each other without complex coding. I can have an address table and again, no stepping on each other. Fixing the description of the address entity benefits the data integrity of the system. I could even have a history table showing the history of the addresses of the employee. The employee's current address can be extracted easily.

That makes address historically accurate. If a piece of mail is returned, I can look at the address history and see that it was sent just prior to the most recent address change.

The address history can be represented with a grid on the employee form. All that can be handled easily. It will be a rare thing that there is a conflict between two users trying to update these entities. So I manage to meet the requirements of the system with a single form UI design, a single data class design and three tables.

Mike Yearwood