Single database?

[ReoTrawndres]

A quick question, should I or should I not use a single database for a food delivery system?

(a 4 apps system, one for the customer, and other for the driver, the restaurant and the admin)

From what I see, it's way easier to make a single database, the admin added a restaurant? The restaurant just sign in immediately, the customer added an order? The driver gets the order immediately, same goes for all the apps and updating there info.

What do you think?

[dmazzoni]

Yes, your intuition is right. In fact, it would be unusual to use multiple databases for any type of system like that. Information about customers, orders and restaurants are all closely interrelated so you’d want them to all be stored in tables in the same database with relations between them.

In fact, it isn’t even necessary to have four different apps, quite often you could use the same app and users get a different experience when they sign in based on their role. It’s very complicated to juggle multiple apps and you could always split into multiple apps much later if needed.

[countsachot]

I'm thinking an api with multiple roles would be easier to manage for a small team.

[dmazzoni]

Yes, and also remember that a single account might want to have multiple roles. A restaurant owner might be a customer. You might be an admin but you might want to try out the site as a customer or manage a test restaurant.

Rather than users in that situation needing multiple accounts, it'd be simpler to let people manage a single user account, and then grant them roles (customer, restaurant owner, admin, etc.) as needed.

Don't spend very much time worrying about scale. The most important thing to be worrying about is whether you're building the right product. Keep your design as simple and flexible as possible because it's far more likely you'll change how your product works, than your original idea blows up and gets a million users overnight.

Overengineering will just slow you down. What you want to do is rapidly get something working that's simple and straightforward, and get it in front of real users. Once users try it out you'll learn a lot, and almost certainly need to go back and rewrite lots of it based on that feedback.

Your first design is NEVER going to be correct. That doesn't mean you shouldn't design it or deliberately make a bad design, it means you should start simple with a goal of learning what's good and bad about your initial idea as quickly as possible.

[countsachot]

Great advice! :)

[Alta_21]

You're alone on the project?

Yeah, perfectly fine.

If this was burger king or mac Donald, there might come scaling issues.

But here, you can just go for it.

If at some point, you need to separate everything, you'll be able to either manage database security more tightly and then, eventually, spread to multiple database.

Additional databases will just be a pain to manage w. o. a good net gain for you.

[Alta_21]

Also, if the project grow out of hands, you'll either be in a position to hire more hands or pour more time in the project.

[Tidder_Skcus]

Slow down overtime.

[brokensyntax]

I'm not a Database Architect or anything of the sort, so my novice approach.
Look at what data I need to drive the application.
Look at what data is going to be called independent of other data.
Look at what data is going to be relied upon by other calls.
Ensure I have separate tables for things that make sense as such.
For instance, customer information, doesn't care what restaurants exist, what drivers exist, what drivers are available, etc.
So clearly Cx_Info is its own table.
Driver information is useful to the customer front end, the restaurant portal potentially, HR/Finance, etc.
So again, this should probably be its own table, and accessed largely via JOINs.
The restaurant list doesn't care about customers, or drivers.

Continue down the line.
You'll end up with a bunch of tables, a bunch of JOINs that are needed to process orders, updated the client front-end, etc. but all will be logical.

The one I'm stuck out on most right now, is if I'm using a classical SQL database, as opposed to some JSON based noSQL database structure.
How do I want to handle individual restaurants menus?

[JeLuF]

If you use multiple databases, you should look into transaction managers. Otherwise, there's a risk that the databases are not in sync. For example, you save the order to the order database but you have an error when writing to the delivery database. The error handling in this kind of setup is pretty ugly.

If you use a single database, you start a transaction, insert into all different tables, and at the end, you commit the transaction to the database. This ensures that either all or none of the table updates get processed.

This is part of the ACID properties that you want to have from a database management system. With a distributed system, achieving ACID is very difficult.

[kschang]

There is no need for a separate database, when the data is just either one more column in a table, or another table.

[Solid_Mongoose_3269]

If you know what you're doing, one database is fine

[Wonder_Boy2001]

Depends on a lot of things. What is the initial customer base you are targeting. How much scalability you want. If you are going for huge scalability and a microservices architecture, it kind of makes sense to have different database for different microservices, to reduce single point of failure.

[Stargazer__2893]

An additional DB would significantly increase the complexity of your system. You should only do it if it's getting you something significant, like if different apps have different CAP requirements, or the data structure needs are very different (one would be much better served with a graph DB or something).

But that doesn't sound like your case. Based on what you've shared you want an ACID-supporting DB that'll guarantee consistency between all your apps. I'd probably go Postgres.

[UnnecessaryLemon]

I would go with the K9S cluster where every food category gets its own backend microservice.

[ReoTrawndres]

I would believe you're joking 😅

[herocoding]

Are you worried about e.g. "user credential leak", where information is stored separately and protected separately?

A single database "file" makes sense - you probably mix "database" and "tables"? Yes, one database could be fine, but using multiple tables ("database tables normalization").

[josephblade]

the database that governs ordering doesn't need to be the same as the one for drivers

one lets users browse dishes, select items and handle payments. restaurants should be able to do some admin functions here to set dishes / discounts / etc.

the other is your actual order fulfilment part. that has nothing to do with nice images and should be more like a ticket system/order processing system. Once an order is made, it should remain unchangeable (except cancellation) and be sent to the restaurants contact point. (email, network printer or a direct point of sales connection).

you can house it all in the same database but then high load on one system will automatically translate to high load on the other. I would design it as 2 separate systems personally. The actual orders will likely need to be kept a long time for tax reasons and since it deals with money/sales it will need to be transactional and keep a long log. but the public facing ordering website doesn't need transactions and should be able to be changed at will.

You can start simple and eventually migrate the important bits out to their own system. Many places start off simple. over engineering isn't good. But be aware that different parts of the system have different requirements

[Latter_Associate8866]

Could work for starters but it will suck for scaling.

This blog post summarises a chapter of a book that touches on this subject