When to Use a Database

  • Comments posted to this topic are about the item When to Use a Database

  • Memory is pretty cheap these days. My analytical "database" is the Microsoft Power BI VertiPaq engine, which is fast. My store is Azure Data Lake Storage (cheap, and unlimited size). For a read-only database, for many use cases, I don't think it can be beat. 🙂

  • My thoughts - a database should be used when you need to have a centralized location for shared data for medium to long term storage for data.  With exceptions.  I say with exceptions because audit data likely doesn't need to be shared, but is long term storage.  Reporting data may need to be re-calculated daily if you need to show financial data and care about exchange rates (shared data, centralized, but short term if it is refreshed daily).  Centralized is one point that I would stick with databases though without exceptions.  If user A has data on their disk and user B has data on their disk, and they are both trying to report and present the same information, they could very easily have different data.

    Keeping things on a local system keeps the data decentralized and puts it at risk of hardware failure.  If your motherboard blows a capacitor (a problem we had with some of our REALLY old PC's... I'm talking Windows XP era, we had motherboards that once they hit a certain age, a capacitor on the motherboard was likely to blow and we had a LOT of computers die in a short period of time), you could lose all of your work.  Blow out the motherboard and there is a chance you blow out any and all connected components including hard drives.

    My former boss's approach was that data stored in Excel (or any format that is on local disk) is only reliable while the file is open.  Once you close it, the data is suspect.  You don't know who else may have opened it and changed the data.  You don't know if cosmic radiation may flip a bit and screw up the data or even if the save was successful or if there MAY have been an error and Excel didn't catch it.   PLUS if that excel file exists on your local disk, there is likely no backup of it.  Now this is an overly paranoid approach but I do get where he was coming from.

    Now my approach is if I want to save the data long term AND/OR I want to share the data between multiple users, it goes into a database.  If it is just for me, Excel is fine.  If it is to do a quick demonstration to multiple people (think proof of concept type stuff), Excel is fine.  But once multiple people are going to be looking at that data (via application or report framework) or it is decided that we need to keep the data long term, into a database it goes.

    We put all of our in-house built application data into the database.  Some stuff requires end users to click on "save" to commit it, other things commit as soon as a cell is changed.  It depends on the application and the requirements, but it all resides in a database.  This allows us to report on it if we need to as well as to look up historical data going back years!

    While I do agree with what Donald.Parish said - memory is pretty cheap, but I would like to add that persisted data (to disk or to database) is more reliable especially if there is an unexpected reboot (power outage).

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply