I work for a small charity and we provide a courier service in our city. In the past seven years, the data has been saved in an Excel workbook with lots (!) of VBA code.
I am about to leave the company and I have finally succeeded to have everything stored in an Access DB, as I could not guarantee for the data integrity in Excel once I have left. It took me a lot of effort to convince my boss to use Access in the first place. I have now transferred over 40.000 records and I need some advice on the table structure/relationships between them. Here is the model:
Let me explain in detail what we do and then my question:
A customer goes to a supermarket, buys his/her groceries and drops them off at the customer service desk. One of our couriers then goes to a shop and delivers the groceries. Shops are supermarkets, department stores and some smaller specialty shops. So I record the name of the shop and the customer and save it to Access. However, we also provide deliveries for flower shops and beverage/wine shops. As these shops tend to have one-time customers (like flowers sent as a present, or the occasional wine order), we decided not to record these customers. Instead, we set the shop and the customer to be the same and added the delivery address to a memo field in Excel. Apart from that, shops cannot have deliveries to themselves and rarely can end customers/consumers have deliveries from them them to another place. For this, I have a shop called 'others' with a delivery to 'other'.
Obviously, this can be done differently now that everything is in a proper database.
How would you deal with one-time customers?
Is there anything inherently wrong with the table setups? I decided not to normalise the tables too much as everything seems to work for now.
Bear in mind that we do not anticipate more than 250 orders a week.
Is there anything else that you need to know?
Thanks for your help.