I’m experiencing a trouble with my business model, let me explain better.
I’m developing a software for 1 year and few months, it’s for the food industry, more exactly a software to: Delivery, Take Way, Table Reservation, POS, Accounts Payable and Receivable, Prints(receipt), Kitchen Monitors Orders, Customers Orders Control and Fiscal Area.
Well, I had separated the software mainly in two areas, one is web area and the other is desktop area (Used by Admins only) and local installed.
1 – Web Area (Basically do the follow:)
- Show Catalog with the products
- Customers Make Orders
- Customers Pay for the Orders
- etc … as mentioned above
2 – Desktop Area
- Manage Orders
- Manage Customers
- Manage Suppliers
- Manage Accounts Payable and Receivable
- etc … as mentioned above
The web area is hosted in an online web server (scripts and database are online). The Desktop area is hosted locally in a Linux machine with a local database and local scripts files.
My question is:
Is it possible to keep only one Database for both applications? If YES, please what is the best approach?
Follow my technical specification environment
Database: Actually I have two databases working and I would love to keep only one.
Operating System: Linux (Kernel 2.6.X and above) or Windows (XP and above)
For the Web Area
- Apache, PHP, Python, Java Script, Shell Script and MySQL.
For the Desktop Area:
- PHP-GTK2, Apache, PHP, MySQL and Shell Script.
Addtional Information about the system [EDITED]
The main purposes to have a Desktop environment is:
- I need to print and give out Tax Coupon on each new order.
- I have the drawer.
- I have a serial reader (Bar code).
- I have a terminal to query for information.
- and few other stuffs that is so hard if not impossible to do in the web environment.
Desktop relational schema: 104 tables.
Web relational schema: 42 tables.
- Permissions is assigned based on: Deny all by default, and assigns as needed.
- (Validation / Sanitization) has already been done in the web application.
- Until now performance is not a problem, since every fiscal year part of my database will be archived.
(Also customer, suppliers and all related data about them will be archived after 3 years of inactivity).
My real problem is I have 2 environments today and I would love to have only 1.
Then “merge” the both worlds into one is an option, but not sure about.
I would love to read here a lot of ideas about this matter.
Observation about the process: Today it is in operation and it works fine, but I feel like waste time in some process.
Take a look in the scenario:
- The order is made in the internet, then stored in my Web database.
- The “order” is sent to the kitchen and prepared.
- Print a receipt (Tax Coupon), put it with the order and delivery to the customer.
- A Tax Coupon is generated by my Desktop Environment and it need a call to the orders table, but it is not the Web Order Table but a Desktop Order Table.
In few words I need to generate again the order to fill up the Tax Coupon. The same happen to Customer Register, Supplier Register and few other stuffs.
I hope this information turn my question more readable. Thanks Again!
6
Short Answer: You would definitely have less headaches with a single database.
Otherwise, you may end-up with record synchronization issues, as well as with record duplicates, in all local databases that you might use.
What would you need is just to create a DAL (data access layer) and make your client applications (desktop, wen, mobile, etc) access your database through your DAL.
4
Yes it is possible. And may I also add that it should be for easier maintenance and modifications.
As I read you specs, it is very likely to develop using a single database, since you are using MySQL and light and easy to start. No you can not claim that MySQL is for only web application. Many applications are there of product and tool domain which are using it.
There are corresponding configurations that must be set for direct database connections. However, your Desktop application is connected to public host server with a static IP for you to do this and have your Desktop application establish connection.
I may also add that if you do not want to go through this tedious configurations, you can have a web service for your desktop to perform your desired database manipulations.
Add:
you web service may be using JSON or XML for better parsing.
It looks like orders are common to both databases – if so then it makes sense to use the one common database as there is no need to synchronize the two. If there is no commonality in the data, then keep them separate.
A couple of things if you are going to have them together in one database.
1) Create a separate log-on for the web and desktop applications. This will make it easier to debug issues using trace tools. Assign access permissions according to what each user needs to have access to.
2) Build a Data layer and only allow the web site to access to database by calling stored procedures – This will help prevent SQL injection attacks. This could possibly be a shared library between the two.
3) If performance becomes an issue, then you would need to be able to move the lesser used tables to another physical disk (I would suspect the Accounts and suppliers tables) So consider in the design phase if there is the need to have separate schema (Ties in with point 1 with regards to permissions) for transactional and non-transactional tables.
1
It is valid to share the same database between desktop apps and web apps. However, I don’t see why you need desktop technology per-se. You have 2 different groups of functions that has different users but that does not call for using a desktop and web-based application environments. There is usually a benefit of hiding your database from the web if you don’t have to have it there. However, many accept the risk after implementing the usual security measures, which you need anyway for the web portion of the application. There are several issues with having multiple environments so, with batch processing aside, I don’t think you have shown compelling requirements as why the desktop functionality is mandatory in your question, and as a result, I don’t see there is a case for 2 different environments, all the functions you have listed can be handled in web environment (except for the batch processing which is not clearly detailed).
2
Sure it is possible, mySQL is a database server, and is designed to support connections from multiple sources, whether these are local or remote.
However, when installed on a webhost, by default mySQL is typically configured to only allow local connections for security reasons. There is a setting in the configuration file to enable/disable remote connections, you will need to set this accordingly.
See: How Do I Enable Remote Access To MySQL Database Server? for details.
2
You could also consider a third approach, a single database and a web API for the application to access the information.
This way you don’t need to directly expose your SQLServer to external sources and can better manage what the application accesses.
It is also easier to update a web API to changes in the database structure than redeploying the desktop application every time.
1