Need advice for data model for customer-orders
Hello everyone
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.
Massimo
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.
Massimo
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your feedback. Regarding Pat's post.
1) I was contemplating merging the customer and the shop table .. However, why would I want to do that?
I mean, my might ask me why I put everything into one table?
2) I thought that it was always good to have an address table so that in case the customer changes his or her address, I can always look back and see where the delivery went to. Having said that, and looking back at all these years I have been at that charity, that was never the case. As 60% of our customers are elderly people, if they moved .. they moved to a care home or passed away. As the addresses might be duplicate in the customer table, does this not break the basic rules of normalization .. or can we sometimes ignore normalization?
Shops have a billing address, as we sell special stamps that are put on delivery slip.
Having a shop address and a billing address necessitates a separate table for addresses?
4) I do not know how to merge the two together.. the date and the time. I'm sure it is something basic.. maybe someone can give me an idea.
5) we have never recorded the pickup time - just delivery time.
6) No need to track who made the delivery. Although I think it is important, my boss disagrees.
7) All deliveries are done by bike - e-bikes actually
8) city table is a good idea .. and street table .. I have one somewhere with 600+ street names
1) I was contemplating merging the customer and the shop table .. However, why would I want to do that?
I mean, my might ask me why I put everything into one table?
2) I thought that it was always good to have an address table so that in case the customer changes his or her address, I can always look back and see where the delivery went to. Having said that, and looking back at all these years I have been at that charity, that was never the case. As 60% of our customers are elderly people, if they moved .. they moved to a care home or passed away. As the addresses might be duplicate in the customer table, does this not break the basic rules of normalization .. or can we sometimes ignore normalization?
Shops have a billing address, as we sell special stamps that are put on delivery slip.
Having a shop address and a billing address necessitates a separate table for addresses?
4) I do not know how to merge the two together.. the date and the time. I'm sure it is something basic.. maybe someone can give me an idea.
5) we have never recorded the pickup time - just delivery time.
6) No need to track who made the delivery. Although I think it is important, my boss disagrees.
7) All deliveries are done by bike - e-bikes actually
8) city table is a good idea .. and street table .. I have one somewhere with 600+ street names
While the shops might have once-off deliveries, I would still set them up you are not really saving anything apart from (maybe) a couple of compulsory columns.
Thats where the "Guest" or "One-Time" flagging can help when deciding what information is compulsory or not.
I probably would not combine Shops and Customers. Customers belong to the Shop and need delivery details to a varying degree.
Shops need their own identities because they are the clients you are providing the delivery services for. So the contact and relationship is different and alleviates some of the concerns in keeping (more formally than an excel memo) delivery details.
That way billing addresses, along with physical address, could well remain in a slightly denormalized structure with the Shop.
I think you have done pretty well with the designs and a reasonably pragmatic approach for ease of implementation as opposed to going all out for a normalized design taking efforts for little practical gain in your environment / stage of evolution....
Thats where the "Guest" or "One-Time" flagging can help when deciding what information is compulsory or not.
I probably would not combine Shops and Customers. Customers belong to the Shop and need delivery details to a varying degree.
Shops need their own identities because they are the clients you are providing the delivery services for. So the contact and relationship is different and alleviates some of the concerns in keeping (more formally than an excel memo) delivery details.
That way billing addresses, along with physical address, could well remain in a slightly denormalized structure with the Shop.
I think you have done pretty well with the designs and a reasonably pragmatic approach for ease of implementation as opposed to going all out for a normalized design taking efforts for little practical gain in your environment / stage of evolution....
As you won't go that deep into normalization, the model is good.
The only thing I would reconsider is the relationship between deliveries and customer addresses. Here I would recommend to use a combined foreign key over CustomerID and AddressID. Otherwise it would be possible to deliver for customer A to the address of customer B.
Also a separate address table makes sense, I can imagine customers having two addresses (private and business). But: don't use this table for historic evaluations. In this case you need a separate dimensional model to track the history correctly.
I would also keep the shops separate from the customers, because they are different entities.
The only thing I would reconsider is the relationship between deliveries and customer addresses. Here I would recommend to use a combined foreign key over CustomerID and AddressID. Otherwise it would be possible to deliver for customer A to the address of customer B.
Also a separate address table makes sense, I can imagine customers having two addresses (private and business). But: don't use this table for historic evaluations. In this case you need a separate dimensional model to track the history correctly.
I would also keep the shops separate from the customers, because they are different entities.
ASKER
As each customer only has one address, there is nothing wrong with having them stored in the customer table?
Each shop has a (physical) address and a billing address which can be different - that's where two separate tables makes sense.
As far as normalization is concerned, I guess it just shows me that what I learned at university is what can be done - or should be done when having a huge DB. Considering that this DB is small (40'000 entries / 1800 customers) there is nothing wrong with keeping entities together if it does impact the performance of the DB? I also find it easier to create a form for customers if each customer only has one single address.
I do have one last question:
How do I merge the date and the time together into a single new column?
Each shop has a (physical) address and a billing address which can be different - that's where two separate tables makes sense.
As far as normalization is concerned, I guess it just shows me that what I learned at university is what can be done - or should be done when having a huge DB. Considering that this DB is small (40'000 entries / 1800 customers) there is nothing wrong with keeping entities together if it does impact the performance of the DB? I also find it easier to create a form for customers if each customer only has one single address.
The only thing I would reconsider is the relationship between deliveries and customer addresses. Here I would recommend to use a combined foreign key over CustomerID and AddressID. Otherwise it would be possible to deliver for customer A to the address of customer B.Thanks a lot for this information - this is certainly not going happen.
I do have one last question:
How do I merge the date and the time together into a single new column?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I would consider my to be an intermediate developer. I am familiar with VBA (coming from Excel) and I studied database design at university (two semesters) so I am in the midst of creating a nice front end. I will merge the date and time. I will have some questions about the userform .. but let me try it first before asking the question :-)
Thank you for your assistance! It helped me a lot.
Thank you for your assistance! It helped me a lot.
userformThis is the term used to refer to forms in other Office applications. Access does NOT use userforms. It uses Forms which are different. I don't mean to be pedantic but if you search for userform, you will get bad examples that you cannot use.
ASKER
yes sorry - just forms! userforms is Excel
You could have a customer type "Regular" and "guest", and then treat them the same, except you clear all guest entries older than say 1 week.
But then again, you could also record them as regular customers? What good reason is there to not keep this on record I might ask?