9 Replies Latest reply on Nov 30, 2015 3:35 PM by cyccyc621

    Need help: A rental system ERD

    cyccyc621

      Hi, dear all,


      I run a rental business. There're 7 kinds of items to rent, but each time they only need some kinds of them, and the quantities of each kind varies from 10 to 10000. All informations are recorded in paper sheets. Now I want to transfer them into filemaker-based solutions. But I am in trouble with this rental system ERD. In attachments, I pasted a picture about our system . Please help me to work it out.


      Thanks a lot.

        • 1. Re: Need help: A rental system ERD
          bigtom

          This is not too difficult but not extremely simple either.

           

          Do your clients reserve rentals for a specific day?

          Do you track each unit rented (I assume so)? How are you tracking them (barcodes, SN)?

          -For FIFO (First in First out) to reduce over use of a small set of rented items.

          -For repairs.

          One Contract can have many payments? The nature of the contracts is confusing (one long term contract or many single contracts) .

           

          InStock and OutOfStock would likely be TO's of Inventory table which has fields for stock status and repair status.

          Depending on how the contracts are working and if you are accounting on receivables vs actual payments may change how your financial statements are in the diagram.

           

          If you do not get enough help here from the community to get it done on your own, send me a message and I will work with you on it.

          • 2. Re: Need help: A rental system ERD
            bigtom

            You should have some sort of Order table. Maybe that is what Contracts is.

            • 3. Re: Need help: A rental system ERD
              TorstenBernhard

              A good approach is putting in a spreadsheet all the information you want to manage. Then split it up in individual spreadsheets, each holding information that belongs together in a dataset->record. A spreadsheet represents a table.

              Then decide how the tables shall be related. Bigtom gave good advise by saying: not too difficult but not easy either.

              I do not now how much an accounting error will cost you. If you don't feel easy with walking alone for designing a business application, consider teaming with an experienced developer for coaching.

              • 4. Re: Need help: A rental system ERD
                cyccyc621

                Hi, bigtom,

                 

                 

                 

                 

                Thanks for your reply. My business runs just like this:

                 

                 

                 

                1. In contract, customer and I will agree upon the details such as rental price, repair fee and compensation, etc. But the duration is unsure.

                 

                 

                 

                2. After I signed the contract, they will come to get those items in different days.

                 

                 

                 

                3. Each time they will get some types of them, maybe 3 or 5, each quantities are different.

                 

                 

                 

                4. During the rental, they probably return some or get more.

                 

                 

                 

                5. In the end of each month, I will send the monthly financial statements to them.

                 

                 

                 

                6. Before they finish their project, they will return my items in different times. When they return these items to my stock, we’ll pick out some of them to repair and some of the to discard. After repaired, return to my inventory.

                 

                 

                 

                7. Payment is long term process. It includes the rental fee, repair fee and compensation for lost and scrap.

                 

                 

                 

                8. Sometimes, my inventory is not enough for my customer, I must arranges them form other venders. Meanwhile, other vendor will come to borrow my items if they needed.

                 

                 

                 

                9. Sometimes, in my stock, I’ll keep some items for other vendors, meanwhile, they will keep some items for me.

                 

                 

                 

                10. In the end of every quarter, I’ll make a quarterly inventory audit, and adjust my inventory.

                 

                 

                 

                 

                I answer your questions:

                 

                 

                 

                1. My clients just give me a call one day before they need, but not a specific day. I will arrange items and transportation for them at that day.

                 

                 

                 

                2. When items in or out stock, I will make a record including customer name, types of items, each quantities, and the rental start date. The rental fee is based on the rental price*quantity*time.

                 

                 

                 

                3. There’s no requirement of FIFO or others for my items inventory.

                 

                 

                 

                4. Payment is long-term process. They maybe pay me several times in the middle, but mostly, they may pay me in several months after their project finished, even several years…I need track those informations.

                 

                 

                 

                 

                It’s a small business, but when I plan to transfer it into filemaker solutions, I found there are lots of relations and confused me. I am a new to filemaker, no so familiar with the ERD based on the filemaker requirements. So please help me. Thanks a lot.

                • 5. Re: Need help: A rental system ERD
                  cyccyc621

                  Hi, Torsten

                   

                  Thanks for your advices. I can not find a useful method to put all informations in one spreadsheet yet, but I will keep trying. Filemaker is new and no so popular in my country, although it has 30 years history. I realize its potential in my business, but it's no easy for me to get useful resources, because there's no experienced developer for coaching I can get in my county, and my native language is not English. I want to find some help in this community. Please help me. Thanks again.

                  • 6. Re: Need help: A rental system ERD
                    Storganise

                    Hi Cyc,

                     

                    I think your best way to start here is with a simple Invoice system that you can then add to...

                     

                    Tables:                           Fields: (where f_***_ID is a foreign ID used to relate the join table (look up foreign IDs for more details)

                    Customers                     Customer_ID

                    Inventory                        Inventory_ID, f_Customer_ID

                    Line Items                      LineItem_ID, f_Inventory_ID, f_Invoice_ID

                    Invoice ( Statement )     Invoice_ID

                     

                    Join them like this...

                    Customer_ID = Inventory::f_Customer_ID

                    Inventory_ID = Line Items::f_Inventory_ID

                    Invoice_ID = Line Items::f_Invoice_ID

                     

                    From this very basic setup, you can then create your fields as required

                     

                    Customers; Name, Address, Bank Details, etc...

                    Inventory; Name, Type, Make, Model, Price to Customer, Price to other Vendors, Is My Stock?, Is Vendor's Stock?

                    Line Items; Quantity, Repair Fee, Compensation Fee, Rental Start Date, Rental End Date, Collection Date, Needs Repair?,

                                       Needs scrapping?, Transport Cost

                    Invoice ( Statement ) ; Date

                     

                    Then create layouts based on what you would like to use the data for...

                     

                    Customers

                    Inventory ( My Stock )
                    Inventory ( Vendors' Stock )

                    Statement ( with a portal to Line Items for each individual item )

                     

                    You would do a lot worse than starting with Filemaker's own Invoice starter solution which gives you a lot of the above in a rather attractive set up already done for you.

                     

                    I hope this helps.

                     

                    James

                    • 7. Re: Need help: A rental system ERD
                      crystaldsmith

                      I'm going to tag Chris Cain Extensitech as I think he could give you some really helpful insight as he's built several solutions for this industry with needs similar to your own.

                      • 8. Re: Need help: A rental system ERD
                        cyccyc621

                        Hi James,

                         

                        Thanks for your advices, I'll try your methods.

                        • 9. Re: Need help: A rental system ERD
                          cyccyc621

                          Hi Crystal

                           

                          Thank you so much!