Issue with Relationships (1 Viewer)

[sajarac]

Hello All, I hope someone can point me in the right direction:

This is my issue, we do buildings inspections, each inspection is a customer and each customer has several buildings, and in each building we check doors, windows, roof.

I can't figure out how will be the relation, any help will be greatly appreciated.

Thanks in advance

Sajarac

 

[Minty]

I suspect you are confusing yourself with your terminology a little - If a Inspection is a customer why not call that table Customers?
Then I would have thought each Customer could own many buildings and each building may have one or more inspection ?

So you should have an inspections table which would be linked to your buildings table , and that buildings table linked back to your Customers . Inspection items would linked to the InspectionID, not the customer.

 

[Ranman256]

This maybe should be 1 table,and add the type of inspection on the record.
tInspection---(other tables)

TPerson table (person who owns location)
PersonID (auto)
Name
Address
Etc...

TInspector table
InspectorID (auto)
Name
Address
Etc...

TLocation table (location of inspection)
LocationID (auto)
PersonID
Address
City
Etc....

TInspect table
InspectID (auto)
PersonID
InspectorID
LocationID
InspectDate
Etc...

All other tables, Windows,doors,etc would link to the tInspect table via the InspectID.

 

[sajarac]

Thank you very much guys for your prompt reply, I will change the concept and get back to you with the resutls, sorry I am not an expert as you guys, but I will try to do my best.

Many thanks

 

[sajarac]

Something like this?

 

[Minty]

No not quite - you are still using the Inspection table to limit you to one customer per inspection/building. Your inspection table should have the CustomerID in it NOT the building ID.

And maybe this is being lost in translation, but your current Inspections table looks more like a buildings table if you look at what you are storing in it.

Think about what you currently store on paper. Or possibly spreadsheets.

Generally in most business models there a fixed things that are related to multiple other fixed things. In database terms these are known as entities. You have correctly identified that a inspection can relate to many windows / doors and Misc things. Hence you have a many to one relationship.

The same should apply to your customer record. Your customer may own many buildings. So you have a customer record that can relate many buildings.
BUT the building may not always belong to that customer. So you need to store the building record ID and customer ID in a table for your current inspection. This is your inspection table.

 

[sajarac]

Hi, thanks again, I understand your explanation but I can not figure out.

Sorry sorry could you provide a sample?

 

[Minty]

Not easily I'm afraid - Change the CustomerID field in the customers table to be the PK and set it to autonumber.

Rename your current Inspections table to Buildings and rename the PK to BuildingID, then create another table called Inspections.

For now put five fields in;
InspectionID - auto number PK
CustomerID - Number FK
BuildingID - Number FK
CompletionDate - Datetime
InvoiceNo - Text

Then re-link your Doors / Window /Misc tables to your new inspections table

If you are then still stuck post up a copy of your database with the some simple sample data to keep the size down. Zip it if it's too big.

 

[sajarac]

Not sure if this is OK, I guess not I've followed your instructions but nothing

:-(

 

[Minty]

Okay - so this is a data planning question - is an inspection always carried out by one person? Or would you limit the Inspection record to be under one persons control?

I ask because you currently have the Inspection person stored in the customer record, this isn't correct. That has nothing to do with the customer. This where you need to examine your business model.

I would take a step back and in plain English describe your business. Then describe what you what to get out of the database , no database terms just a functional description of how this will help.

So as an example of a requirement of the assistance you want the database to provide, "We struggle to find customers inspection records when they phone up and all they know is their name, and sometimes the address of the building."

 

[sajarac]

Thanks again for your interest with this, the enclosed diagram will help?

We already have the customer information and the buildings information, the information that is collected is the number of doors, windows, roof etc.

 

[Minty]

No - you are still thinking about how you think the data should be stored - not what your real business needs are.

How are you dealing with this at the moment. Do you have a piece of paper that is completed and filed?

 

[sajarac]

I am trying to do a database since long time ago, but always got stuck at the same point, currently we have an excel file with with tabs and each tab is a building, inside each tab we have a template with all of the calculations for doors, windows, roof, etc, then at the end a summary tab with the rest of the formulas.

 

[plog]

Maybe I can help:

sajarac - explain to an 8 year old what it is your company does. No database jargon allowed--no tables, no tabs, no Excel, no formulas. It's career day at an elementary school explain to them the tasks you perform.

 

[sajarac]

Thank you, we basically do inspections "building inspections, we receive a call from a customer and he wants us to visit his facility and check the building to reduce the energy consumption, so before we go to the building the customer provide all of the building(s) information like building name, address, building size, how many floors, etc, once we are on site and following a PDF floor plan we start marking doors, windows, etc and their condition. I mean is the door has to be fixed, replaced, etc.

like a tally sheet, after that we transfer all this data to a excel file and we do some calculations to provide a quote

is this ok ?