Which database should I use?

Hi everyone!

Firstly, I’m very new to Xojo. I have had some experience with Matlab for my classes, but that was limited to assignment work, so I’m adjusting to new ways to code. I’m very excited about Xojo and what I’ll learn. :slight_smile:

My question is about databases. I’ve never had to develop a database before and I’m not sure what is the best option. Here’s my goal: I want to create a database where the user can input their merchandise and keep a log of it. Also, in a separate db, a list of customer information.

I’ve read that Oracle is really good, but honestly, this is the first time I’ve ever looked at this stuff.

If anyone has an interesting input, please feel free. And please forgive me in advance if I ask silly newbie questions. Everyone here is so smart!

Hi Kayla, welcome aboard.

Some additional information would be helpful. Are you intending to design a single-user desktop app, one that will be shared by thousands simultaneously, or something in between? Understanding the scope of where you intend to start and how large it might grow will help us to make a recommendation.

Some additional questions: How much data do you expect? MBs or GBs? How complex is your data structure going to be?

SQLite is the most simple database to use. Even so you should be familiar with things like ERM or SQL. Oracle is for those who seriously have too much money. In between there are other solutions (MySQL, PostgreSQL) that are multi-user. Valentina is both single- and multi-user.

oracle is also really expensive…
you may use sqlite for a single user database
and postgres for multi user database
they are both free to use
think of oracle as a 1000’s users database engine.

I would also say:

  • SQLite for single user desktop application
  • Prostgres for multi user destop and anyway for web-app
    Both are free to use and even you can download free management tools for it.

Wow! Thanks for all the responses!

Kem, yes, I’m creating this as part of a software for thousands to use. Single user desktop app. I do have a question though. For the software, the user will start a business file. This business file will include the product database and also a client database, but if they wanted to add a completely separate business for the software, generating a completely new product/client database for that separate company, would that be a problem? When the user would start a new company they would click File ->New…starting a new file/company. Would the separate information (separate databases) be difficult to construct? Thanks for your help!!

Beatrix, I personally don’t see it becoming the GB rage, but maybe the user has a super long list to input. So I’m thinking the bigger the better. I’ll say the GB range. I’m hoping not too complete. Just inputs from the user (Product name, product code, price, qty, etc.) Thanks for the recommendation. I’m definitely going to check out SQLite, as it seems to be highly recommended. :smiley: I’ve heard of Valentina (I found this link on YouTube last night: Installing Valentina for Xojo), but I’m still researching it. It looks very useful!

Thanks also Jean-Yves and Joost! I’m going to research SQLite.

I just found this video by Xojo. I’m looking at it now. Using SQLite

Hi Kayla,

as you already understand: Welcome.

You can go to the SQLite Home to get more in depth reference material (for SQLite general use).

For SQLite inside Xojo, the documentation is here to help starting…:

SQLiteDataBase

You will find some interesting links there and at the page bottom.

At last, in Xojo’s folder Examples, you will fnd a Database folder with many examples, notably the SQLite folder who holds:

EddiesElectronics.sqlite SQLiteBackup.xojo_binary_project SQLiteBlob.xojo_binary_project SQLiteExample.xojo_binary_project

HTH.

I pondered exactly the same as you when I first started with Xojo. I’ve been pleasently surprised at the performance of SQLite as I’ve used it in different projects. Oracle is quite the heavyweight when it comes to database systems and when I had a brief look, the learning curve seemed high.

I would second the recommendations already made here for SQLite, PostGreSQL or Valentina. Theres loads of choice between the three and they can all handle large amounts of data.

Hi aboard, I strongly suggest you to create your own I/O Functions for Communication to databases whether you choose SQLite or another DBMS for your Apo e.g.:

function SQL(query as string) as Recordset ... end function

This gives you the flexibility/freedom to change your database provider/ technology with ease at any time without going through all your code.

Kayla,
you will find this forum extremely helpful.

The members are varied - from hobbyists to hardcore professionals, and there are a lot of us regular members who visit the site…ermm, regularly :slight_smile:

Feel free to ask as many questions as possible; someone will be able to help you.
There is also a Language Reference page to help with syntax / info etc. Language Reference Link .

Have fun, and welcome.

You might also need to make sure you encrypt your database or at least use a secure connection to the database.
Depends on how sensitive the information you are storing is.

[quote=191591:@Kayla G]Wow! Thanks for all the responses!

