Web Connection
Database recommendation
Gravatar is a globally recognized avatar based on your email address. Database recommendation
  Kathy
  All
  Mar 18, 2020 @ 11:11am

Hello,
I'm starting a new Webconnect project.
Could you please let me know your idea on which database is mostly recommended now?
Thanks,
Kathy

Gravatar is a globally recognized avatar based on your email address. re: Database recommendation
  Tore Bleken
  Kathy
  Mar 18, 2020 @ 11:52am

My favorite is Microsoft SQL Server Express, which is free unless you have a huge amount of data.

Gravatar is a globally recognized avatar based on your email address. re: Database recommendation
  Kathy
  Tore Bleken
  Mar 18, 2020 @ 01:50pm

Thanks Tore.
And what if I'm asked to store .pdf data in database? First of all, what's your idea on that?
And second, what database you would suggest for huge amount of data?
Thanks.

Gravatar is a globally recognized avatar based on your email address. re: Database recommendation
  Rick Strahl
  Kathy
  Mar 18, 2020 @ 02:36pm

Whether you use SQL data or Fox data really doesn't impact the decision of storing large blobs of data (like a PDF file) in the database. You still have to decide out to deal with the storage for those files.

You can certainly store binary data in the database and SQL makes that pretty easy and efficient - unlike Fox the 2gb limit and memo bloat aren't issues with a SQL Server of any kind. But - storing binary files in a database makes the database very large. For SQL Express you might hit the 10gb limit a lot quicker with binary files stored. Also if you have to manage backups, having a huge database can be burden as backups take longer, and have to be stored and copied as huge resources etc.

Personally I don't like storing binary data in a database, but rather use a file storage mechanism - a folder with subfolders and files basically and then use paths in the database to locate the data. The OS is pretty efficient at file storage and retrieval. That said - backup is still an issue in that scenario as you also have to backup the physical files and it will be separate from the database. But one can use a zip routine to package up all files relatively easily and as a bonus that can probably be done in the background while the database is still online.

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Database recommendation
  Tore Bleken
  Tore Bleken
  Mar 18, 2020 @ 02:40pm

I don't recommend to store PDFs in tables. Instead I recommend to store the PDFs as separate tables, and only store the name of the PDFs in the table, with or without the path.

The limit for the free version is 10 GB, if you need more maybe you should consider to pay for another version?

Gravatar is a globally recognized avatar based on your email address. re: Database recommendation
  Rick Strahl
  Kathy
  Mar 18, 2020 @ 03:12pm

Also if you want to store data as blobs maybe you can consider some other mechanism like Redis or a NoSql database, which generally do better with large files. That's what most big services do for file storage to allow fast access and quick, non-transactional storage. These are basically flat key/value stores that hold all content in a single file. This would give you the SQL database for your application related data, and a separate data store for the 'content' data that can be managed separately from the regular data.

Another options for the data: If you do decide to store in a SQL database, perhaps store the data in a separate database, so the large blobs are managed separately from the application data and doesn't slow down the data access. Whatever you do, you won't want to store large blob data mixed in with regular data records - always store the blob data in a separate table at minimum, or a separate database, file system or data store.

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Database recommendation
  Kathy
  Rick Strahl
  Mar 24, 2020 @ 02:17pm

Thanks to both of you for your precious advice.
Kathy

© 1996-2020