Small Business, Why have a SQL database?

[sdweim85]

I am a system administrator at a small company, small enough that I don't need to have a bachelors or a ton of experience to be able to do my job.  I setup a backup server that houses all our companies old projects that we don't touch anymore, and I keep a excel spreadsheet of what is on each backup drive.

I've been told numerous times, by friends in the field that I should make a SQL database for everything.  I just don't understand why.  There isn't that much information for a 40 user company that can't just be kept in a few excel files that I can just double click on and run a search.  Same thing if i'm looking for a specific project on the backup server.

Am I missing something?  I don't mind learning SQL if it improves the overall quality of my job with some organization, but it doesn't feel necessary.

[sandheaver]

Well, Excel doesn't scale.  You get to a certain point and then Excel is little more than an obstacle to your data.  You want to get rid of it (or at least mitigate your dependency upon it) BEFORE that point.

I agree with your colleagues, in principle.  That said, only you can know when it is the best time to transition to SQL, if ever.

Besides, if you move your data into SQL you can still access it with Excel or Access.  But, you gain all kinds of non-obvious stuff.  Web front-ends, much more possibility for automation, much simpler reporting, etc.

If your data is simple enough, you can chuck it into SharePoint and manage it that way.  You can do some really neat things with SharePoint workflows, especially if you know enough to write custom workflow actions.  You can REALLY streamline a whole lot of process just with the free SharePoint foundation, or Office 365 w/SharePoint.

[MonMotha]

Excel's propensity to treat anything and everything as a date is problematic.  In general, its lack of proper typing becomes an issue with annoying frequency.  As has been mentioned, it also just plain doesn't scale, and it's FAR easier to "do it right the first time" than try to migrate later.

I've seen people use Excel/Word (and LibreOffice, etc.) to generate templates for report outputs.  Kinda the best of both worlds.  You can use VBA, etc. to fill the report using Excel, but there are things that will auto-fill e.g. a PDF form for you.

FYI, you don't need MSSQL.  It's not bad, but Postgres is arguably better and free.  A bit harder to configure, though.  Avoid MySQL if possible.

[shponglefan]

Being intimately familiar with both SQL databases and Excel, here are my 2 cents:

First, it depends on the data.  If you're just listing Backup Drive #X and Project #Y, then Excel is probably sufficient depending on how many projects/drives were are talking (dozens, hundreds, thousands?).  If you are storing more data than that, then a database can be more ideal.  Especially if you need to run searches on specific criteria.  But judging from the OP, that doesn't sound like the case.

Another thing to consider is migration: Excel is pretty ubiquitous.  Most people know how to use it and it's relatively easy to learn, so if you quit and someone else takes over then it's probably not an issue for them to use/adopt the same spreadsheets you were using.  SQL and general database management is more specialized; not a lot of people know how to create/use them, so if you ever leave your job, then it could be more problematic for the next guy.

Also, the "do it right the first time" mantra is a good idea in theory.  But this assumes you really do "do it right".  I've seen cases where databases have been set up incorrectly from the get go, and as a result can be a PITA to fix later.  If you don't have a background in database management/SQL, then trying to set it up from scratch will be a learning curve and chances are you'll make mistakes.  So I wouldn't necessarily recommend doing that until you know what you are doing.

[MonMotha]

This is true.  If you're not familiar with relational databases or SQL (the language), you probably won't "do it right", and migrating schemas can be just as painful as going from an ad-hoc solution using e.g. Excel to a more formal model.

Now, here's another consideration: how many relations are in your data?  If it's really just a bunch of independent lists, you won't get a ton of benefit out of a relational database.  If everything's intertwined and depends on or references other stuff, well, that's the kind of problem a relational database is designed to solve.  For example, if you just want to enumerate the colors of the carpet in your office, a text file is probably fine.  If you want to create a CRM system, you need a real database (and should probably buy an off-the-shelf system unless you have really good reason not to).

[kahlid74]

As others have said SQL is a great tool but I would suggest you grasp it completely before investing your time and data to it.  Remember, you're the support for it, so if you move your data to SQL and then it breaks or doesn't work you're the front line of support.

So yes, I would say it's worth using and migrating too but only if you take the time upfront to learn it and implement it right as others have said.

[sandheaver]

