Complex (for me anyway) schema

[fubofo]

Hi guys, firstly I'm new here so let me say hello .
Secondly, I'm not wholly proficient with databases (I'm learning SQL, MySQL and PHP for other things) however, I'm not a complete noob.

Anyways, on to the problem.

I need to create a Contact / Company / Project database. I think I have normalized the database but am a little unsure with some things (explained later). Basic layout is:

Tables:
Head Office Group
Head Office Contacts
Company
Contacts
Projects
Leave / Join dates

Head Office group can have one or more companies assigned
Head Office group can have one or more Head Office contacts assigned
Head Office Contacts can have one or more Companies assigned
Company can have none, or only one Head Office Group assigned
Company can have none, one or more Head Office Contacts assigned
Company can have one or more Contacts assigned
Company can have none, one or more Projects assigned


Company can have none, one or more Leave / Join dates per none, one or more Projects
Contacts can have none, one or more Projects assigned (for purposes of who deals with specific project that is assigned to related Company)


(please note I have left out Foreign Keys until I have confirmed a relational structure with following tables)

tbl: Head Office Group
headofficeID (auto# PK)
nickname
company_name
building_name
building_number
area
street
city
postcode
telephone

tbl: Head Office contacts
headcontactID (auto# PK)
title
forename
surname
position
extension (related to head office telephone)
mobile
email

tbl: Company
companyID (auto# PK)
nickname
company_name
building_name
building_number
area
street
city
postcode
telephone

tbl: Contacts
contactID (auto# PK)
title
forename
surname
position
extension (related to company telephone)
mobile
email

tbl Projects
projectID (auto# PK)
projectname

tbl Leave Join Dates
projectdateID (auto# PK)
projectID (FK)
companyID (FK)
joindate
leavedate
leave reason

Hopefully this is laid out correctly? Now I just need to come up with the correct relations and foreign keys for some potential queries that are troubling me:

1) I may need to display full company details, also displaying which Head Office the company is assigned to, while also showing all contacts assigned to the company (including Head Office contacts and which project they are assigned to)
Do I need a separate Head Office contacts table, or is there some way to assign a contact to either Head Office or just Company? (ie maybe a join table like [id]-[contactID]-[headofficeID]-[companyID] (but this would then leave a blank in either headofficeID or companyID fields, and potentially have more than one row if the contact is assigned to one Head Office but multiple companies?)

2) Would the above layout allow me to correctly audit when a company joins a specific project, then leaves the project, then joins again and so on?

I'm fairly sure the above is fine, although I know it needs a few extra join/link tables in somewhere - hopefully some good soul can give me a hand with this? Also please feel free to tear it apart and correct me in any way you see fit if I'm entirely wrong with any of this

Thanks

[jzwp11]

If the head office is like the headquarters of a company or a parent company then it belongs in the company table (no need for a separate table). You will need a table to related the head company with its subsidiary companies

tblCompanies
-pkCompanyID primary key autonumber
-txtCompanyName


tblCompanyRelation
-pkCompRelID primary key autonumber
-fkHQCompanyID foreign key to tblCompanies (the headquarters)
-fkSubCompany foreign key to tblCompanies (the subsidiary)

The same would apply to contacts. All contacts in one table; you would relate the contacts to the respective companies

tblContacts
-pkContactID primary key, autonumber
-txtFName
-txtLName

tblCompanyContacts
-pkCompanyContactID primary key autonumber
-fkCompanyID foreign key to tblCompanies
-fkContactID foreign key to tblContacts

[John_G]

Hi -

You're on the right track, but I do have some suggestions.

1. Since the structures of tbl: Head Office Group and tbl: Company are the same, you should put them in one table, perhaps with a Yes/No field to indicate if the address is the head office or not. That also makes it easier to work with tbl Leave Join Dates, which in your layout would would have a problem if the company was a head office. Not only that, but most small companies only have one office, which by deafault is the head office.


2. The same is true for contacts - the structure is the same for both, so the can be in one table.

3. For the telephones, I would put the whole phone number (number + extension) in each contact record. That might seem a bit redundant, but your layout won't work for large organizations where each person has their own distinct telephone number.

On looking through your requirements again, I do have a question - is there some key difference between Head Office Group and Company? I don't see that there would be, unless you are using terminology differently (i.e. what is a "Company"?). (See point #1 above)

Hope this helps.

John

[fubofo]

Hey, thanks for the replies. Wasn't really expecting such a quick response, anywhere else normally takes weeks for advice about databases lol.

Yeah you are both correct, Head Office is like Company Headquarters. Take for example bars / pubs / clubs - some may be part of one over-arching conglomerate like "Example Inns Ltd". I simply wasn't sure about relating data within the same table...? (I see now that the 'relation/link' just needs to be done in a separate join table.)

With this layout I assume it would be easy enough for me to create a report that lists company details (if possible grouping by Head Office and displaying any associated companies beneath)?

Still undecided about the telephone number as I see that the number is fixed to a business and not a person, plus if a person leaves a company then the telephone number should not go with them. I will have to think about this further.

One thing neither have commented on is the ability to assign specific projects to customers. I guess this similarly needs a join table as per company relation table.

There is actually quite a bit more information and a number of other tables involved in this database, however I feel I can look at these after correcting any problems with the supplied info.
I might submit the entire schema later just for review.

[jzwp11]

With this layout I assume it would be easy enough for me to create a report that lists company details (if possible grouping by Head Office and displaying any associated companies beneath)?

Yes, you are correct.

Still undecided about the telephone number as I see that the number is fixed to a business and not a person, plus if a person leaves a company then the telephone number should not go with them. I will have to think about this further.

One way I have seen is to have a table of telephone numbers and then use a foreign key in the company table (the company phone number). For contacts, since a phone number can apply to many people over time (as people leave/join the company), you have a one-to-many relationship, so a table that joins contacts with their telephone number

tblContactPhones
-pkContactPhoneID primary key, autonumber
-fkCompanyContactID foreign key to tblCompanyContacts
-fkPhoneID foreign key to tblPhoneNumbers
-dteEffective (effective date)

One thing neither have commented on is the ability to assign specific projects to customers. I guess this similarly needs a join table as per company relation table.

The project table will stay the same:

tbl Projects
projectID (auto# PK)
projectname


The company contact would join to the project as follows:

tblProjectCompanyContacts
-pkProjCompContactID primary key, autonumber
-fkProjectID foreign key to tblProjects
-fkCompanyContactID foreign key to tblCompanyContacts

Since a company/contact can have many join or leave events that is a one-to-many relationship.

tblProjectCompanyContactEvents
-pkProjCompContEventID primary key, autonumber
-fkProjCompContactID foreign key to tblProjectCompanyContacts
-dteEvent (event date)
-txtEvent (join or leave--you could have a table of preset events and then use a foreign key here)
-txtEventDetail (your reason field)