I am working on what was to be a simple purchase order / inventory application. It's gotten complicated....
There are 15 locations using the application, about 100 vendors and 2000 items used. Not all vendors sell all items, not all items are used at every location, and some items are sold by more than one vendor.
The users location can be determined at the time of login (database field in the users table contains locationID). I need to filter the items so that the user only has access to items used at their location. I'm wondering if it's easier to filter by item (items_used table with locationID, itemID and flag for 'y' or 'no') then join this with the vendors table (vendor_items table with itemID, vendorID, and flag for 'y' or 'n') or grab the vendors list first then the items_used list. Does it make any difference?? Is there an easier way??