SQL Database Application

[Rhys_SSD]

Hello All,

Just after some advice really.

I've been asked by a customer to look into developing a software application for an opticians practice. This system will handle patient records, appointments, sales etc.

My plan is to use an SQL database for data storage and develop a LabVIEW application as a front end for querying data from and inserting data into the database.

I would like to understand other people's experience with this type of setup, and some things to look out for etc.

Also, what SQL database package would you suggest to use. I have recently implemented a simple database application in the cloud using Microsoft Azure. Unfortunately due to the location of the stores, the internet connection is not entirely reliable so I think I would rather have in-store servers to store the database. I am aware of other SQL Database suppliers, Microsoft SQL Server 2016, MySQL, but not sure of the benefits of each one. Some suggestions on this would be welcome also.

Many Thanks.

[aputman]

Microsoft has the Express edition of SQL server that is free for up to 5 users (at least in 2012 edition).  Newer editions of Express may not have the user limit (not sure) but they have other limitations:

• The database filesize is limited to 10GB.  This equates to a LOT of patients and based on your description, this is a mom/pop practice in the country.  I don't see this limitation being a problem unless you start storing images/videos, etc within the database.
• There are server hardware limitations (4 cores, memory, etc)

I recommend going this route because if your customer ever decides to upgrade to the fully licensed edition, it should be a relatively simple task.

[Mark_Yedinak]

I have  worked extensively with MySQL integrated with LabVIEW and it is a full featured DB which is free. I highly recommend it. Regardless of which DB you choose I strongly recommend that you use stored procedures to access the DB. First, you can move much of the processing for your queries into the DB itself. Also, you create an API between your application and the database which means it is easier to change the structure of the database. The API also makes it easier to integrate other applications if necessary.

[Rhys_SSD]

Thank you both for the replies.

With MySQL being a free DB, does it have any limitations as opposed to say SQL Server?

Can it be installed on a Windows Server OS machine?

For running the Stored Procedures, would you simply use the 'CALL' function from within LabVIEW to run the various queries?

Can MySQL export BACPAC files if I wanted to transfer the database to SQL Server in future?

Thanks,

Rhys

[aputman]

You will probably want to have access to the Database Connectivity Toolkit, which is included in the Professional editions of Labview and can be purchased as an addon for other editions.  There is a Create Parameterized Query VI in the toolkit that you'll use for calling stored procedures.  Stored procedures are definitely the way to go.

[SimonAldred]

I have used SQL express with LabView and started with no prior experience of databases. Connecting labview to the database was simple using a UDL file  and I have experienced few problems with the database toolkit. The only major issue we had was the 10GB limit which was a problem because we were storing a large number of thumbnail images to the database. We implemented a simple purge to keep the database below this limit. I probably spent more time on configuring SQL than I did writing labview code.  

[terickson]

I have done quite a bit of this, both with MySQL and MS SQL Server.  As others have said, use stored procedures.  Also, scour the internet on database design, specifically how to normalize a database.  You will thank me later.

Other things to consider:

How will you manage security?

How will you manage backups?

Will there be concurrent users accessing the same tables?

[SimonAldred]

Can you explain what you mean by normalizing a database?

Thanks

[terickson · Accepted solution]

It is part of the process of designing a relational database.  The intent is to reduce data redundancy and make inserts/updates/deletes easier.

https://www.guru99.com/database-normalization.html

https://support.microsoft.com/en-us/help/283878/description-of-the-database-normalization-basics

There are lots more.

I like to think of it as designing the database so that if you need to expand it, the tables grow vertically (adding rows) instead of horizontally (adding columns).

[Rhys_SSD]

Hello,

Apologies for the delay in replying. Thank you all for your input.

I think I will begin by using SQL express to build the solution, and then depending on the preference of the customer, I may keep to SQL Express or upload the database to a Microsoft Azure SQL DB.

My current preference would be to use the cloud solution; backups, security etc will be easier to maintain.

Thanks again,

Rhys