As others have said SQL is a great tool but I would suggest you grasp it completely before investing your time and data to it.  Remember, you're the support for it, so if you move your data to SQL and then it breaks or doesn't work you're the front line of support.

So yes, I would say it's worth using and migrating too but only if you take the time upfront to learn it and implement it right as others have said.

Agree 100% with this.

[ark_ader]

As others have said SQL is a great tool but I would suggest you grasp it completely before investing your time and data to it.  Remember, you're the support for it, so if you move your data to SQL and then it breaks or doesn't work you're the front line of support.

So yes, I would say it's worth using and migrating too but only if you take the time upfront to learn it and implement it right as others have said.

Agree 100% with this.

+10

SQL is piss easy.   

[sdweim85]

Being intimately familiar with both SQL databases and Excel, here are my 2 cents:

First, it depends on the data.  If you're just listing Backup Drive #X and Project #Y, then Excel is probably sufficient depending on how many projects/drives were are talking (dozens, hundreds, thousands?).  If you are storing more data than that, then a database can be more ideal.  Especially if you need to run searches on specific criteria.  But judging from the OP, that doesn't sound like the case.

Another thing to consider is migration: Excel is pretty ubiquitous.  Most people know how to use it and it's relatively easy to learn, so if you quit and someone else takes over then it's probably not an issue for them to use/adopt the same spreadsheets you were using.  SQL and general database management is more specialized; not a lot of people know how to create/use them, so if you ever leave your job, then it could be more problematic for the next guy.

Also, the "do it right the first time" mantra is a good idea in theory.  But this assumes you really do "do it right".  I've seen cases where databases have been set up incorrectly from the get go, and as a result can be a PITA to fix later.  If you don't have a background in database management/SQL, then trying to set it up from scratch will be a learning curve and chances are you'll make mistakes.  So I wouldn't necessarily recommend doing that until you know what you are doing.

The data I'm inputting is very simple.  We just have 20 backup drives, and I have an excel sheet of all the projects on them.  I COULD add many variables for each project like, job description, size, output, input, time completed.  All that stuff, but its really irrelevant.  We already know how long things will take to complete since we break it down by image*time=completed job.  Also put in the DB things like Serial keys for programs, username and passwords. PCs with their specs.

Also the backup drives of previous projects is only just in the customer we did the project for loses their backup.  We keep it in good faith for them, they don't actually pay for us to backup their work and keep it forever.  Eventually after a year or two we delete it.

[sandheaver]

PC hardware & software inventories; you can collect that stuff dynamically when needed.  I don't see a reason to store that at all, except maybe to detect changes.  Small shops don't care a lot about that.

Usernames & passwords; if you're storing user's usernames & passwords, then you're Doing It Wrong(tm).  You didn't specify, and I assume the worst. 

Project backups wouldn't go in SQL, though metadata might.  you can usually encode the needed stuff in the filename, or just put a .txt file in the backup with those details.  Not SQL.

[MonMotha]

If you really do need to store user account information for your users, what you probably want is LDAP, not SQL.  If you're a Microsoft shop, that means Active Directory.  If you're an "anything else" shop, probably OpenLDAP or Apache Directory Server.  Apple has (or had, when they sold the xserve) a solution that was a rather decent frontend to OpenLDAP, IIRC.  People commonly store host (PC) information in LDAP, too, since it's convenient and provides a decent way to associate users with the hosts the operate.

As for your time tracking and work order tracking, it sounds like what you probably want is a ticketing system with a time tracker built in, or maybe just a plain ol' time tracker.  Check out Redmine/Chili Project for the former.  For the latter, there's tons of little options that are free.  These will generally use an SQL (of some sort) backend, but you won't have to do much other than set it up.

[ahofle]

Minor semantic/wording correction.  You wouldn't move to "SQL", you would be moving to a relational database (SQL is a language used for querying data in that relational database).

My 2 cents, stick with your spreadsheets, especially if you are unfamiliar with databases.
From what little you've revealed about your business, it doesn't sound like you have a need for complex relationships (like products, orders, customers, etc).  The time to think about moving toward a true database would be when you find yourself unsuccessfully attempting to perform queries across multiple spreadsheets and failing to get the answer you are looking for from your data.

EDIT: I missed MonMotha's reply, which was basically the same as what I said.