4 Replies Latest reply on Aug 19, 2013 10:49 PM by deebol

    Database Relationship Quandaries

    deebol

      Hi there

       

      I'm attempting to create my first "real" database. I'm intending help out a colleague who has a Housing Inspection business. His current solution involves laptops for employees who fill in Word documents, take photos and assemble everything at home or back at the office. Poor things.

       

      At this stage, I'm unsure how to proceed, as I am confused about relationships within the possible solution.

       

      CUSTOMERS | INSPECTORS | AGENTS | INSPECTIONS | HOMES

       

      The potential for Primary Keys and Foreign Keys seem endless. I've been learning from books, courses on Lynda.com and even attended a seminar on developing solutions for iOS. I reached out to FileMaker, but got no reply. And now, this whole thing is foxing me. I may be overthinking things, as I can see relationships in almost all directions.

       

      Obviously this is not enough information at this stage for any type of solution. Possibly the supply of an Entity Relationship Diagram (ERD) to you would help?

       

      Anyone out there kind enough to lend me a hand?

       

      Thanks in advance. The idea of doing a database seems easy, but has quickly become very complex. However, I'm still enjoying the challenge.

       

      Sincerely, deebol

        • 1. Re: Database Relationship Quandaries
          GaryTate

          Deebol

           

          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.

           

          Gary

          • 2. Re: Database Relationship Quandaries
            deebol

            GaryTate

             

            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

             

            450x600-ERD-v1.0.png

            • 3. Re: Database Relationship Quandaries
              user17418

              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.

              • 4. Re: Database Relationship Quandaries
                deebol

                Hi Zorba

                 

                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.