A Normalization Question

From: VHarris001 <vharris001_at_aol.com>
Date: 26 Jun 2004 16:02:09 GMT
Message-ID: <20040626120209.29625.00000578_at_mb-m16.aol.com>

Over the years, I've tried several different relational database schemes for tracking personal and professional information, and in every instance have been frustrated with the results.

The biggest problem seems to lie in identifying entities, or 'things.' For instance, when I set up a corporation as a business entity in a table, and use other tables for employees, vendors, customers, strategic partners, etc., I find that some employees are also vendors, customers, and partners. Of course, this means that I must enter the same person (or 'thing') into multiple tables.

At one point, I thought I could work around this difficulty by 'normalizing' the data -- for instance, by creating just one table for 'things.' But in trying to do this, I discovered that I still could not 'normalize' the data sufficient to eliminate duplicate entries in tables -- to which the only solution seems to be dramatically increasing the number of tables.

The only way to accomplish this that seems to make sense to me is to create one table that has a unique instance for each entity and another table that relates entities. (I note that a entity-relationship approach was was being discussed in another thread here.).

In this conception, the unique corporation would have a unique identifier (probably a unique number) in the entity table. Same with the person who is an employee. Also, in the entity table would be the entities, for example, of
"Delaware Corporation." and 'Human Being.'

In the relationship table, the corporation entity would be related to the
"Delaware Corporation' entity, the employee entity would be related to the
'Human Being' entity, and the corporation entity and the employee entity would be tied together in the relationship table as type 'employer-employee.'

When the employee also purchases from the employer, the relationship table would be updated to tie the corporation and the human being as type 'vendor-customer,' etc.

In this way, the entity table contains all entities, and the relationship table contains all the pointers between entites, and describes the relationships between those entities.

To relate this to the other thread about things and relations, in this conception, the names Brown and Browne, and the color brown would all three be entities in the entity table. If human being Brown was incorrectly tied to the entity 'Browne' in the relationship table, only the relationship table would be updated to point to the entity name Brown.

In this way, as was commented on in the other thread, even the letter 'b,' if helpful, could be set up as an entity or a thing.

I guess this would be normalization in the extreme, except that once the data is normalized to this degree, it no longer makes sense to keep different data types in different tables, because the number of tables becomes unwieldy.

Is there any database scheme that is set up in this manner, and that could be used to eliminate the necessity of entering the same 'entity' in multiple tables?

V Harris Received on Sat Jun 26 2004 - 18:02:09 CEST