Store order details.

Question

I am building an multi vendor appointment booking app.

Need SQL database design to store orders.

Example :-
There is a shop and they have four employees.

End user can book any one employees.

I was thinking to dynamically create seperate table for each shop. So we can store orders seperately for each shop.

Or i should just create a single order table and put all shop orders in same table... And retrieve the data using shop_id and employee_id

Answer 1

It sounds like you're asking if a single table for all orders in all vendors is better than a separate table for each vendor.

I would go with 1 table for all orders(regardless of vendor). More generally, keep things as simple as possible until there is a clear reason to complicate things more. 1 table is more simple than a table for every vendor and I see no good reason to do something more complicated than that.

Also, create a vendor or shop table to have each vendor represented in a separate record. The order table can reference the vendor or shop like you mentioned with a shop_id field.

Answer 2

There is no problem having millions of records in one MySQL table.

The maximum number of records in one table is discussed here and is in the billions if you have an int primary key:
https://www.quora.com/What-is-the-maximum-number-of-rows-in-a-mysql-table

The most important performance issue in any SQL database is improper indexing so be careful with that. You want primary keys and foreign keys defined properly by indexing on ints or GUID's and not lengthy strings.

Database table names are often tied directly to application code and foreign keys can become a complete mess if you split up tables like you're considering. Duplicating your orders table for every vendor probably requires duplicating every table that could reference your orders table too.

A last resort if you really run into a performance problem is to split up your application's deployment and database into multiple instances. If an employee is associated with 1 vendor instead of many vendors, it should be simple enough to make each web app and database deployment responsible for a subset of your vendors. This would tackle not only your database becoming a bottleneck but also your web application server because it limits the load on both. Connecting with the appropriate instance could be done through subdomains. You could have a separate subdomain for each vendor like sallysalon.yourapp.com, finedining.yourapp.com. Multiple subdomains could be associated with the same IP address so a separate subdomain for each vendor won't require you to deploy 1 web app and database per vendor. It would just be a clear way to indicate which vendor to connect with.

This last resort would complicate your deployments and hosting a lot, though. This added complexity isn't worth it if you aren't actually noticing or anticipating any performance issues in the near future. Being overly proactive leads to lots of improper planning and wasted complexity. It is often better to be more agile than complicate things based on a questionable prediction.

Answer 3

I would break it into multiple tables in MySQL. Breaking it up will make it easier to use SQL queries to find the information you want. JSON and SQL don't mix really well in that SELECT statements can't query into JSON in any standard way.

You're probably referring to a financial transaction but also be sure to use database transactions for your financial transaction. A database transaction can be rolled back if something breaks part of the way through. Database transactions can prevent customers or you from losing money if something weird happens like your server freezes or reboots part of the way through updating your data and collecting payment.

Answer 4

Thanks..i dont have much experience in database designing.

The reason i asked to create seperate order table for each vendor.. because i thought one table for all vendor orders might get very big it may contain thousands or millions of rowas and slow down the select query.

Answer 5

Josh Greig thanks man you are a life saver..

Answer 6

Hi Josh Greig ,

Sorry for disturbing you but i have one more question.

If a user booked multiple service with one employee in same transaction.

Then, should i insert every service in a seperate row in order table or i should just use json to store multiple service and insert as a single row in order table.

Answer 7

Josh Greig yeah... Thanks