1 2 Previous Next 16 Replies Latest reply on Oct 8, 2009 2:36 PM by jonnyt

    table relationship advice

    jonnyt

      Title

      table relationship advice

      Post

      I was wondering if any of you would comment on the best table and relationship structure for the following scenario:-

       

      Im create a vehicle bookings database.

       

      1) A customer can have multiple bookings

      2) Each booking has a Vehicle Class (small, medium, large)

      3) Each Vehicle Class has a supplier (Hertz, Enterprise. Alamo, Dollar)

      4) Each Supplier has a Rate for that vehicle class(1 day=$10)

      5) Each Supplier has one or many vehicles in that class for the customer to rent

       

      Its baffling me for some reason, so any help would be much appreciated!

       

        • 1. Re: table relationship advice
          philmodjunk
            

          Tables Needed:

          Customers (1 record = 1 customer)

          Vehicles ( 1 record = 1 specific vehicle )

          Classes ( 1 record = 1 class for one supplier )

          Bookings ( join table linking Customers and Vehicles, 1 record = 1 vehicle booked for 1 customer )

           

          Relationships:

          Customers::CustomerID = Bookings::customerID

          Vehicles::VehicleID = Bookings::VehicleID

          Vehicles::ClassID = Classes::ClassID

           

          You may also find it useful to include a supplier table with links to the Classes table which in turn makes Classes another join table linking Vehicles and Suppliers.

          • 2. Re: table relationship advice
            comment_1
              

            Seems like you need:

             

            Customers:
            • CustomerID (PK)
            ...


            Bookings:
            • BookingID (PK)
            • CustomerID
            • SupplierID
            • VehicleClass
            ...


            Suppliers:
            • SupplierID (PK)
            ...

            Rates:
            • RateID (PK)
            • SupplierID
            • VehicleClass
            * Rate
            ...


            Vehicles:
            • VehicleID (PK)
            • SupplierID
            • VehicleClass
            ...

             

             















            • 3. Re: table relationship advice
              jonnyt
                

              thanks for the above advice.

               

              can you check and let me know the best table and relationship setup based on the following conditions:-

               

              1) a booking has a vehicle class (small car, medium, large) (vehicles Classes are set in stone)

              2) a booking has a supplier

              3) a booking has a per day rate based on the vehiclClass and the supplier suplying it

              4) a booking  is assigned a vehicle by that supplier

               

               

              5) Vehicles have a vehicles class

              6) vehicles belong to a supplier

               

               

              Help! im not sure where im getting stuck, im getting confused!

              • 4. Re: table relationship advice
                philmodjunk
                  

                The key to most of this is the Bookings join table which links Vehicle and customer records. As suggested by Comment, you'd want a rate field in this table to record the current rate for the selected vehicle. (since rates are subject to change, you'd look up this rate from your related class table at the time the vehicle is booked.)

                 

                "1) a booking has a vehicle class"

                Actually a vehicle has a class. This value is accessible via the relationship: Vehicles::ClassID = Classes::ClassID

                 

                2) a booking has a supplier

                Again, the booking record does not need to record the supplier, Vehicles::ClassID = Classes::ClassID can supply the data directly if you store supplier info in the classes table. More likely, you'd have a second relationship Classes::SupplierID = Suppliers::SupplierID to access needed supplier data.

                 

                3) a booking has a per day rate based on the vehiclClass and the supplier suplying it

                As stated above, this would be stored in Bookings, using Vehicles::ClassID = Classes::ClassID to look up the current rate data.

                 

                Note that this can be harder to understand in the abstract rather than in actual examples.

                 

                Keep in mind that using my example, ClassID is not the vehicle class label (small, medium, large...) you describe. Instead, it's serial number that uniquely identifies once class record from one supplier.

                 

                • 5. Re: table relationship advice
                  jonnyt
                    

                  we have multiple suppliers (alamo, dollar, budget)

                   

                   

                  there is a constant set of vehicle Classes (small car, medium car, mpv, truck)

                   

                   

                  each supplier can supply all of the vehicles above but at their own rates.

                   

                   

                  I want it so the bookng agent chooses the desired vehicles class, sees suppliers available for the class and then choose the availabkle vehicle for that class from the supplier.

                   

                   

                  Will your setup work in this scenario?

                   

                   

                  Thanks again.

                  • 6. Re: table relationship advice
                    philmodjunk
                       Yes
                    • 7. Re: table relationship advice
                      jonnyt
                        

                      Phil,

                       

                      This is what I now have:-

                       

                      Companies

                      companyID (PK)

                       

                      Bookings

                      bookingID (PK)

                      companyID

                      vehicleClassID

                      bookingDate

                       

                      Rates

                      rateID (PK)

                      supplierID

                      vehicleClassID

                       

                      Classes

                      vehiclesClassID (PK)

                      vehicleClassName

                      vehicleClassDescription

                       

                      Suppliers

                      supplierID (PK)

                      Supplier Name

                       

                      Vehicles

                      vehicleID (PK)

                      supplierID

                      vehicleClassID

                       

                       

                      Is that correct and also can you let me know what relationships I need to setup for this to work effectively?

                       

                      Im not sure why ive got so confused. I think like you said writing it down is more confusing that actually doing it in practise, if only I got get it all to work I would be weel away with finishing the rest of it off!

                       

                      Thanks again, and I think once you have helped me with the raltionships, ill be home free.

                       

                       

                       

                       

                       

                      • 8. Re: table relationship advice
                        philmodjunk
                           Since you are using Comment's tables and field lists, I suggest he take over explaining the implementation. That way we don't have too many "cooks in the kitchen here". :smileywink:
                        • 9. Re: table relationship advice
                          jonnyt
                            

                          the tables are the same as yours apart from the addition of the rates table.

                           

                          Are you suggesting I store the rates in the bookings table?

                           

                          I am so close now, I was hoping you could provide one last comment on the table relationships just in case comments doesnt come back on for sometime!

                           

                          Thanks very much if you can pass comment on this one last issue of relationships for the table I hae created and listed.

                          • 10. Re: table relationship advice
                            comment_1
                              

                            jonnyt wrote:

                            4) a booking  is assigned a vehicle by that supplier


                            I sort of suspected that, but you didn't say so at first. This means that the Bookings table also needs a VehicleID field.

                             

                             

                            Important:

                            There are two sets of relationships involved here:

                             

                             

                            1. The "core" relationships are:

                             

                            Customers -< Bookings >- Vehicles >- Suppliers -< Rates

                             

                            Vehicles >- Rates

                             

                             

                            I believe the exact relationships should be rather obvious: in general, each table has a primary key ID named after the table. These are linked to simlarly named foreign key fields in the other tables.  e.g.

                             

                             Bookings::VehicleID = Vehicles::VehicleID

                             

                            with the exception of Vehicles >- Rates which needs to be:

                             

                            Vehicles::SupplierID = Rates::SupplierID

                            AND

                            Vehicles::VehicleClass = Rates::VehicleClass

                             

                             

                             

                            2. The other set of relationships is merely for selecting the vehicle, AFTER narrowing down the choices by selecting the class and the supplier. Once the exact vehicle has been selected, the previous choices made for class and supplier become redundant - they could even be changed to contradict the chosen vehicle.

                             

                            The relationship to show the available vehicles for a booking would be:

                             

                            Bookings::SupplierID = Vehicles:SupplierID

                            AND

                            Bookings::VehicleClass = Vehicles:VehicleClass

                             

                             

                            Obviously, you will need several occurrences of some tables in order to implement all of these relationships.

                             

                             

                             

                            Notes:

                             

                            1. Strictly speaking, VehicleClasses should be another table - but since, as you say, these are "set in stone", you can use a value list instead and reduce the clutter in the relationships graph.

                             

                            2. There should be a Rate field in the Bookings table, and it should lookup its value from the Rates table (through the Vehicles table) after selecting the vehicle. This way, rates in the Rates table can be updated without affecting existing bookings.

                             

                             

                             


                            • 11. Re: table relationship advice
                              jonnyt
                                

                              comment - thanks ever so much for your help so far.

                               

                              Here is my relationship graph now in filemaker: http://www.assessors-unit.com/relationships.jpg

                               

                               You will note the double relationship representing

                              Vehicles::SupplierID = Rates::SupplierID

                              AND

                              Vehicles::VehicleClass = Rates::VehicleClass

                               

                              The only part im struggling to implement is:-

                               

                               

                              The relationship to show the available vehicles for a booking would be:

                               

                              Bookings::SupplierID = Vehicles:SupplierID

                              AND

                              Bookings::VehicleClass = Vehicles:VehicleClass

                               

                               

                              Could anyone elaborate on how to implement that in my relationship graph?

                               

                              • 12. Re: table relationship advice
                                comment_1
                                   No, that doesn't look right. For example, what is the purpose of linking Bookings to Rates by SupplierID?

                                I've made you a sketch of what I believe the RG should look like:
                                http://www.filedropper.com/vehicles
                                • 13. Re: table relationship advice
                                  jonnyt
                                    

                                  comments, thanks for the diagram.

                                   

                                  I think ive got it now.

                                   

                                  Would you kindly take a look at my relationship Graph now:  http://www.assessors-unit.com/relationships.jpg

                                   

                                  Let me know your thoughts.

                                  • 14. Re: table relationship advice
                                    jonnyt
                                      

                                    Ignore my last post.

                                     

                                    I have now got things up and running to an extent. My relationship graph is here http://www.assessors-unit.com/relationships.jpg

                                     

                                    I have created my add booking layout and I have added some Relationtional Value Lists.

                                     

                                    When I select the vehicle class, I only then only see the suppliers that can supply that class - this is what I want it to do.

                                     

                                    (I think the best use case is to select vehicle class, then supplier, then a vehicle from that supplier)

                                     

                                    So next i want to select the vehicle - but it can only be the vehicles that the chosen supplier has that also applies to the chosen vehicle class. (It doesn't!)

                                     

                                    My issues now are as follows:-

                                     

                                    Once I have chosen my supplier, I go onto choose my vehicle.

                                     

                                    My value list for VehicleID is set to use values from vehicles 2 and include only related values starting from Suppliers 2

                                     

                                    But its not displaying the correct vehicles based on my supplier choice and Vehicle Class.

                                     

                                     

                                    Do I need to switch the relationship graph around slightly? or where am i goign wrong?

                                     

                                    So close to the final working relational model!

                                     

                                     

                                     

                                    (My database file can also be downloaded from here)

                                    1 2 Previous Next