This past month’s Construction Business Owner magazine had an article entitled “No. 1: Avoid Spreadsheet Dependency!” which highlighted the limitations of spreadsheet based solutions and given the dominance of spreadsheet users here I was wondering what all of you might think and have to say about what the article had to say.
Some other articles on basically the same topic:
- Spreadsheet vs. Database Which One Offers The Best View Of Your Data?
- Estimating- No Easy Task (McGrawHill)
- Tradesoft ProjectPak Better Than Spreadsheets (
In my own biased thinking I think a few points I have on the downside of spreadsheet based solutions are:
- Spreadsheets can very easily get to be big and clumsy
- Spreadsheets are not multiuser. You can’t, at least not very easily, have more than one user working using the workbook at any one time)
- With spreadsheets, by default the entry of data into a spreadsheet doesn’t require validation. In other words you can vary easily enter text in a field you intended for date, a date in a field intended for dollars etc. While you can set up a spreadsheet with validation most of the time it’s never done.
And on the upside I think that database solutions:
- Are more robust
- Are more scalable
- Are better at searching and sorting
- Have a better more detailed security framework (you can set up different users with different levels of access)
- Provide better reporting
- Are more efficient in that their relational capabilities allow you to link more data related tables in more ways to minimize duplication of data entry.
- Are easier to maintain and harder to break
- By default the fields in a database all require basic validation. When you design the database you select a date field for dates, a time field for time, and a number field for numbers etc and you can very easily program in more precise and specific validation too.
- Databases have ‘referential integrity’ so you wont (or are at least very less likely) to end up with orphan records. Lets say you have a table of vendors and a table of materials costs for materials provided by those vendors. If you delete a vendor record (with referential integrity set up) all the material cost records from that vendor would be deleted too and not just left as orphan material costs with no related supplying vendor. In other words you don’t have any records that reference other records that no longer exist.
On the other hand speaking kindly of spreadsheets, spreadsheets can be up and ready for data entry faster than databases and spreadsheets also are more efficient at copying and duplicating blocks of information. When you are doing a one-time analysis of a particular set of information there isn’t a faster tool for creating a simple snapshot that you might not use again.
So what do you all think and what do you use?
Replies
I'll use a spreadsheet and /or computer database if and when I ever do the same thing twice.
which pretty much will be never.
Yellow legal pad and a fuzzy memory ... all it takes.
Jeff
Buck Construction
Artistry In Carpentry
Pittsburgh Pa
As a small-time builder, but big-time dumazz, I've got no idea what a data base is, or even what it can do for me...but learning spreadsheets has taken a lot of the repetitive drudge out of number stuff.
Since I can set up the spreadsheet, I get it, and once the formulas are right, no more math errors...even better, no more doing the math til it comes out the same twice<G>
From 941s to figuring concrete, spreadsheets keep amazing me with what they can do.
Whoever it is I wish they'd cut it out but when they will I can only guess.
They say I shot a man named Gray and took his wife to Italy,
She inherited a million bucks and when she died it came to me.
I can't help it if I'm lucky.
Snort - "As a small-time builder, but big-time dumazz, I've got no idea what a data base is, or even what it can do for me..."
Snort I like to think think of a 'database' as an elecronic or digital filing system. It really kind of a 'souped up spreadsheet' in that the records (rows) in spreadsheet are what makes up a table in a database. Like peteduffy has just said QuickBooks and the other accounting rograms are really at their core relational databases.
You might want to take a look at the 30 day demo of FileMaker Pro to see just what a database program can do and or allow you to do. FileMaker provides a bunch free pre-built database examples along with some free one rom 3rd Party developers too (Free FMP Solutions)
While FileMaker is getting more and more powerful and feature loaded with each release it still retains an incredibly easy user design interface so it easy for the average Joe (or Jane) to design their own solutions.
View Image
Jerrald, I could certainly use a lot more organization in my filing...I just ordered the trial version of file maker...we'll see just how simple it really is<G> Someone's got it in for me, they're planting stories in the press
Whoever it is I wish they'd cut it out but when they will I can only guess.
They say I shot a man named Gray and took his wife to Italy,
She inherited a million bucks and when she died it came to me.
I can't help it if I'm lucky.
I'm with Snort,spreadsheets work for me I understand them and can alter them to suit my needs, however I'm always up for learning a new way so i will check out the database and see if I'm up for it.
I don't know what a big time dumazz either is but I could take a guess!cheers,
john
http://www.johnwalkerbuilders.com
Relational databases are the real way to go. Very powerful, useful, safe, etc. Once set up correctly, and the data is put in, then you can do just about anything with your data to analyze it. The important part here is, SET UP CORRECTLY.
Problem is, most people (not just in the trades) don't understand them and can't think of data in more than 2 dimensions. Just about everybody understands the rows and columns of a table (spreadsheet.)
Databases take time and knowledge to set up properly. A spreadsheet table can be done quickly and easily, with minimal computer skill.
I think once people really realized the power of a relational database (set up properly) they would forego spreadsheets for all but the simplest tables. But I doubt this will happen anytime soon.
Something pre-packaged would be nice, but peoples' needs are all different.
Basically, accounting software (like QuickBooks) is a relational database, isn't it?
I've made databases using MS Access. It's amazing what can be done with those things. It's also amazing how much time it can take to get everything right, especially for someone without formal training in DB design and computer programming. But once it's right, then data entry goes smoothly, then analysis of data can be anything you want.
Pete Duffy, Handyman
peteduffy - "Problem is [with database programs], most people (not just in the trades) don't understand them and can't think of data in more than 2 dimensions. Just about everybody understands the rows and columns of a table (spreadsheet.)"
I think you really hit right on the head one of the reasons why more people don't use databases. A spreadsheet user see a column of numbers that has a total, average, or some other function at the top or bottom of the column and that makes visual sense to them and it easy to figure out what is going on. In a database program that function can be positioned anywhere on a form
Whereas in a database program that total, average, or some other function can be positioned anywhere on a page so the visual clue may not be there so the user doesn't make the connection as to just what is going on.
"Something pre-packaged would be nice, but peoples' needs are all different."
Well there are many solution providers out there with solutions that they will customize to meet different user needs and requirements.
"I've made databases using MS Access. It's amazing what can be done with those things. It's also amazing how much time it can take to get everything right, especially for someone without formal training in DB design and computer programming. But once it's right, then data entry goes smoothly, then analysis of data can be anything you want."
Since you understand what a database is and have worked with MS Access you too might find FileMaker Pro interesting and useful in that it is faster and much easier to build solutions in FileMaker compared to Access.
View Image
I'm with Pete on this. I too have built a couple of MS Access databases for different things. I don't program, but once you understand the tool you can do amazing things.
But there in lies the problem - you have to understand the tool. After I left one company, the database was added onto and reconfigured by people with no clue as to what they were doing. Basicly, they started treating it like it was a big spreadsheet. They never did the regular data maitenence (compacting the database) that was required, and it came around years later to bite them in the azz. Unfortunatly, I had forgotten most of what I knew by the time I got to look at it again so I could unwind it again.
Rebuilding my home in Cypress, CA
Also a CRX fanatic!
Parenting has always been a mix of sage life advice and inexcusable laziness.
xxPaulCPxx - "...They never did the regular data maitenence (compacting the database) that was required, and it came around years later to bite them in the azz. ... "
Yeah but the 'maintenance' you need to perform to keep a database system in shape is nothing compared to the work you have to do to maintain and operate a spreadsheet based solution. And by 'maintenance' I don't mean just the technical software maintenance. I'm also talking about the 'maintenance' of the data itself, keeping it all up-to-date and relevant.
View Image
No question that is correct - however... the nature of a database is to keep some of the messyness away from the front end. People know when a spreadsheet is all messed up - or at least getting that way. A database is more like a cat - it hides that it's sick until it barfs and poops in your shoes.Rebuilding my home in Cypress, CA
Also a CRX fanatic!
Parenting has always been a mix of sage life advice and inexcusable laziness.
is excell a spreadsheet or data base
MS Excel is a spreadsheet.
Jon Blakemore RappahannockINC.com Fredericksburg, VA
Excel is a spreadsheet program. MS Access is a database program. If you purchase Microsoft Office pro, it will include both. Or you can buy the apps individually.
jt8
"You live and learn. At any rate, you live." -- Douglas Adams
While other have already said Excel is a spreadsheet program, it has advanced so much that it is starting to creep into the fringes of database land. I used to use Excel to set up the data and data tables just the way I wanted them, then imported them into Access.Rebuilding my home in Cypress, CA
Also a CRX fanatic!
Parenting has always been a mix of sage life advice and inexcusable laziness.
I used to use Excel to set up the data and data tables just the way I wanted them, then imported them into Access.
Excel & Access compliment each other. You can clean up your data in Excel prior to importing it to Access. You can create new tables with the import, or simply append the data to existing tables. You can also copy or export the data from Access back to Excel (if you want to email some data to someone for instance).
Both programs can do a vast multitude of tasks.jt8
"You live and learn. At any rate, you live." -- Douglas Adams
Paul,
I am a database programmer who see's a whole lot more 'Sick' spreadsheets then I do databases. You are correct that when a database gets out of control, it is really out of control, but that is more a lack of understanding the users needs when the database is originally created or during maintenance. This can be avoided by looking at the requirements during the design phase in the same way a plan review can save a whole lot of headache before a house gets built.
My 2 cents is that Spreadsheets and databases have their places. The upside to spreadsheets, as has been stated before is that they are easy to create by just about anyone. The downside is that the people who create these often understand their business needs but don't understand computers or the risks of not getting their data correct. This often means that when the data is really needed, you need to bring in a high priced expert to analyze the data and pull out whatever they can into a system that can help figure out what the problems or potential is. By the time I get a call that a spreadsheet solution is no longer working, the train wreck is coming along quite quickly. For financial information or real simple applications, a spreadsheet is quite often the correct solution. Anything more complex, I would opt to look at a database.
The upside to databases is that they give you the ability to look at your data in ways you would never think of with a spreadsheet or another tool. Databases also give you the ability to validate your data on the front end so the data in them is cleaner when it goes in. Remember - Garbage In, Garbage Out. Finally, databases are great at historical archiving.
The downside to databases is that you need to think in a fashion that is not natural to most people. This is generally lumping data into buckets - a customers name and address in one, sales history in another, etc. This requires thought up front to avoid the traps I mentioned above, and it also requires occasional true-ups of the database structure to respond to changing business needs. It's not always easy to justify the time and trouble until the problem is apparent - kind of like backups - but it is always obvious in hindsight when a database is no longer working.
Overall, knowing the advantages and disadvantages of the software is a lot like learning when to use a framing hammer and when to use a finish hammer. Each have their place, but some are better suited to the job at hand.
Steve
I'm biased. Part of my regular job is designing databases.
In general, normal folks have to make a leap to start using spreadsheets. Once they get used to them they usually love them. And once you've been using spreadsheets for a while, chances are a database would be an even more effective tool for you. Better at crunching data, better at organizing information and making reports, etc.
IMO, the average user needs to get used to speadsheets before they're ready to tackle databases, because they don't have someone (like me) who is custom designing the database to their needs. Once they are comfortable with spreadsheets, then maybe they can move on to a database program like Access.
jt8
"You live and learn. At any rate, you live." -- Douglas Adams
A spread sheet really is a database. So, the line between the two as to when to use one or the other isn't always that visible.
Yeah, technically, a spreadsheet can be considered a database, but only on the most basic level. A flat database. Relational Databases are built from tables, which are spreadsheets.
The major difference is this: With a spreadsheet, it is difficult, if not impossible, to make it relational. That is the benefit of relational databases. One key item or value can be linked to a whole table of information. And a piece of information (cell, if you will think of it in Excel terms) in that table can be linked to another whole table, (ad infintum). It can be done with spreadsheets, but you risk data integrity because you would have repeating cells, rows, or columns, when really you only need one (the key).
There is a whole science behind relational databases, and I remember something like 5 laws or levels to ensure data integrity. That's what can't be done with a flat spreadsheet.
I could look up what those 5 laws are, but I'm in a hurry right now, and maybe one of the DB developers knows them off the top of their head and can enlighten us all.Pete Duffy, Handyman
actually, a spreadsheet can have linked 'tables' as well with lookup fields and pivot tables and the like. You can do quite a lot with a spreadsheet.Now, granted, you can do a lot more with a well designed relational database.In the end, use what you know. I've see WAY too many Access databases that were set up by folks that didn't understand databases and are such a giant mess that they really would have been better off using a spreadsheet in the first place. ;o)
A spreadsheet is not a database. It may be able to act in some ways like a database , but that does not make it a database.A database is a collection of tables. Each table can be thought of as page in a spreadsheet. A relational database lets a table be related to another table through the use of a key value, like relating a constructional material to a supplier code; this lets you store the information about that supplier once and use that supplier for several different materials.A database used for a construction application could have a customer table, a job table, a labor table, and a materials table. The customer table would hold your customer information (name, address, phone numbers, etc). The job table would identify which customer, have a description of the job, some dates around scheduling, possibly an overall estimate of costs. Your labor table would hold contractor/employee information, showing who has done what on a given job. The materials table would contain a list of materials for the job and could identify the supplier.There are as many different ways of defining the tables in a database as there are of building a house. I would recommend that anyone wanting to track costs for a job either stick to using a spreadsheet or bite-the-bullet and buy a commercial application. I would not recommend that a builder invest the time and effort in building a database for his business; he is better off buying something off the shelf.RJ
There are a couple of database management systems that are geared toward the average user. Back some 20 years ago when I supported a bunch of MacIntosh users, Filemaker was a database that a number of users with limited computer knowledge were able to get up and running. Filemaker is still available, although I haven't used it in at least 15 years, but I assume it is still aimed at the entry level user. There might be some other simple database system hanging around.Access, is of course, the big gun of the individual user database management systems. It takes some computer knowledge, but probably could be mastered by anyone who can use Breaktime. It has a number of tools to try and get one up and running quickly. Last time I checked, it wasn't exactly cheap, however.If one wants to go really cheap, there are some free database management systems out there. MySQL is available for free - however you need some fairly serious programming chops to get it up and running. I think PostGRESS is still free from U.C. Berkeley. PostGRESS is billed as an object oriented database system and a step beyond the previous generation of relational database systems. (I have developed databases with heirarchial, networked, and relational database management systems, but I have yet to tackle an object oriented database.) Actually, a database need not be a collection of tables. Tables are the hallmark of relational database management systems, but before the RDMS systems came into prominence, there were hierarchical database systems. These systems used "entity relationships" rather than tables. I started out on something called "System 2000" in 1983 and, IIRC, it had only one file for data. Anyhoo, not that it matters for what we want to do here.As indicated, modern spreadsheets an act like simple databases. They are easy to get up and running (compared to a full blown database system) and some users have done amazing things with spreadsheets. However, they are certainly not as flexible as most relational database systems.In a properly designed system, people with limited knowledge can enter and retrieve information in a database management system. However, as previously indicated, it does take someone with some intimate knowledge of the database system to set up the data entry system and the data retrieval aspects of the system.
I love pedantic computer topics. I'm a sucker for them. ;o)While a traditional 'database' and 'spreadsheet' are certainly different in many ways, at their core, they are both databases. As can be an XML file, a comma delimted file, or what have you. As mentioned, the multi-table concept is a hallmark of a specific type of database: relational, of which (I think) most databases are that you'd commonly come across.
Filemaker is still usable by entry level people, but you can getting pretty freaking sophisticated with it these days. If I were a dedicate code monkey, I might want something else, but there isn't much limitation to what you can do with FMP... there are a few functions to avoid on gigantic record sets, but those can be avoided even in large solutions. Maybe for larger numbers of users (like, enterprise level) it's a bit cumbersome, but for small business I haven't run into much I can't make it do. I am, admittedly, a fanboy though...-------------------------------------
-=Northeast Radiant Technology=-
Radiant Design, Consultation, Parts Supply
http://www.NRTradiant.com
Hey Jerrald, found your postings on bidding a couple of months ago extremely informattive...o.k. nuff brown nosing. I was wondering if you had thoughts on some of the software out there for small business, Looking for something I can create bids on, turn them into cost tracking records, track costs etc.... Third time around for me, had 70 framers a year ago and almost went bankrupt. Had office people supposedly doing the books, but since the guy at the top [me] had his head in deep things went south fast. Been reading alot of lit out there, some reccomended by you, now have a better 'idea' of how it is supposed to work. Starting small again and want to have some office tools to help that don't require huge amounts of time to learn, though all come with a curve....Do have a book keeper who uses quick books...
ryder - " I was wondering if you had thoughts on some of the software out there for small business, Looking for something I can create bids on, turn them into cost tracking records, track costs etc...."
That's actually something of a loaded question. Were you looking for information in general about all the different kinds of software products out there or the ones I develop?
View Image
While we do produce our own commercial solutions that doesn't mean we don't consult on and recommend other systems too. It all depends on the contractor's specific operation and way of doing things (although I do really like the systems I've designed, :-) )
"Third time around for me, had 70 framers a year ago and almost went bankrupt..."
It's the second time around for me. About a decade and a half to two decades ago I had something like 16 guys and only narrowly avoided bankruptcy. I rebooted as a new business after a one to one and half year break and I'm still here today although now my own personal concentration is on business consulting (which is really just making sure my contractor clients don't blow it like I did) and software development.
"Had office people supposedly doing the books, but since the guy at the top [me] had his head in deep things went south fast. Been reading alot of lit out there, some reccomended by you, now have a better 'idea' of how it is supposed to work. Starting small again and want to have some office tools to help that don't require huge amounts of time to learn, though all come with a curve....Do have a book keeper who uses quick books..."
Yeah the reading and studying the right way to do things is important. And then you actually have to do what you know you have to do too for it to all work. Making knowledge into habit and procedure is the real key. It doesn't matter what software you have is the business rules and practices you have in place don't work.
If your at all interested feel free to give me a call at the phone number that is on the web site and you can tell me more about what you're looking to do business and software wise and I can see if we can recommend or propose a plan of action for you.
View Image
Thanks for the offer and I will certainly take you up onit
“[Deleted]”