Database design advice - for equipment
Could somebody give me some advise on how I structure the database, if say we are buying in 2 items and these are sent out to clients in pairs - any 1 from product A with any 1 unit from product B.
I specifically am wishing to understand how I control stock levels. When an item is dispatched to a client, how would I automatically flag it as dispatched. Would it be just a case of using a flag to indicate if "S" is in stock and change to "D" for dispatched and then sum the total of all items by this flag. Guess if "S" ( those in stock and not dispatched) is less than our designated minimum reorder level, if should trigger an e-mail or system warning that more items need to be ordered.
The difficult part is how would I control this, if we have a main database in the office and a field engineer has stock with him. Once the items have been installed and a report signed by the client, the engineer needs to send an e-mail back to Head Office to confirm this. How can those details de imported and updated into the database. I do not want to have to host the database on a server - there are only 2 engineers in the field and I admin staff in HEad Office.
Comments and advice would be appreciated.
NB This is a database running on Windows 8.1 while the field engineers have iPads.