1 2 Previous Next 28 Replies Latest reply on Feb 3, 2012 7:57 AM by FMnoob

    Car Rental DB

    FMnoob

      Title

      Car Rental DB

      Post

      Hello all. Trying to work on my very first Database under Filemaker. I just purchased the software not long ago, but I will be honest and say that I am a little overwhelmed at the capabilities that this software has. I am trying to run a Database for my boss' car rental business. I got started creating a few tables and fields, made some relationships but don't want to get too far and have it all wrong. What I am trying to figure out is what would be the best way to log in payments. Any ways of adding interest to a charge? Any help will be greatly appreciated. Let me know if I can help you with anything. Here is what I have so far:

        • 1. Re: Car Rental DB
          philmodjunk

          Your relationships look good. Rent would be a join table between customer and vehicle.

          A layout based on rent could use a portal to payments to log a payment. What are the "rules" for charging interest on these payments?

          • 2. Re: Car Rental DB
            FMnoob

            Thanks for the quick response! So tables and relationships look good? I just want to be able to get the program to view the rents, payments, reservations and things like that in an easy manner. As far as the interest charged, it's not necessarily interest, but kind of like a limit on the miles they can travel per day. like say they rented the vehicle for 2 days, they have 50 free miles per day. If they go over the 100 allowed, they would have to pay a rate per each mile that they go over. If the vehicle is going to be rented for more than 3 days then the mileage is unlimited. Again thanks for the response. Hope I can get the ball rolling

            • 3. Re: Car Rental DB
              philmodjunk

              What you have right now is the "backbone" to which you'll very likely need to add additional related tables before you are done. This wont' be a project completed in a single afternoon.

              Reservations can be tricky when it comes to determining whether a car is available to be rented for a given interval that might or might not overlap an existing reservation's date in and date out dates. (If vehicles cannot be reserved in advance, this becomes simpler to manage.)

              I'm assuming here that a payment can only be applied to a single rental. If you want to support accepting a single payment for multiple rentals, an added table is needed to support the resulting many to many relationship.

              Figuring mileage charges (and any other charges such as charges for an empty gas tank, insurance premiums, tax, etc) all complicate the process of computing a rental fee. You'll need to identify the "source" of each piece of data. Is the charge specific to a given vehicle? The the data for that charge needs to be recorded in vehicles. Specific to a given location such as sales tax? Then you need another table for tax rates. Specific to a given promotion? Then you need a table for that as well. A key factor in all of this is that you want to copy all these items into matching fields in the rental record--usually looked up value field options are used for this. This is so that changes in any of these items (new promotion, new tax rate, etc. only affect new rental charages and do not cause existing rental records to compute a new fee.

              • 4. Re: Car Rental DB
                FMnoob

                Wow, you have so much knowledge on this bro. Thanks!! Well let me see what I can work with today and I will try and post pictures of what I have. So far I think I understand what you are telling me, and yes I know it will not be an easy task. Especially when I have to walk in and out of the office and away from the computer.

                You are right about all of the different charges that will have to occur, pretty much all of the charges will be the same for every vehicle. Insurance is already included so that will not be an option. So basically every little option I want to add has to have its own table. Then everything will be recorded to that table in case I need to look at what has been charged? What would be the easiest way to control primary and foreign keys. This is where I start to get a little confused on how much relationships can do.

                The value fields go accordingly with the tables that I will create for the necessary charges right? (tax, gas, mileage etc..)

                 

                Thanks Phil. Owe you a few cups of coffee or some brewskieee Smile

                • 5. Re: Car Rental DB
                  FMnoob

                  If reservations will make it much more complicated I am just thinking of keeping up with that on a planner, and just have some type of control over the rents per month and things like that. This Database will be pretty much just for us to keep up with the transactions in a more organized manner. So let's forget about reservations, thanks phil!

                  • 6. Re: Car Rental DB
                    philmodjunk

                    So basically every little option I want to add has to have its own table. Then everything will be recorded to that table in case I need to look at what has been charged?

                    Not necessarily. You can have a single table with a single record where each field in that record records a different item needed in order to compute the rental fee.

                    Take a look at the looked up value field option. This option can copy data from a field in this single record "rate" table into a field in the Rental table so that a calculaiton defined in the rental table can compute the rental fee.

                    What would be the easiest way to control primary and foreign keys.

                    Best design approach is for Primary Keys to always be generated internally by the database and be devoid of any added or "encoded" meaning. If you use data, such as the VIN number from the vehicle as the primary key, you put your data integrity at risk as you now must rely on 100% accurate data entry of that value. If it is mis-entered and you then create records in a related table, fixing the error "breaks" the connection to the related records--something that you can avoid if you use an internal ID. Likewise, externally supplied ID's and ID codes that include "meaning" are vulnerable to the powers that be decreeing changes to the content and format of the ID code--again something that can cause problems for your related data. VINS, license plate numbers and other identifiers from outside your system and those that encode meaning in some way can still be entered as data in your database, just don't use them as a primary key and use it in relationships. In FileMaker, primary keys are almost always defined as auto-entered serial numbers.

                    • 7. Re: Car Rental DB
                      FMnoob

                      Not necessarily. You can have a single table with a single record where each field in that record records a different item needed in order to compute the rental fee.

                      When you say this, I understand that I will have a table (Options) and under that table I will add fields that have the different add-on charges in order to calculate the rental fee. EX:

                      [Options]

                      Gas not full

                      Over mileage rate

                      Tax

                      --------------

                      I see what you mean by primary keys and foreign keys being vulnerable if it is not entered exactly as it is supposed to be. So in the option where I can add a field and I choose to calculate the value, would there be a way that I can use the operators to use primary and foreign keys to give me a unique primary key for each rent depending on vehicle and customer?

                      As far as payments go, it would be nice to have an option to be able to have 1 customer pay for different rentals. I think payments is where I will have the most trouble with as I dont quite understand how to put together a layout or portal to show me the given payments over a rent or payments given by a customer.

                      • 8. Re: Car Rental DB
                        philmodjunk

                        Your primary key for  each rental record should be a serial number. Nothing more than 1, 2, 3 etc. This number need not be visible to the user on any layout though sometimes developers make it do double duty as an invoice number.

                        Then you add foreign key fields for linking this record to a customer and a vehicle. Serial number fields in the Customer and Vehicle tables serve as Primary keys for those tables. That gives you these relationships:

                        Customers::__pk_CustomerID = Rent::_fk_CustomerID

                        Vehicle::__pk_VehicleID = Rent::_fk_VehicleID

                        Rent::__pk_RentID = Payments::_fk_RentID (and keep in mind that this last relationship is not enough if you accept payments that pay off more than one vehicle rental.)

                        • 9. Re: Car Rental DB
                          FMnoob

                          Ok I see what you mean. Let me work on it and see how far I can get. Thanks phil!

                          • 10. Re: Car Rental DB
                            FMnoob

                            Well I went over these a few times just trying to get rid of many to many relationships. I hope I am on the right track.

                            Phil, I believe that on your last post my relationships do look like that?

                            • 11. Re: Car Rental DB
                              philmodjunk

                              You have a many to many relationship between Customer and Vehicle. This is what you should have. Rent functions as a "join" table linking the two and making the many to many relationship work.

                              You don't need two tables for payments. I would remove extraPayments here. With just the single table for Payments, you can log as many payments against a single vehicle rental as you need without using an "extra payments" table to do so.

                              I'd link Rates directly to the Rent table and use the X operator so that one record in Rates matches to all records in Rent. That way, fields in Rent can copy the rate data that is current at the time the Rent record is created. If you find you need to change a rate--say the government changes the tax rates on you, you can change the value in Rates and this will only affect the values used in any new rent record while leaving existing rent records--which should use the original tax rate, unchanged. To change the relationship operator from = to X, double-click the relationship line between the two table occurrence boxes.

                              • 12. Re: Car Rental DB
                                FMnoob

                                You have a many to many relationship between Customer and Vehicle. This is what you should have. Rent functions as a "join" table linking the two and making the many to many relationship work

                                So my vehicle and customer relationship is correct.


                                When you say use the X operator, what value would I use to make the relationship to rent? I changed the "Payment_ID_fk" in rates to "rent_ID_fk" and it shows as 1 to many when I make the relationship. But then when I change to the X Operator it changes it to Many To Many. I never want to have many to many correct?

                                • 13. Re: Car Rental DB
                                  philmodjunk

                                  Don't get too hung up over whether or not the relationships map shows crows feet or not on the ends of the relationship lines. In the case of the X operator, the many to many relationship you get is exactly what you want here. With the X operator, you can choose any two fields as the match fields as the actual values in these fields do not affect what records match to each other in the relationship. You can even define dummy fields in both tables, connect them in the X relationship and then delete the dummy fields. The relationship will still work.

                                  Those little indicators are a case of FileMaker "guessing" whether or not the relationship is one to one, one to many, many to one or many to many. It's usually a function of whether or not the field is an auto-entered serial number or has the "unique values" validation rule specified in field options--though a field that has global storage or a field that is an unstored calculation will also have an effect.

                                  It's quite possible to have what looks like a many to many relationship between two tables that is really one to one in actual use if you don't specify one of those field options so this won't necessarily tell you that you have the wrong relationship.

                                  • 14. Re: Car Rental DB
                                    FMnoob

                                    Ok I see what you mean. I was just worried that I wasn't doing something right. I seriously need to get some studying done on how everything in FM works. For now I have just been playing around seeing what each thing does but it is a very broad program with just about any and every option you want to add. So for the Rates Table, I wouldn't need a primary key would I? I know it sounds weird because I know just about every entity needs to have its own primary key, but I'm thinking using the X operator clears me of that rule. Here it is modified:

                                    Is there any more work I need to do as far as tables and relationships go? I don't want to get started on a layout because I want to be sure that the backbone of the DB is well built.

                                    1 2 Previous Next