If you want a hand I can certainly help, but this is what I suggest you need…
Key is to identify what connects to what and what sort of relationship it forsm.
one to one (1 record links to 1 record)
one to many (1 record links to many records)
many to many (multiple records link to multiple records) - BAD IDEA. DO NOT DO THIS ;-)
Draw each of your 5 tables and then join them to each other (where appropriate). Then ask yourself if one record in the first table links to 1 or multipl records in the other.
If you end up with a many to many relationship then you need to insert a link table in between, the link table will have 2 one to many links with the other 2 tables.
That should get you going.
Excuse the delay -- I'm doing this in my spare time.
I've put together an ERD. Could you cast your eye over it and let me know what you think?
The solution I have in mind would have the user enter a home, hit PLUS to create a new room (as the home is explored), that's why I have a table for "rooms".
Thanks -- Dee
I would connect these a bit differently at first.
Inspectors should be connected to Inspections.
Inspections should be connected to Homes (Properties)
Homes (Properties) should be connected to Rooms (Areas)
Now for some questions :
Can someone be an Agent and an Inspector?
For every Inspection, you will want to specify InspectorID, CustomerID, and PropertyID.
Which ones you want to enter automatically will impact the connections. Do you have a workflow yet of how the user will walk through the data entry?
Lastly, You will have situations where 2 agents will refer the same customer just after different times. So a Customer can't be locked to a single Agent nor would you want 2 dupliate customer records just so you can connect them to two different agent. You may want to consider connnecting them through the Inspection table. But that only depends on your work flow.
Thanks for the feedback.
From what you have pointed out, in the ERD:
- Inspectors are already connected to Inspections (one-to-many). One Inspector can generate many Inspections.
- "Inspections should be connected to Homes (Properties)" -- that's an interesting approach. Wouldn't they be connected via inspectors and then customers? After all, Homes can have many Inspections, but only via Inspectors invited to do the work by Customers. Surely these tables can't have two or more connections running through them?
- Homes are already connected to Rooms (one-to-many). One Home can have many rooms.
"For every Inspection, you will want to specify InspectorID, CustomerID, and PropertyID."
Well, the way I intend to build the relationships and introduce interaction is by way of Primary Keys and Foreign Keys whenever there is a one-to-many occurence.
The workflow would be something like:
- Inspector fills in their own details.
- Inspector fills in the Customer details.
- Inspector fills in the Agent details.
- Inspection begins with Inspector entering home, creaing a "room" and recording details.
- Upon completion, the Inspector generates a report (Inspection).
"Lastly, You will have situations where 2 agents will refer the same customer just after different times."
Agreed. So this relationship would require perhaps a link table, or as you say, link them via the Inspection Table.
Interesting. Could I please reserve those thoughts before creating a new ERD, and wait to hear back from you on my points?
Thanks for your time.