Massive check-list database

[JFo]

I forgot to add relating to reporting and new inspections 1 inspection will only have 1 clientsite contact as well as the 1 client contact (head office)

[Rainlover]

There is lots more to do, especially with relation to the actual Inspections.

I have to go out for a while so chat later.

I have attached what I have done so far. It is totally different to what you sent me so all your forms etc have been destroyed.

If you do not like my style then feel free to change it. But I hope it does help with your understanding of Normalisation.

Any Questions then Please Ask.

[JFo]

Thanks for all your help, you could say I am extremely confused with the structure without the forms. The way you input and retrieve data is my best way of understanding if the database is designed right. A few things I cannot see from your relationships and tables is how to add or link a client to site for example, contacts table structure may work however without progressing through form I am not sure how it can add a contact for a client and then once a client is linked with a site how it can add a contact for the client for that site.

I also see that you have the equipment fk in the inspection table however there may be 20-30 pieces of equipment inspected in 1 inspection. Is this correct?

I am obviously a novice in this area of design but as long as database does what I need it to then I would like best way to do it

Essentially from a data entry POV-
A user or inspector must be able to add or edit a client, add or edit a site, add or edit contact details, add or edit all details about an inspection undertaken at a site including all equipment that has been inspected and the results including non-compliances

Ultimately from a data retrieval POV-

All info must be easily searchable to get any information about an inspection including what client and where, who is the clientcontact what pieces of equipment were inspected and what didn't comply

Overall a report must be produced from the system that includes all information relevant to an inspection inlcuding
- Client name, details, contact, etc
- Site Name, details, etc
- CllientSiteContact, details, etc
- Inspection details, time date, inspector, etc
- Equipment inspected, all details for all equipment inspected and whether it failed/ passed, etc. As per the check-list tables I have included

Other reporting and alert like queries will also be required from system however this is the essence of what the system must do, can the current system do this as designed with this structure?

Furthermore every input must be validated for every single field and the practically all fields will need different validation and default fields.

Are we on the same page as to the system requirements? Apologies for my ignorance however without the forms asking me exactly what I want to enter or search for, edit, etc and without looking in database to see that all data is recorded into appropriate tables I cannot tell if the design is correct

[JFo]

Furthermore as per relationships
1 client may have many sites and 1 site may have many clients
1 site may have many client contacts
1 site may have many inspections although 1 inspection is only on 1 site
1 inspection may have many equipment and one piece of equipment may have multiple inspections

for each piece of equipment there is a completely different check-list

[JFo]

I can't begin to describe how thankful I am for your assistance

[Rainlover]

Let's sort this out a little at a time.

Does the Site have a Contact?

Can a Client be located at different Sites and if so then the same Client at a different Site would therefore have different Contacts. Yes? No?

[JFo]

OK
A site does not have it's own contact, a client does

A client has one main contact lets just say he is the manager of the company (head office contact) there can only be one and is updated if the old manager leaves for example so I put this in the client table and client form

A site can have multiple clients on it at any one time (consider a mine or a building site) and also a client can be on multiple sites (multiple mines/ building sites)

For each site you are correct there is more than likely a different client contact, there may be multiple clients on the site so multiple client contacts may exist for each site there may also be the same client contact on different sites


to keep it even more complex a site may also have multiple contacts on the same site and even more complex again is that occasionally depending on how large the company is the same contact from head office may be the site contact

a possible scenario is joe works for company a and is our main client contact and james works for company b and is our main client contact, joe is also our site contact on site 1 and site 3, mark works for company b and is a site contact on site 3 and tom works for company a and is a site contact on site1

Sorry for confusion I hope this clears it up

[JFo]

Additionally when the final report is printed for an inspection it must include the client, the main client contact, the site and the client site contact.

[Rainlover]

Try the attached.

[JFo]

Strictly sticking to your client contact relationship So how do you link different client contacts to different sites?

your relationship only allows 1 client to have many contacts

"clientcontacts are specifically related to sites, yes one client can have many contacts but one client specifically has many clientsitecontacts"

also in your relationship 1 site has many clients but 1 client can not be at many sites (not sure if you got to this bit yet)

[Rainlover]

Strictly sticking to your client contact relationship So how do you link different client contacts to different sites?

I added a Join Table to fix this.

your relationship only allows 1 client to have many contacts

Many Clients can have the Same or Different Contacts.

"clientcontacts are specifically related to sites, yes one client can have many contacts but one client specifically has many clientsitecontacts"

Did you not say that a Site does not have a Contact, it only has Clients

also in your relationship 1 site has many clients but 1 client can not be at many sites (not sure if you got to this bit yet)

One Site can have One or Many Clients.
A Client can be at One or Many sites.

Note. When you select a Client e.g. BHP you choose the Client with the appropiate Contact. If this does not exist then create a new Client Record together with the Contact.

Try creating a few Queries to see if you can create the result that you need.

[JFo]

OK thanks yes this is similar to what I have as well with the clientsitejunction table, tbh I had not gotten around to the contact side of things yet as I didn't feel like it was as important.

Therefore moving along this is same relationship as I had originally however I was instructed on this forum in an earlier post to have dual primary keys for the ClientSiteJunctionTable, either way it will do the same thing

I also implemented a clientsitecontact junction table for linking each clientcontacts to multiple sites

So forgetting about the contact stuff for now the main relationships are the inspections/ equipment
So after that this is where things get tricky. I can't see why you would join the equipment to client table?

Also I can't see how the inspections are linked to the sites

I would think you would use the siteid as a foreign key for Inspections table

Also I thought you would need another junction table in between inspections and equipment as many equipment for inspections and equipment can also be in multiple inspections (another many to many relationship)

Here is a screenshot of my current relationships I have updated a bit since what I sent you. Let me know your thoughts?

Attachment 4450

I understand it is the anti-normalization however there are many to many relationships everywhere

I wish it was all one to many relationships with a standard check-list but alas

I also don't see the point in having tables for cities and states besides if you were trying to teach me normalization

Thanks bud much appreciated

[Rainlover]

dual primary keys

I assume you mean Foregin Key.

The is no such thing as dual primary keys. A Table can only have One Primary Key, but this key could be made up of more than one Field. This is FYI only. As you are using Autonumer then there is no need for a Composite Primary Key.

I am sure you will come across this type of thing eventually.

[Rainlover]

I can't see why you would join the equipment to client table?

Who owns the equipment? The Client or the Site. If the site owns the equipment then my structure is incorrect. But if the Client owns the Equipment the the equipment must be attached to the Client which is what I have.

Note that the Client is attached to a Site so indirectly the Equipment is attached to the Site through the Client.

[Rainlover]

Also I can't see how the inspections are linked to the sites

Same answer as before.

The Equipment is attached to the Client which is attached to the Site.