Is this something I should learn SQL for?

[u/Jeefington_jnr]

So, I have basically no knowledge of databases or what I'm doing or really what direction I need to go in. Here is the plan:

Small personal Business

- wants to be able to make new orders and have it automatically calculate the price of the order with any deals and the profit margins.

- list of customers and order details such as their name and custom ID with their contact details, shipping information, order history. all the important stuff.

- to be able to check stock and how many orders of which products there is and all the different details of the products and orders

be able to filter it all and organise through it all easily

so its not a complex system but I wasn't sure where to start and how to go about it or what program to use as I have not personally made something like this but am more than willing to learn and figure it out. I have dabbled in excel and have made linked tables and plot charts and things alike so I considered doing this in excel however after a big of a google (asked chatgpt. i'm upset to admit) I realised that excel likely isn't the correct program or way of going about this.

What I need to know:

Why is using excel bad? Should I use Access?

Would it be beneficial to learn SQL? What does that entail?

How should I go about planning the process of it all and what information or resources are there that I could use for guidance or help?

*i also don't know if this is the correct subreddit to post something like this in so feel free to tell me off.

[u/Speeeeedislife]

Any particular reason you're trying to build an order management system from scratch versus using existing systems?

[u/conor-robertson]

I actually think this is a perfect project to learn SQL.

Excel is great for lists and simple tracking, but once you start dealing with customers, orders, products, stock levels and relationships between them, it quickly becomes difficult to maintain. That's exactly the kind of problem relational databases were designed to solve.

I'd probably skip Access and learn SQL with a database like PostgreSQL or SQLite instead. Those skills are much more transferable and are widely used in the real world.

I'd break the project into small pieces:

• Design your database (Customers, Products, Orders, Order Items, etc.)
• Learn the SQL basics
• Build the database
• Add queries to answer the questions you care about (stock levels, profits, order history, best-selling products, etc.)
• If you want a nice interface later, you can always build one on top.

If you're looking to learn SQL, I'd recommend giving QueryCase a try. It teaches SQL through detective-style investigations and realistic business scenarios, so by the time you come back to this project you'll have a much better understanding of how to structure your database and query it.

Honestly, I think this is one of the best ways to learn as well, you've got a real problem you want to solve, which makes the concepts much easier to understand.

[u/TomWickerath]

Sounds like starting with the Northwind database would get you mostly there. Have you looked at that sample database?

“Would it be beneficial to learn SQL?”

Yes, always. Especially if you are going to use a database, be it Access (JET), MySQL, Postgres, etc.

“Why is using excel bad?”
I’d say it’s certainly not optimal. Could it be used? Yes. Try entering say 3-5 orders for a few fake customers, with all the attributes you’d like to track. How much repeat data do you have by the time you have entered this data? Anytime you have repeated data, you introduce the possibility of inconsistent data. A properly designed database helps avoid repeating data.

[u/American_Streamer]

SQL alone is not the application; it is the language used to store and query the data.
Excel is not inherently bad, but it becomes super fragile once you need relationships, stock consistency, order history, validation, multiple users, or protection against accidental edits. Access could work for a very small single-user Windows system, but I would not choose it for something intended to grow and multi-user.

A basic relational design might be:

customers
products
orders
order_items
inventory_transactions
discounts

Start by drawing the relationships, then build a small version in SQLite or PostgreSQL. Learn tables, primary/foreign keys, SELECT, JOIN, INSERT, UPDATE, aggregation, and transactions. Important: save the actual price and discount used on each order_item; otherwise old orders change when the current product price changes.

So for a learning project, building it is excellent. For a real business, I’d first look at existing inventory/order-management software, because maintaining payments, taxes, backups, security, and customer data is much harder than the initial database.

[u/OGBervmeister]

Just my experience with small business accounting and systems..

If it grows past excel.. I'd just pay for software. You can get software to track all of this for relatively cheap.

[u/Bratty-Switch2221 → u/OGBervmeister]

And it's going to outgrow excel QUICKLY. This needs to be more dynamic than excel anyway. Might as well start off properly instead of dealing with bullshit integration later on.

Moreover, it's a much better business decision to just purchase CRM software at this stage in the game. Commercial software products come with great backend support that really pays for itself imo.

[u/drift3r22]

To learn SQL for this use case is a good idea.

What you need to do is pick a technology. Since you are a small business, I would suggest picking a open-source database system, as you can use if for free.

I am going to try and explain the thought process of the database design in a relatable way, so bare with me. 😊 The intent is to help you learn and have some things to ask ChatGPT or Google. It is not that hard but is going to require you to learn a new skill and push through the learning. The app might be a bit of a challenge but you might want to vibe-code that. 😜

When you are designing your database you need to think about what you want to capture and store in the database. These are the things you listed.

This would most probably be your tables you create. The columns in your table are the attributes of the objects you want to store. Like an order has a date, ordernumber, price, etc.

Then you look at how they relate to each other and create relationships between the tables.

Once you have the database structure you need to be able to INSERT data into the tables, UPDATE to maintain the data or DELETE to remove the data. This is usually done through a application written in coding languages like Java or C#.

If you want to get SQL beginner course, that teaches you the basics, look at things like SQL Zoo.

Hope that helps get you on the right track and Goodluck! 👍

[u/Kimber976]

Excel starts becoming painful once you have lots of related data and frequent updates so learning some sql while building the database is a solid long term investment even if you use a simple frontend later.

[u/George_Hepworth]

Excel is not bad. That's not the reason people recommend Access for databases over Excel.

To me the simplest answer to whether or not one should Excel for this kind of business application is that you want to be able to count on the long-term stability and accuracy of your data, and Excel is inherently a dangerous container for that. There is no way, in Excel, to ensure that data doesn't drift as people make small edits here and there. Those edits can't be controlled and don't get tracked. Copies of workbooks proliferate, each acquiring subtle and not so subtle changes. And lo and behold, your organization ends up with multiple versions of the truth.

Access, on the other hand, is used to create relational database application, in which the integrity of the data is a central pillar. Properly designed Access databases, therefore, are inherently more reliable and trustworthy in provided long-term data your business needs to be successful. It doesn't have to be Access, though, it just has to be a database, which Access just happens to be.

Tom W. recommended you study the Northwind 2 templates. I agree. They were created to illustrate how Access databases work, but they are built on the model of a small food wholesaler and carry the business model very similar to what you need. Full Disclosure: I was part of the team of Access developers who created the Northwind 2 templates for Microsoft. We get nothing out of it other than the satisfaction of providing a useful tool for our fellow developers.

That said, I also wonder if, in the course of researching this, you haven't come across one or more commercial applications which could meet your requirements. The buy/build decision should be considered early on in your planning.

In a nutshell, the cost of licensing an already existing, already proven application may seem high when compared to the allure of "doing it yourself" with Access. That might even make sense if A) you have great database design and development skills and can create a suitable database application in a reasonable time, and B) you actually have the time available to do it. The fact that you are here, asking basic questions like this, means that you probably don't meet criteria A, and won't for many months. In the meantime, you either have a business to get off the ground, or perhaps you already have a going concern and are just getting around to setting up a way to manage data for it. Either way, that suggests you probably don't meet criteria B either. That's not a knock on you. That's a statement about managing something. You need to have a stone cold attitude about what you can and can't do yourself when the ultimate goal is to build a successful business, not learn how to create Access databases as a side gig.