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:

db.PNG
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
Massimo ScolaInternshipAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
The model seems to be good.

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?
1
PatHartmanCommented:
Now that you have converted to a relational database, you don't have the size constraints you have in a spreadsheet.  Therefore, storing one time customers shouldn't be a problem.  If you want to expand your services, you would want to market to these people so keeping their information will prove valuable in the future.  As to the design, I'm a purist and so there are issues.

1.   I wouldn't use separate tables for shops and customers.  Keeping a flag to identify shops should suffice.  You could also add a "one time" flag if you want so you can identify one time customers.  Keep the Company name in the LastName field if you combine the tables rather than adding a CompanyName column.
2.   Separating the addresses into another table only makes sense if you can support multiple addresses per shop/customer and to do that, you probably need a role.  For example, the billing address is frequently different from the address where you go to pick up stuff.
3.   If you stay with the separate tables, you need to make the column names consistant.
4.   Most applications do not separate date and time but it is not wrong per se.
5.  Don't you need both Pick up and Drop off times?  If this can span days, you would also need two date fields.
6.  Do you need to track who made the delivery?
7.  How about whether it was by bike or car?
8.  I would create a City table to ensure that city names are spelled consistently.

In summary.
Combine Shops and Customers.
Eliminate the address tables unless you actually intend to manage multiple addresses correctly.
0
Massimo ScolaInternshipAuthor Commented:
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
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Mark WillsTopic AdvisorCommented:
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....
0
ste5anSenior DeveloperCommented:
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.
1
Massimo ScolaInternshipAuthor Commented:
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.  

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?
0
PatHartmanCommented:
1.  You would use a single table because most of the fields are common.  Also in many situations (I can't be sure about yours) indidividuals might occur in both tables.  A common example is teachers and students.  Teachers can take classes and so are students and you don't want to end up having to manage and synchronize two separate records for the same individual.
2.  If you need history, fine.  But the table is not set up correctly for history either.  Just because some customers live at the same address doesn't mean that they should share the same address record.  Even as co-residents, they would have different unit numbers so technically the addresses are different.  If you have them share the same address record (you would need to change the schema to make this viable), you will have to be exceptionally careful if one client moves because you can't change a shared address.  You would need to make a new one.  Although this is certainly possible, it becomes very complex and so I wouldn't recommend it in this case.
3.  The decision to separate billing and shipping addresses into an address table tends to be one that is dependent on the application.  Technically, the correct decision is to use an address table with separate rows.  Here also are splits in implemntation.  Do you have two FK fields, one named BillingID and the other named ShippingID in the Customer table.  You would probably choose this implementation if the vast majority of the time the two addresses are the same AND you could say with great certenty that you would NEVER, EVER had more than a single ShippingAddress.  However, again, the technically correct implementation is to use a RoleID in the Address record so that one address is flagged as Billing and the other is flagged as Shipping.  This would cause the address to be "duplicated" if the addresses were the same.  So you might add a junction table so that it has CustomerID, RoleID, and AddressID and the addresses are in a separate table but identified only as AddressID possibly with a FK of CustomerID to make the pick list shorter.  Addresses can be very simple or very complicated and this is one place where the technically correct implemation might not be chosen due to its complexity.  One of my clients was Readers' Digest so you can imagine the lengths to which they went to break addresses appart to minimize shipping costs and data storage but most implementations are much more streamlined since they don't involve a lot of mail or shipping.  Your application is all about shipping but I don't get the sense that it is anything like what RD or Amazon would need.
4.  A Date field automatically contains a time component if you just type one into the field.  For applications like this, they are sometimes separated so you can provide help for the data entry to avoid a user haveing to type 3/18/18 5:24 PM into a single box.  As I said, it's not wrong and if it helps your data entry than split them.
5.  Just because you have never done it doesn't mean that you might not want to.  Do you care how long a delivery takes?  You'll never know unless you have both a start and stop time.
6.  You can put the field in the table and on the forms but hid them if you think that decision might change in the future.
7.  OK.  Could that change?  Might there be walkers?
8.  If you deliver in multiple cities, does that mean you have different warehouses?  It doesn't seem like you would bike from Bridgeport to Stamford.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
I am going to fly in the face of normalisation / database design wisdom.

>> As each customer only has one address, there is nothing wrong with having them stored in the customer table?

Yes, can be done - drawback is having to create another customer if the address changes - so put a date in there
 
>> Each shop has a (physical) address and a billing address which can be different - that's where two separate tables makes sense.

Why ? Can just as easily keep in the Shop table

While either scenario is not fully normalised, given the time and moving on, do what is easiest to achieve in the current circumstances.

If it makes sense to seperate Shop and Addresses, then using that same logic, why doesnt it make sense to separate customers and delivery address ? Conversely, why separate the addresses if it is more convenient to keep them with the entity ?

More important to be consistent in your design. Remember this is a migration from Excel to a more robust database environment so you are going to be better off almost regardless.....

Once there, it is relatively easy to split out Address tables. In the meantime, your forms can be created quickly and easily if they deal with the table they gather information for.

I know that will make my fellow experts cringe, but there needs to be a certain amount of pragmatism given your circumstances.

How much experience in MS Access do you have ? If you are comfortable in developing, then develop the solution in accordance with your comfort level and experience.

In Excel : If column A is date (and formatted as such) and column B is time (likewise formatted) then create column C = A2+B2
date	       time         	datetime
3/01/2018	11:23:00 AM	3/01/2018 11:23
3/01/2018	11:30:00 AM	3/01/2018 11:30
3/01/2018	11:45:00 AM	3/01/2018 11:45
3/01/2018	12:15:00 PM	3/01/2018 12:15

Open in new window

You can format the new datetime columnC accordingly if you want.

In Access : the datatype is DATETIME  https://support.office.com/en-us/article/create-a-field-to-store-dates-and-times-a9d057b0-78ac-4705-a274-7de7f4d24d14

To display separately use FORMAT : https://support.office.com/en-us/article/format-property-date-time-data-type-3251a423-3dd7-446e-be65-c7293eddbb43
0
Massimo ScolaInternshipAuthor Commented:
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.
0
PatHartmanCommented:
userform
This 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.
0
Massimo ScolaInternshipAuthor Commented:
yes sorry - just forms! userforms is Excel
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.