table relationship question

[SCantoria]

currently I have three tables and I will create more if needed to accomplish my task. My challenge is that I do not know how to start creating the relationships.

ENTITY
.ENTITY_KEY
.ENTITY_NAME
.ENTITY_TYPE ("P" PERSON OR "O" ORGANIZATION)
ENTITY_FOR
.ENTITY_FOR_KEY
.ENTITY_KEY
.CONTACT_KEY
.ORIGINATION (CLIENT OR IN-HOUSE)
.ORIGINATION_DATE
CONTACT
.CONTACT_KEY
.CONTACT_TYPE (PROVIDER OR CONTACT PERSON)
.FNAME
.LNAME

I am trying to keep a normlize design. Here is the pseudo data flow.
An entity can be a person or an organization. This entity can have multiple person members. This entity can also be a member of an entity which is in a higher echelon.

Each person members can have a contact person or self as contact person.

Any help to get me started is greatly apreciated.

Thanks,

Steve C.

 

[Darrylles]

Hi SC,

Lets ignore yours and look at an example:

tblEmployee          tblJob
pk             |---> pk
name           |     jobtitle
job_fk     <<--

pk are Autonumbers key fields for the tables (example only).
job_fk is Long data type.

This means that the pk from a tblJob record can be placed in the job_fk of 0,1 or many tblEmployee job_fk fields.
So one tblJob record can be applied to many tblEmployee records.

Does this help?

ATB

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[SCantoria]

Not really, because this is what I have right now. In the current live database I have a one-to-many relationship between entity and persons. The piece of the of the puzzle that did not make the initial design is:

definitions:
entity = clinics, healthcare facilities, hospitals, organizations, offices, person owned practice, and etc...
person = provider

business rule:
1.) a person must be a member of an entity.
2.) an entity can be a person if the practice is owned be the person.
3.) an entity can be a member of higher echelon entities.
4.) an entity can have lower echolon entity members.
5.) an entity can have many person members.

so far all have configured is BR1 & BR5.

I hope this helps.

I am on AIM for a more real-time collaboration.

Thanks,

Steve C.