JonAgnew Posted February 10, 2012 Posted February 10, 2012 Hey, guys. Fairly noobish here. I'm making a DB for the video production company I work for and I'm having some trouble figuring out the best implementation for a particular set of relationships. So, we are hired to do a JOB. For that job, we provide EQUIPMENT. Some of this equipment we own, and some of it we RENT from VENDORS. A job almost always has both owned and rented equipment, and we often rent equipment from multiple vendors for the same job. On the JOBS layout, I have a portal that should show ALL EQUIPMENT assigned to that particular job, regardless of whether its rented or not. The rentals are what are jamming me up here. If it was just our gear, I could just have JOBS->LINE_ITEMS->EQUIPMENT....but, with the rental gear, I'm not sure how to handle this. I had thought that "ownership," might be an attribute of equipment, eliminating the need for a separate table, but there are different attributes that i need to keep track of for rental equipment and our equipment. I played around with making a kind of muli-join join with a an additional line items table called ALL_ITEMS that is related to LINE_ITEMS_EQUIPMENT->EQUIPMENT and LINE_ITEMS_RENTALS->RENTALS, but I couldn't get that to work. Thoughts? Suggestions? I'm not opposed to Google, just not sure what to search for.
comment Posted February 10, 2012 Posted February 10, 2012 but there are different attributes that i need to keep track of for rental equipment and our equipment Could you elaborate on this part?
JonAgnew Posted February 10, 2012 Author Posted February 10, 2012 Sure. The differences are few. For our own equipment, we need to keep track of serial numbers (manufactuer serial, not FMP serials), repairs, and quantity. When added to a Job, a script is run checking for booking date conflicts. For Rentals, I need to keep track of vendors from whom we can rent this equipment (there are multiple vendors offerring the same item), from whom we rented for a specific job, and rental prices for each vendor (this price can change from job to job, depending on the job's budget and the vendor's mood). Booking conflicts do not occur with rental items and we do not need to keep track of serials, repairs, or quantity.
comment Posted February 11, 2012 Posted February 11, 2012 I am afraid I still can't wrap my head around this: first, how can you track both quantity and serial numbers on a single line item? About rentals: don't you record the actual vendor from whom you rented this item for this job? It seems to me that if an item can be at any time moved from one type to another (e.g. your own piece of equipment broke and needs to be replaced by a rental), you should make an effort to keep both types in the same table - even at the expense of having more fields than are required by any one type.
JonAgnew Posted February 11, 2012 Author Posted February 11, 2012 I'm sorry. I think I misunderstood what you meant. I was not referring to line item attributes but to equipment attributes. I do not need to track serials in line items. As per rentals, yes, I need to track the vendor of this item for this job. It seems to me that if an item can be at any time moved from one type to another (e.g. your own piece of equipment broke and needs to be replaced by a rental), you should make an effort to keep both types in the same table - even at the expense of having more fields than are required by any one type. Ok. I think something clicked when I read this. I was over-complicating the issue, and approaching the problem from the wrong angle. Forget Jobs and Line Items. What I really wanted was to keep the management of our gear separate from rental gear. I was thinking in terms of tables and relationships, which complicated everything. Now, I realize that the solution to my problem is to have separate layouts for our gear and rental gear. Thanks for your help, Comment. I've learned a lot from many of your answers to others' questions on the forum. Glad you're here.
Recommended Posts
This topic is 4730 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now