Rental Equipment DB with Daily, Monthly, Weekly Price

[Cousin Eddie]

I want to create a vendor DB showing rental equipment we typically use. I need a daily, weekly, monthly price column. Using the example of an air compressor, there are many sizes of air compressors, and there are many rental vendors that supply air compressors (furthermore, each rental equipment vendor likely supplies many sizes of air compressors). I need help understanding the many to many relationship of the equipment vendor and equipment item being rented.



Additionally, I would like to show which project has which rental item from which vendor.

My exposure to Access and databases in general is 4 days....so none.

[alansidman]

Here are two resources to help you get started.
http://sbuweb.tcu.edu/bjones/20263/a...sdb_design.pdf

http://www.accessmvp.com/strive4peace/

[Cousin Eddie]

Thanks for sending the docs about databases.

I continue to get hung up on how tables relate to one another. I have attached a screenshot of the relationships I have created. Does this look right? I am trying to say that one vendor can offer many pieces of equipment. Furthermore, each of these types of equipment have an associated daily, weekly, and monthly price associated with them. I have created a VendorPricingID because a vendor could offer different prices for the same equipment based on time of year, equipment in stock, or any other vendor determined option.

[alansidman]

Your relationships look ok to me. Here is a link on Junction Tables that will help to sort through this issue.

http://www.utteraccess.com/wiki/inde...Relationships)

[Micron]

The only thought I'd raise is if the rates belong in their own table. If you had to introduce new rates, it would require new columns, which many of us know is often a bad thing insofar as what it does to queries, forms, and often, their controls. Another approach might be to have the VendorID in the equipment table (instead of in Pricing) to tie equipment to a vendor and have a RateID in the pricing table. You tie the RateId to tblRates, which lists the rate type in rows (Hourly, Daily, Weekly, Monthly, Extended) instead of columns. I realize this would increase the number of rows in the equipment table, as you'd need a row for each rate type that you can rent it at. However, if the rate types for a piece of equipment change (i.e. a rate is added or becomes unavailable) you add a row or delete/deactivate it. No effect on any table, query or form design. Nor would there be any nulls in the rates data like you will have now for any rate that is not available. You will also have to store the rate/and or rate dollar amount in a table somewhere, be it PO or tblRentalHist or whatever. Imagine the impact on that table if you have to add a rate type to your existing design.
Edit: now you know why they say "normalize until it hurts".

[Cousin Eddie]

@Micron - Having a separate table for Rates makes sense, so I have included that. Should I delete the VendorPricing table? How does the Vendor relate to the rates tables - with a VendorPricingID or VendorID? A VendorPricingID would be a specific rate structure for a particular pieces of equipment based on something like time of year. The VendorID is just the primary key to the vendor. Additionally I have created a separate table for an Air Compressor. The thinking goes that there would be a separate table for each item of equipment: forklift, pressure washer, man lift, etc.

The addition of the Rates table and Air Compressor (more equipment specific tables will be added) table have me a bit confused in regards to relationships. How would you attack this?

[Micron]

You've introduced a few curves. I'll need time to think about it, but I may not be able to do anything until later tonight.
Edit: this IS for tracking what you rent, not what you rent out, correct?

[Cousin Eddie]

It could actually go both ways. I want to create a vendor database to track vendors we use and the prices we have from those vendors instead of calling the vendor every time we put an estimate together. Additionally, I want to create a DB for time & material projects. T&M projects would consists of both our company owned equipment and re-rental equipment form 3rd party vendors. Make sense? Any help you can offer is greatly appreciated!!

[ssanfu]

I would suggest you put the brakes on for a while. Your scope seems to be changing with each post.



I stole the following from a post by orange - it is better than anything I could write:
---
"I recommend you create a narrative in simple terms about your proposed database and application. You don't start building a database by identifying a Form with 3 buttons. Now, that may very well be your initial user interface, but that is not the database. It is not as easy as many think to clearly describe WHAT your business is/will be. Consider the "things" involved(entities), the particular characteristics of each(attributes) that are important for your business and how these things relate to one another (relationships). After you have the narrative, you can use pencil and paper (or whiteboard or cardboard or ...) to develop and refine a data model. Create some test data and vet your model.
<snip>

<snip>
I see too many people, who have the latest HW and Access, jumping in head first think the software will build the database. They have multiple issues and can't access the data required for X and/or Y, and typically it is an issue of basic table and relationship design. Their next "rationalization" is " I've got too much invested to go back and correct the design...". Don't get yourself in that predicament.

You have to do the analysis, build a plan, set priorities......Since you have the business, you know the processes better than anyone, so analysis should be second nature-- and providing you are familiar with database concepts -- designing the database should be an iterative process and a good learning experience for you. The data model and testing/vetting will result in a blueprint for your database. Review, test, adjust, and repeat until you get the results you expect. People are here and willing to help."
---



I would also offer these suggestions:

Use only letters and numbers (exception is the underscore) for object names.

Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated field in tables.
Do not begin object names with a number.

[Micron]

There is good advice in what ssanfu says. On the off chance that you don't take it and since I said I would reply with my thoughts, I will do so. Several things about your new design that actually, might be worse than what you had. First, adding a table for anything for which you already have tables for is as bad as adding fields in a table. A new query would be required for each type since you're not going to alter a query upon each use, selecting the correct equipment table. Second, you might be trying to add too many attribute fields for equipment (e.g. compressor) - many of which you would not be able to use throughout your forms. If your form had a 'trailermount' field, what would you do with this when you're renting a puller or chainfall? Your only fix for those two flaws is to have a form for each equipment type. Now how do we allow looking back at a rental history but not allow new rentals for a type because we now own that type. Yikes! On a side note, I also think many of your field names are too long.

Back to the attributes. If you really need to individualize them, my take is that you'd need an attribute table that links to equipment. However, if there are 3 vendors you can get compressors from who offer 5 rates and 10 attributes (cfm, gph, psi, fueltype [gas, LPG, elec, diesel], trailer, skid) you'll have 3*5*10=150 rows in your equipment table for this as opposed to the 15 I alluded to in my first post. I think a memo field to describe the attributes of equipment might be better, but I'm not sure if you should even attempt to tie these variables into the project at all. I foresee using all of this info to build a job cost estimate based on these factors, only to find that when you make the call, you have to upgrade to a higher cost item because the one you could get by with is not available. Here's what I threw together as a potential model based on your first post. It probably is not robust enough for your expanded project but I hope it gives you some ideas. You have to think in terms of entities when creating related tables, like Equipment, Vendor, Customer and tie them together via their relationship to one another. You probably already understood that you would not have employee name in tblRentalHistory (but emplID, yes) and that you would not create a table for each Customer, yet you were prepared to create a table for each equipment type. So I'm saying it's not easy at first, but as suggested, more thinking and planning would definitely be a good start.

[Cousin Eddie]

I really appreciate all the words of wisdom and advice all have given. I'm going to step back and write out on paper what it is I'm really after and try again at creating an Access DB for my equipment needs. Stay posted!