Windows 7 64bit 
Access 2007
Hello,
I'm trying to find the cleanest design for a couple of tables. Hope fully someone can help. The key table going to be "Transactions" and this is where all transactions will be recorded for a reuasable widget that Company ABC has in their inventory of assets. This widget has a barcode which will remain with it for it's lifetime. There will be several fields in the table pertaining to who did what with regards to scanning the wdiget's barcode and when. The main area I want help to keep as smooth and clean as possible is this...
Each widget actually goes out to a customer but at some point it returns to the company where it will be cleaned and reused. Some widgets may be taken out of service for various reasons, which includes bening sent to a recycler at the end of it's life cycle.
My thoughts are to have a boolean field in the transactions table which allows a user to put a widget in an eiligibility status of active or inactive. Then another field to give the widget a status which would be filtered based on eligiblity. If the widget is active it will have one set of statuses and anoter for inactive. A status of an active widget could be that it is "out" (with the customer) or in at the company, however, when it is in at the company it will be in different states (i.e. dirty, cleaned, internal use, damaged, etc)
Would it be better to assign a widget to a location while it's at the company and have each location define it's state while at the company, meaning it could be assigned to a cleaning location (meaning it's being cleaned) or a dirty location (meaning it's waiting to be cleaned).
It seems like using locations rather than states would give me more bang for the buck because not only does it tell me what state my widget is in but also where it resides.
tblTransactions
WidgetType
Active - Yes/No
StatusID
tblStatus
ID
Status
Any ideas would be greatly appreciated!
Reply With Quote