Kem, yes, I’m creating this as part of a software for thousands to use. Single user desktop app. I do have a question though. For the software, the user will start a business file. This business file will include the product database and also a client database, but if they wanted to add a completely separate business for the software, generating a completely new product/client database for that separate company, would that be a problem? When the user would start a new company they would click File ->New…starting a new file/company. Would the separate information (separate databases) be difficult to construct? Thanks for your help!!

Beatrix, I personally don’t see it becoming the GB rage, but maybe the user has a super long list to input. So I’m thinking the bigger the better. I’ll say the GB range. I’m hoping not too complete. Just inputs from the user (Product name, product code, price, qty, etc.) Thanks for the recommendation. I’m definitely going to check out SQLite, as it seems to be highly recommended. :smiley: I’ve heard of Valentina (I found this link on YouTube last night: Installing Valentina for Xojo), but I’m still researching it. It looks very useful!

Thanks also Jean-Yves and Joost! I’m going to research SQLite.[/quote]
Welcome to Xojo.

Based on the information you provided, SQLite is likely the best choice. If your application grows to become multi-user then PostgreSQL will likely be the best choice. Both database engines are free, powerful and well supported by Xojo and this community.

Consider keeping all the application data in a single database with many tables. Doing so will make it easier to retrieve when you are combining related values in a single query. For instance, you may want to present a list of customers that purchased certain merchandise, share customer information between businesses or compare the sales of one business to another.

When designing your tables, be sure that each one has a field with a value that uniquely identifies the record. These are called primary keys and are usually numeric. In SQLite, it is an Integer Primary Key. You can use those values as a way to cross-reference records across tables. For instance, each sales transaction would have references to a customer and merchandise. More precisely, each sales transaction parent record would have a reference to a customer record and each sales transaction child record would have references to the sales transaction parent record and a merchandise record. There could be many sales transaction child records for each sales transaction parent record. This is all part of Database Normalization and something important to learn a bit about before designing your tables.

Happy coding! :slight_smile:

Wow, this is just amazing. Thank you everyone for you incredible help. Not only did I learn, but I also feel so welcomed. Thank you so much! I’m going to research everything that has been mentioned. :slight_smile:

Not sure if you are aware of this page, but it’s definitely worth checking out:
Resources Link

Kayla

If I understand your answers to size correctly I think you are expecting hundreds/thousands of users but NOT to a single database. I think your answer was based on maybe thousands of users EACH with their own database running on their own machine.

If that is the case then SQLite will be great.

If, however, you want a single database that is accessed by thousands of users then you want a heavier database. In that instance then you need to follow the previous advice given about Postgre/Valentina etc.

I am a heavy user of SQLite and I have one database with over a million records on one table alone. My queries run really fast on SQLite and I have received no issues at all. I think SQLite is fantastic!

Simon.

Simon Larkin has some very nice tutorials which may help get you started.

Database Design Tutorial

SQL Tutorial

Welcome to the community! Obviously you’ve found that we’re a pretty friendly lot and will answer most questions (just don’t ask us to do it for you).

If you need additional training on getting up to speed we have over 200 individual videos at http://xojo.bkeeney.com/XojoTraining/ available for subscribers. This includes two complete, start to finish desktop applications and one web application.

Kayla, Welcome!

We’ve got this video playlist of tutorials and recorded webinars on using databases with Xojo: https://www.youtube.com/playlist?list=PLPoq910Q9jXhRoPw0_mHKdVUKPXpL9TKV

This blog post should also help once you have decided what database to go with: http://www.xojo.com/blog/en/2013/06/databaseconnectivity.php

Not trying to poopoo on anyone’s parade, but you will have to consider licensing issues eventually. From what I briefed over in the comments above, you’re developing an app to be used in a business enviroment (or something close to that?)

The vast majority of RDBMS all have some licensing/restrictions on commercial use - and most are not free when used in this capacity. Which ever you choose, make sure you take the time to research if licensing will be an issue down the road. This I learned the hard way. As a caveat, SQLite can be used without restriction in any environment.

Also, I would roadmap your data storage needs and redundancy. If transactions, rollbacks, or concurrency (to name a few) of your database is necessary, or you want to off load some of your business logic to the DB engine, the bigger players are a much safer and powerful choice.

I have used MySQL, Postgres (found the learning curve steep on this one), SQLite, and Valentina in my previous projects. All have pros and cons. (I would recommend MariaDB over MySQL).

I have switched over to Valentina for both embedded and Client/Server applications as of late and have found it to be a pretty venerable product. Brutally fast and it has a pretty rich API with Xojo.