6 Replies Latest reply on Aug 9, 2010 5:21 AM by LaRetta_1

    Relationship questions - newbie

    DougLoader

      Title

      Relationship questions - newbie

      Post

      Hi All I have 3 tables.  Table 1 - List of users.  Table 2 - List of Equipment.  Table 3 - List of computers I need to create a library, borrowing database.  So my Users (table 1) can borrow  items from either Table 2 or Table 3. So do I need to create another table to house the 'borrowing status' Table 4? I have created a primary key for table 1,2,and3.  I have developed this 4th table as well which so far has 3 foreign keys.  But I'm very confused, as I don't think I have got my Entity Relationship diagram correct. Please, any help would be hugely appreciated.  Thanks in advance.  I have attached a picture to illustrate. Doug

      Screen_shot_2010-08-05_at_11.28.27_AM.png

        • 1. Re: Relationship questions - newbie
          LaRetta_1

          Why do you have both an Equipment table and a Computers table? 

          And what does the pkYear12 represent and why do you have a Year11 Students table occurrence? 

          Actually you say you have three foreign keys but I see four.  Also be sure to add a primary key in your Lending table. 

          Please explain how your lending process works.  Are staff assigned Macbooks and then they can also check out other equipment?

          I would think that your Lending table will need a Checkout Date, Length (duration (number) to calculation date due) and a Checkin Date.

          I don't have a clear sense of your purpose, context or process yet and only then can we properly advise.

          • 2. Re: Relationship questions - newbie
            DougLoader

            Thank you for your response.  You are right, my picture did have one extra table 'Year 11 students' (sorry - quite misleading).  This table has the same purpose as the staff in the sense it is a populated table of users.

            I am trying to create a lending library of Equipment and Macbooks.  Staff/Students can borrow multiple items of each.  I feel that I am on the cusp of a Eureka moment.  But am just getting confused with how to populate my '5th table' or the form which student/staff would complete to hire the items.

            I need the student/staff to be able to select their own 'profile' (details from student/staff table).  Choose an item from either Equipment or Macbook table and for the item they have 'borrowed' to not be available to other users.  Adding hire dates etc would also be beneficial.

            I am investing every hour I have now on this and am open to any suggestions !!  I have been using Lynda.com, and making some progress.  But could do with some real life advice on my particular situation.  Thank you again for your time.

            • 3. Re: Relationship questions - newbie
              LaRetta_1

              I would have Equipment and MacBook in one table.  It does not hurt to have a few extra fields on either side which aren't used by the other type of equipment.  Then have a Category field which indicates whether Equipment or Mac.  In this way, the User can see ONE list of their rented equipment.

              I would do the same for Students and Staff ... use one table called USERS and have field Staff which is only a flag of 1 (set as value list called 'select' with only a value of 1) and display it as a checkbox.  In this way, students may become staff and staff may borrow equipment.  Again, it doesn't hurt to have a few extra unused fields when the result is a cleaner graph, easier design, and faster system.  It also increases your flexibility.

              What you have produced is a standard relational model (displayed like an invoices structure).  Here is a perfect demo of how to set it up:  http://fmforums.com/forum/showpost.php?post/309136/

              The 5th table you are missing is the invoice table (where you want to produce the 'form' of loaded equipment).  So let's translate from standard structures in the demo file to your situation:

                   Users table is same as Customers table

                   Equipment table is same as Products table

                   Lending table is same as LineItems table (I would rename it LendingLines)

              ... and your missing piece ...

                   new Lending table is same as Invoices table

              So now your LendingLines table would ONLY have these foreign keys:

                   EquipmentID (whatever type of equipment)

                   LendingID (new table's foreign key to tie all borrowed equipment to one lending 'form)

                   DateReturned

              Your new Lending table would have:

                   LendingID (primary key)

                   Date Borrowed, Duration of loan

                   StaffID (UserID of person lending the equipment)

                   BorrowerID (UserID of person borrowing the equipment)

              ... note that the UserID in StaffID and BorrowerID can be same if a staff member loans to his/her self. :^)

              ** UPDATE: I should mention that it is not always best choice to have Employees and Customers in the same table; much depends upon complexity and number of fields but I would rather split dissimilar fields off than split 'people' into different tables.  But I've found that people (no matter their purpose) should usually go into same table and not be split in same theory that we don't split other 'same' types of data.  People share things in common to be tracked:  DOB, Name, Primary AddressID (addresses usually split into related table), email, and so forth.

              If an Employee can purchase your products, and you have them in a separate table, then you must DUPLICATE their information into a Customer record.  If Vendors are in separate table and you hire a vendor, same issue.  Not only does it duplicate work (and go against normalization) but, if they change emails, you might change it in one place and not the other, again validating why we normalize data (data should exist in only one place) to begin with.  I've structured both ways but I keep coming back to 'people are people' no matter what their TYPE.

              Of course for Employees, sensitive information such as pay rates would be separate table.

              • 4. Re: Relationship questions - newbie
                DougLoader

                Thank you so much, your tips have helped me hugely.  I have made huge progress with my lending library style database.  However, as always, more questions....

                You mentioned my new Lending table should include UserID of person lending the equipment.  This is not the case in my instance, no particular person is lending the equipment.

                I have about 30 Macbooks and say 30 digital cameras (all in the equipment table) which I lend to students and staff.  I just need a way of monitoring who has what.  I have a database of Users (being students and staff) and a database of Equipment.

                Do I still need that lending lines table?  I'm not sure what is populating the lending lines table.  Also one key function of my database is if a user hires a piece of equipment.  It is then not available for others to hire.

                Thank you again for your time

                • 5. Re: Relationship questions - newbie
                  LaRetta_1

                  You mentioned my new Lending table should include UserID of person lending the equipment.  This is not the case in my instance, no particular person is lending the equipment.

                  You regardless should know who created the record to lend the equipment in case of questions.   You can also use auto-enter created by instead.

                  Do I still need that lending lines table?  I'm not sure what is populating the lending lines table.  

                  The lending lines table is exactly like the lines on an invoice (see the Invoices demo).  For example, if one person wants to borrow equipment, a Lending record is started.  The lending record is like an invoice - it is the agreement between parties and it holds: User who is borrowing, User who created the record, creation date of lender record (borrow date), during of loan (number of days before becoming overdue).  The lending lines allows them to borrow more than one piece of equipment at a time. 

                  Also one key function of my database is if a user hires a piece of equipment.  It is then not available for others to hire.

                  What version of FM are you using?

                  • 6. Re: Relationship questions - newbie
                    LaRetta_1

                    Well, no matter the version, this will work for them all.  I suggested that your DateReturned be in Lending Lines table because all equipment (from one Lending transaction) might not be returned at same time.  So here is how to know if equipment is available:

                    In your Lender Lines table, create a calculation called c_OUT (result is number):

                    not not DateReturned or you can use GetAsBoolean ( DateReturned )

                    This calc will produce 1 on all returned lender lines (has date in DateReturned) but will produce 0 if there is no return date.  Then below, while you are still in the calc box, uncheck 'do not evaluate if all referenced fields empty' IMPORTANT.  Now create another table occurrence of your Lending Lines table (call it something like UnAvail_Equip).  Join it like this:

                    Equipment::EquipmentID = UnAvail_Equip::EquipmentID

                    AND

                    Equipment::EquipmentID > UnAvail_Equip::c_OUT

                    From your Equipment table, the only lender line records which will relate will be those not yet returned.  For display on your equipment layout, place the UnAvail_Equip::EquipmentID.  Change the number format (format > number or Inspector > Data tab > Data formatting, as boolean.  Type OUT in the 'show non-zeros' and remove the value from the 'show zeros'.  If you need to test, for example via script, whether the equipment is out, it would be:

                    If [  not IsEmpty ( UnAvail_Equip::EquipmentID ) ]

                    ... true ... this equipment is not available

                    Else

                    ... false ... this equipment is available

                    End If