Design question, select either or both?

[Steven811]

Hi

I'm building a simple CRM db for my friend and he has just moved the goal posts.

The basic structure is hierachical:

Customer
RegionalOffice
Site
Contact
Job

The problem is that the contact may be based at the site or the RegionalOffice although sometimes there isn't a RegionalOffice. There is always a Site.

A contact may have responsibility for many sites.

How would the relationships work? I have experimented quite a bit and searched throught the archives, now I'm getting more and more confused.

Any advice would be great.

Thanks in advance

Steven811

 

[KenHigg]

Steve, One method to work out relationships is to draw up a diagram similar to the attached file. It would read out like: A customer has many sites. A regional office handles many customers. Etc. Then the one side and the many side of a relationship becomes obvious and complex models can be worked out in smaller pcs...


Hope this helps...
kh

 

[Steven811]

Sketching the relationship

Hi Ken

Thanks for the suggestion.

I have previously attempted to sketch the relationship and I enclose a screen shot that shows what I have. I need to be able to have a relationship where the RegOffice table is optional and I am unclear how to proceed.

How can I link the tables so that it can reflect that the contact may be based at the site or the RegionalOffice although sometimes there isn't a RegionalOffice. There is always a Site

Any views would be appreciated.

 

[KenHigg]

The first thing I would do is establish what entity (table) is the dominate one in the db and have all the other support that one. In this case, looks like your db tracks job information. (?)

TblContacts:
Q1. Is this contact an employee of your company or the cutomer (the job customer)? Assuming the contact is your employee, looks like your set up may work here.

tblSite:
Q2. Is a site really related to a contact or is it related to the job?

tblRegOff:
Q3. Is this your companie's reg office or the customers

tblCustomer:
I would think this table needs to be directly related to the job table (?)

kh
FYI - This may take several iterations to establish the relationships you need...

 

[Steven811]

All revolves around the contact

The primary relationship is the one between the contact and the site, everything else is secondary.

One site will have many contacts and each contact many jobs, this part of the relationship I have resolved. My problem is now that the contact may be located at a regional office and not at the site, but this is not always the case perhaps 50/50.

The customer and contact can change every 12/24 months.

The link to the customer is less important, but still needs to exist.

This sort of structure is common in the facilities management industry where the contract changes every 12/24 months although the site and job will remain fixed.

I thought that a separate table for the site and regionaloffice with a link to both the customer and site would work, but having been down this route before and got it wrong I thought that I'd seek a bit of advice first.

 

[KenHigg]

I would put a custid field in the site table and link it back to the customer table. Then if reg office for the site is blank, the job is managed through the primary customer table info...


I think this would work...
???
ken