I have a database that I have inherited that is implemented in Firebird.
Its not a big database, but has a number of tables that are implemented in a
'physical' manner. These are specifically dealing with business 'entities'.

What I mean by this is that the previous developer had modeled the database
design so that there is a table for CLIENT, VENDOR, MANUFACTURER, etc.
They are all pretty much the same content - mainly demographic information
(ie. Name, Address, Phone, etc.).

What I have been asked to do is extend this database so that it offers much
more CRM style functionality and if I followed the same approach, I'd end up
having to create half a dozen more entities such as LEAD, ACCOUNT, etc. to
represent the various stages that a prospect is in before they become a
CLIENT.

It would seem to me that an easier and more manageable approach would be to
do away with storing the physical state of the entity as the basis of the
entity, and create a model that is more like this:

ENTITY (The Business or Person Entity)
ENTITY_LOCATION (A physical address of an entity)
ENTITY_CONTACT (A person who is associated with the entity)

The relationships between this structure would be something like:

ENTITY (1) ----> (n) ENTITY_LOCATION
ENTITY (1) ----> (n) ENTITY_CONTACT

Possibly I'd extend this to relate ENTITY_CONTACT with ENTITY_LOCATION, but
I can work that out later.

Then I would create a table that would identify different roles that
entities could be, like so:

ROLE
- PK_ROLE_ID
- PK_ROLE_NAME

An ENTITY could be a member of multiple ROLES, therefore to resolve this
many to many relationship, I'd incorporate a table as:

ENTITY_ROLE
- FK_ENTITY_ID
- FK_ROLE_ID

My ultimate goal is to be able to reduce redundancy of ENTITY data, simplify
the code that handles the List, Add, Edit, Delete functionality (all web
based) and yet be easily able to filter entities down to who is a member of
what role for users to see.

If you have any suggestions for something I'm not seeing here, please let me
know. If there is any existing sample code for this type of modeling out
there that anyone has done, I'd love to hear it. I have to do a fair amount
of re-factoring of the existing database to accommodate this, so I'd prefer
to get other's experiences for doing this sort of thing before I delve into
breaking everything at the foundation level to accommodate this model.

Thanks in advance for any feedback.

Myles


============================
Myles Wakeham
Director of Engineering
Tech Solutions US, Inc.
Scottsdale, Arizona USA
Phone (480) 451-7440
www.techsol.org