2

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:

  1. I need to print and give out Tax Coupon on each new order.
  2. I have the drawer.
  3. I have a serial reader (Bar code).
  4. I have a terminal to query for information.
  5. 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.

  1. Permissions is assigned based on: Deny all by default, and assigns as needed.
  2. (Validation / Sanitization) has already been done in the web application.
  3. 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:

  1. The order is made in the internet, then stored in my Web database.
  2. The "order" is sent to the kitchen and prepared.
  3. Print a receipt (Tax Coupon), put it with the order and delivery to the customer.
  4. 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!

Improve this question
6
  • 6
    Honestly, what makes you think it could not be possible? Sharing data between different applications is one of the main reasons why databases exist.
    – Doc Brown
    Commented Sep 25, 2012 at 5:55
  • Actually, from your comment below I guess you don't want to merge your 2 database schemas into one, you just want to use one database server instead of two (keeping the schemas separated). So why don't you just try it out? I guess the location of the DB server and the db names are not hard-coded into your 2 applications, but configurable (or could be made so easily).
    – Doc Brown
    Commented Sep 25, 2012 at 7:46
  • @Doc Brown - At a first glance it appears to be a bit confuse, but I have some commonality in the 2 databases, for example -> I have customer, supplier, orders and products in my both structure and I would like to merge it into only one, then I do not need to UPDATE both when it is needed. Thanks man.
    – devasia2112
    Commented Sep 25, 2012 at 17:20
  • 1
    do you expect everyone to grab the missing details here out of the comments to the several answers? Why don't you add such important information to your question (current state/size of your system, size/number of tables with overlapping data , and the fact you want to merge database schemas into one, not just one database, as you wrote). You will get much better answers if your clearly describe what you have in mind.
    – Doc Brown
    Commented Sep 26, 2012 at 6:11
  • 2
    Never ever duplicate data unless you really know what you're doing.
    – Louis Kottmann
    Commented Sep 26, 2012 at 14:42

6 Answers 6

Reset to default
12

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.

Improve this answer
4
  • 1
    Sounds good, I will read more about DAL and how to implement it in a working system. Thanks!
    – devasia2112
    Commented Sep 25, 2012 at 11:46
  • 1
    @B4NZ41 While a DAL may be a good idea to reduce coding, it is not a requirement to achieve what the OP wants. You can simply connect direct to the database from both systems.
    – Gavin Coates
    Commented Sep 25, 2012 at 15:16
  • Look at advantages that DAL provides. link is provided
    – Yusubov
    Commented Sep 25, 2012 at 15:24
  • @EIYusubov - I will accept your answer 'cause it sounds the best to do in my problem. I came up with a solution after measure all "real possibilities" that I have today without compromise the databases. Each time a operation like: customer register, supplier register, product register is made, it will be made in the two databases (now -> db_web and db_desktop). Since it start to happen in this way I will have no problem about duplicate data and other hassles. Thanks about DAL, I will also implement it to centralize.
    – devasia2112
    Commented Oct 2, 2012 at 13:40
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.

Improve this answer
3

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.

Improve this answer
1
  • Wow.. I have today in the Desktop relational schema: 104 tables. In the Web relational schema: 42 tables. 1 - Permissions is assigned based on: Deny all by default, and assigns as needed. 2 - It is already done in the web application (Validation / Sanitization). 3 - Until now performance is not a problem, since every fiscal year part of my database I will archive (Also customer and all related data after 3 years of inactive will be archived). My real problem is I have 2 environments and I would love to have only 1, but I have no idea where and how start to "merge" the both worlds into one.
    – devasia2112
    Commented Sep 25, 2012 at 1:15
2

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).

Improve this answer
2
  • Thanks for the answer! The main purposes to use a Desktop environment is: 1-I need to print and give out Tax Coupon on each new order. 2-I have the drawer. 3-I have a serial reader (Bar code). 4-I have a terminal to query for information. 5-and a few other stuffs that is so hard if not impossible to do in the web environment.
    – devasia2112
    Commented Sep 25, 2012 at 0:58
  • 1
    Now that you have shown some desktop functionality, I will have to say that it does make sense to have 2 environments in this case. I'd suggest you consider buying a solution that has all of this functionality built-in if possible/feasible to your business. I am sure there must be some out there.
    – NoChance
    Commented Sep 25, 2012 at 1:24
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.

Improve this answer
2
  • Sounds a good solution too. I did in the past something in this way,If for some reason I can't "merge" my databases into one, It will be one option. Thanks!
    – devasia2112
    Commented Sep 25, 2012 at 15:56
  • I had allowed my remote server to receive incoming connection, but my real situation is a bit different. One Database is in the internet and the other is LOCALHOST (127.0.0.1). How could I access my local server (127.0.0.1)?? I do not want to port forwarding in the firewall and I really do not want to depend of any "extra configurations" outside of my software. Then I see no solution again in my case. Maybe I will generate queries files with "queries of the day" ahahah and update the local server via (cronjobs) every night after closing-time. thanks anyway!
    – devasia2112
    Commented Oct 5, 2012 at 21:38
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.

Improve this answer
1
  • Yes, API is the next step to us, after completely eliminate the desktop app. The main idea now is turn 100% web app. That is the endeavor.
    – devasia2112
    Commented Jan 29, 2014 at 13:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.