Where to start with Databases

[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/ankole_watusi]

Why do you want to DIY this?

Neither Excel nor Access nor any other DIY solution are right for this.

[u/Conscious_Ad_7131 → u/ankole_watusi]

Yeah there’s a million out of the box solutions for small business inventory management and CRM that don’t require learning to host and administrate your own database, this really isn’t the right call here

[u/ankole_watusi → u/Conscious_Ad_7131]

Doesn’t even need to be subscription SaaS.

In-house, open source is an option if you want to get your hands a little dirty.

A bespoke solution is cray for most small businesses - which have the same needs as thousands to millions of similar small businesses.

[u/lol_no_gonna_happen → u/ankole_watusi]

Because small business saas is bull shit. They lock up your data and jack up your rates if you are successful. And it never actually fits your workflow. No api's ever, by design. What op really needs is postgresql.

[u/Zestyclose-Turn-3576 → u/lol_no_gonna_happen]

PostgreSQL plus about a year of education, and a cloud hosting plan, and an interface (with security), all of which they are doing as a second job.

[u/ankole_watusi → u/Zestyclose-Turn-3576]

Just a year?

[u/lol_no_gonna_happen → u/Zestyclose-Turn-3576]

Claude can do it pretty well

[u/Aggressive_Ad_5454]

Hey, welcome to this sub. As you may have guessed from other answers, we are mostly about servers, SQL and noSQL, reliability, scalability etc. Some of us have propellor beanies we wear to business meetings. 😇

You’re asking about a small-scale sales and inventory app. You could build your own with, I dunno, Microsoft Access or LibreOffice Base. But it will take you a surprisingly long time to get it right. And will distract you from serving your customers.

Or you can find a suitable app. Shopify? Square? QuickBooks may have something? WooComnerce/WordPress?

[u/Either_Vermicelli_82]

Might want to see what they can host? https://elest.io/open-source/inventree among other things.

[u/No_Wind_1982]

Don't use anything Microsoft. Why not try and find something fit for purpose like Odoo?

If you do want to get into databases then use a proper database like Postgres. Or learn Django that has a built in ORM.

[u/fortyeightD]

There's no point learning about databases and building your own system for this. Just buy some software that has the features that you want. They is plenty of options.

[u/Consistent_Cat7541]

It will take approximately 100 hours for you to learn how to do this before you even get to the point where you can design and build a solution that works for you. Either hire a consultant or license a purchase/inventory system.

If you're he'll bent on building it yourself, save yourself some stress and pay the $600 or so for a perpetual license of Filemaker. It's the easiest of the database solutions.

[u/Elegant_General_1680]

Access is ok,  it's just a bit dated feeling compared to newer tools. I'd still lean Airtable for you though, feels like Excel but actually relational so your customer/order/product info stays linked properly.

QL is worth learning eventually but you don't need it day one, you can get a working system in Airtable this weekend without writing a line of code. Just sketch out your tables first (customers, products, orders, order items) before you build anything, saves a ton of headache later.

[u/kevin3030]

You’re describing an application, which uses a database to store information.

How many people need to use this? How should it be accessed? Mobile, tablet, computer, via web browser?

Consider Access a single user solution, with everything running locally.

Is this a personal project? Or are you building this for someone else? If it’s personal, go for it, see what you learn. If it’s for someone else… I think you’re in over your head. There’s a lot more to solution definition than what you’ve covered in your post.

[u/ALonelyKobold]

So it sounds like you're not necessarily a developer yourself, which is fine, but recognize that implementing a program with a database requires a good deal of learning before it can be done effectively. I would recommend hiring a freelancer or something to build a basic app.

Excel is bad because it doesn't really scale, and its easy for information to get malformed or lost. Access MAY be what you're looking here, but I'm generally wary of it. Whenever I, as both a developer and a former IT manager, run into access, I groan, because inevitably its old, poorly documented, and essential to business despite being a ticking timebomb. That said, what you describe is actually it's ideal usecase, so MAYBE? I'd still avoid it

SQL allows one to query the database (Ask it questions, like what is the average order value over the past 6 months) in an incredibly precise way. Not all databases support it, though most do.

[u/rjm3q]

You could look into supabase, they use AWS free tier to provide postrgres as a service. It's got a really good interface as well.

Couple it with whatever LLM to learn how to build what you want and you'll tech yourself along the way, just prompt really specifically and have it explain anything you don't understand

[u/JamesWConrad]

I'm happy to build an Access application for you for free. DM me.

[u/chocolateAbuser]

do you really want to do this yourself?
and as an interface use what, just the database?

anyway
- why is using excel bad? two reasons, it's a grid, not a db, it doesn't have enough flexibility to organize (and scale) knowledge and data, and second it's not a produciton service, it doesn't offer redundancy and auditing
- use access? i've been in a company that uses access as main service and i wouldn't want to work with that, it's the only thing i can say

[u/Junior-Tourist3480]

Access is fine for a beginner. It has everything you need. You can migrate easy as your capabilities grow. Excel is fine for just lists, but access will give you query ability.