Spreadsheet or Database?

In My Humble Opinion
1

I have this idea that I’m going to do some organizing around my house, among other things. To that end, I wish to make some kind of record for some of my possessions, such as DVDs/Blu-Rays, CDs, books (paper and electronic, to include old RPG-type books) and video/PC games. I also want to (finally!) get all of my dadblasted logins & passwords (to include security questions & answers, security codes, backup emails, etc.) stored in one easily accessible place (flash drive, not on the computer). All such constructed lists are entirely for my personal use, bless my anal retentive soul, and will not be used for any business purpose that I can currently fathom.

I’m somewhat familiar with constructing spreadsheets (having done several here at home for personal use), but have only used the very simplest of formulas whilst doing so. I use Open Office, if that matters. I’ve only ever fiddled with constructing any kind of database in an introductory course some years ago, which spent only a small portion of time covering the big 4 of the Microsoft Office suite. I do have a spreadsheet book that I can’t seem to put my hands on at the moment, nor apparently do I recall the exact title (something along the lines of ‘How To Be A Spreadsheet (or Excel) Hero,’ with the cartoon figure of a superheroine on the cover–online searches turn up nothing, but I believe the book is around 10 years old, give or take a couple years), and that book has helped me quite a lot, even if it may be judged to be simplistic. If you’re a spreadsheet fan or a database fan, or both, beginner book suggestions along with your recommendations will be very much appreciated.

I expect some of my categories to be large, numbering in the dozens if not the hundreds (I’ve recently reawakened an interest in Magic: The Gathering–please don’t judge. :wink: ). Here’s the elements I want to include, although I don’t rule out adding more to this list as needed: name/identification, creator/company, type of thing, year published where applicable, sequence/order where a series is concerned (such as Butcher’s Dresden Files), possibly whether I own the item or not (where a series is concerned–music albums and movie franchises count here as well as book series), and maybe a couple-other things that aren’t coming to mind at the moment. A high priority for me is ease of updating and reordering–if I have 30 books under the letter ‘P’, I want to be able to insert a new book easily into its ‘proper’ place. (Which reminds me of an old joke where I don’t suffer from OCD, I suffer from the same disease called CDO, with the letters in alphabetical order–as they should be. :smiley: )

Sorting within spreadsheets seems to me to be a fairly easy process to finish updating a particular list/category, but then I have almost no experience with databases, thus I turn to you all. Considering the lengthiest of a given category would have absolutely no more than 200-300 entries (except for M:TG, which I foresee climbing a bit higher–they don’t call it cardboard crack for nothing), should I stick with (lots of) spreadsheets? Or should I construct a database with everything in it, and call up a list out of that bloc when I want to see (for instance) what albums I have, specifying albums as a category that must be included (thus, of course, eliminating any non-album items for that report)?

Any and all recommendations, suggestions, anecdotes, experiences, and preferences will be most appreciated. I’m really looking to hear from those of you who’ve used both types of program, but of course anyone with anything to tell is welcome to chime in, even if you take the trouble to tell me both types of program are Ebil™ and I should use a real program like MySQL, or just stick to pencil and paper like back in the good ol’ days.

Mods, since I’m soliciting opinions, I thought this would be the appropriate forum. If that just ain’t so, please move as needed. And to all, thanks in advance!

2

Lovely question! There isn’t any one answer; everyone will build their data list differently.

My old ex-co-worker strenuously insists on databases. They’re made for this kind of thing.

I vote for a spreadsheet, and that’s what I use. It’s far more immediately visual; I can see exactly what I’m getting. With a DB, you have to create a report to visualize your data, but with a spreadsheet, it’s all right there, in front of you.

Also; spreadsheets are much easier than some databases for building derived data fields. Example: you have a personal db of contacts, with, among other columns, city, state, and zip code. With Excel, you can make a fourth column concatenating those, a new “csz” column. In MS Access, that’s simply not possible (or, rather, requires jumping through a big complicated load of hoops.)

In a spreadsheet, doing a subtotal by categories is simple. In Access, I have never succeeded in this, despite being helped out by people who really know the program inside and out.

Finally…if you have a spreadsheet, you can use many database programs to open it and do reports on it. But you can’t open a db in a spreadsheet program! With a sheet, you can have it both ways, if you insist!

ETA: I have a particular skunner against Access. It is a MEAN interface. It’s hard to work with, unpleasant, awkward, ugly, and just damn nasty.

I wish to goodness I had bought a copy of the Progress Database when I had a chance. It’s much more elegant, and permitted derived data fields (like csz) without any hassle at all. If I could afford a personal PC installation of Oracle, I’d go for it in a second.

MySQL is tough sledding. I’m still trying to get started with it. It is not being nice to me.

3

Depends on what you want to do and whether the categories have stuff in common.

Lets say you have several categories. Now lets say you want to know what two categories have in common. For example lets say you have a list of comic books and a list of music albums. If they have something in common, lets say genre or author, you might want to ask “Show me all authors who produced both a comic book and a music album in genre XYZ”.

If you just want to sort a SS is probably fine.

If your categories really have nothing in common that you’d want to cross reference then it might be a waste of time to make a database.

4

I’m not that aware if how versatile Excel is in terms of how you arrange fields on a page.

What I do know is that in a database you can arrange fields in a layout just about any way you choose. You can include or exclude any field you want. You can include summary fields without even having the fields that go into calculating them on the page at all.

I’ve been working in FileMaker Pro for 20 years or more. I’m far from an expert, but I know enough to do some fairly sophisticated things.

Another advantage: you can combine text in the page with fields (for labeling and other purposes), and also use such handy entry aids as check boxes, pop-up menus, etc.

You would want to dump everything into one database only if everything your tracking had enough fields in common. Otherwise, you’re better off with separate databases.

I just believe databases offer a lot more versatility in terms of getting stuff in a page to look exactly the way you want it to.

FileMaker Pro works on both Mac and Windows. And by the way, it can do the concatenating fields bit with ease. For the purposes you describe, there wouldn’t be too steep of a learning curve. You could also look into pre-made templates to get started.

5

It If the majority of what you’re looking to catalog is media, there is software designed for that task. In the past I’ve used Readerwarebut I’m less a fan of it nowadays due to its increased cost and closed nature. Data Crow is a free option I liked the last time I used it. Both need just the UPC code of your book/movie/game to get all the information on your item and add it to your database.

I think they both use SQLite to store your collection (I’m confident Data Crow does, but I think Readerware used to and I haven’t used the latest versions), so if you wanted to learn database programming, your collection would already be there to work with. Otherwise, you’ve got a decent application that should be able to give you what you want.

6

This is definitely true. Databases usually have report-writing tools that allow you to put together differently-formatted reports.

Say you have a big list of everybody you know. You can have a Boolean field for “Christmas Card y/n?” Then, not only can you select everyone whom you want to send a card, but you can also format the name and address fields to print out as mailing labels.

A spreadsheet would also allow a column for “Christmas Card y/n?” and you could sort the sheet to select only the people you want. But the data is still in columns.

Spreadsheets do allow you to move the columns around, hide columns, etc., so you have some control over data appearance.

Both SS and DB allow you to “export” data into files that other programs can use. For instance, as mail-merge files that Word can pull from to make mailing labels.

SS are easier to do data-entry in – for some kinds of data! I do a lot of tabular data entry, and can use Cut-and-Paste, Copy, and Sequential Fill to save myself a lot of time. With most DB, I’d have to type the same entries over and over and over.

7

if the data is limited or you might use a spreadsheet. some people do OK, especially if they know the spreadsheet and don’t want to learn the database.

i find the database beter for lists because of the many data fields or multiple operations that i might want to do.

8

Unless you’re planning a career change to become a DBA, I can’t imagine why anyone would want to do a home inventory sort of project in something as user-confounding as an Access database.

I’ve never really messed around much with spreadsheets whose names aren’t Excel, but you mention having CDO. If you do a sheet for books, for example, you can enter a header row of Author, Title, Location, Format, etc. then just start typing in the info for each book. If you want to sort by title, click the Title column, then hit A-Z. Likewise if you want to sort by author or whatever. If you add more books, just re-sort and all is happy.

It is worth noting that you are far from the first person to want to catalog their possessions, so there are a half-million or so stand-alone applications as well as templates and macros for Excel to do this.

9

Which is why I recommended FileMaker Pro.

I’ve never used Access, but I’ve certainly heard the horror stories.

To add to what I said earlier, if you want to see every item in your collection all at one time, and the information each entry contains is limited to just a few fields, then a spreadsheet will work.

However, let’s say you have a lot of information about each item. For example, a record album with title, artist, label, year released, group personnel, genre, tracks, etc. In a spreadsheet, you’ll run out of screen real estate before long and will end up scrolling right and left to see it all.

A database allows you to look at each entry individually and see ALL of its information on one screen, without information from any other record getting in the way. However, it will also allow you to have a list view (with perhaps only the most important fields displayed) when you want to see several entries at once.

Yes, and to expand on what Mithras said, some programs allow you to bypass manual entry altogether by scanning the barcode of the media, which automatically fills in a lot of the data.

10

Hiding columns you don’t want to see in excel is trivial as is producing reports.

The key difference is that Excel is fantastic for sorting and displaying your information.
Access is the tool you want in order to investigate the relationships in your information.

11

A database is harder to set up from scratch. And can be harder to modify for new categories etc.
I went from almost zero experience, to creating an operational, web accessible database. But it was rough.

I used XAMPP, which is a package that includes MySQL, Apache, PHP and other supports, all very functional and interconnected right after install.

I then purchased PHP Maker to create a nice interface. There are others.

Open Office, Office Libre, also contain database software. I found them less intimidating than other ones for learning some concepts before trying to make the big one. These would function fine for a non online data base. With some tinkering they can also go online. There are templates out there for databases of collections. Some with great explanation.

Now I love database recording of stuff. But only if it is a big bunch of information, that has constant addition to individual records. It is a database of repairs to equipment of various types. Over 10,000 records now. Before, each piece of equipment had a separate spreadsheet. It was a nightmare.

But I just use a spreadsheet for my household record of items. Model, serial number, etc. If I had a large and growing collection of certain things, with various important details. Database.

12

I’d do it in FileMaker (that should come as no surprise, it’s what I do for a living).

FileMaker is 80% as easy to set up as Excel and so much easier to run reports and do searches and organize relationally. Interface is the development environment which in turn is the data warehouse (hence if you rename, add, or drop a structural element you don’t have to change code elsewhere as a general rule).

13

I’ve done both at work.
The first thing I’d do is to read up on SQL and see if building queries sounds like fun. If not, I’d go with a spreadsheet. I’ve maintained a complex one with the raw data on several sheets and reports on others, linking to the data. I also have written tons of Perl scripts to extract data and process it in ways spreadsheets can’t.

If you have lots of data, though, something like mySQL should work fine.,
I have my books, all 6,000 of them, in a spreadsheet now which is fine because I can also put it on my phone for shopping at used book stores. I’ve done a schema for them in mySQL, but I have to write a script to load the spreadsheet into the database. No time, no time.

I don’t know anything about specialty programs, but I’d want to check if they are flexible enough for you.

14

Thanks for all the replies so far! I’ll be looking into the suggested programs. I find it both interesting and telling that no instructional books or websites have really been mentioned. That type of thing seems to have been replaced in this day and age (for programs/software, at least) with ‘take it out for a test drive.’ :slight_smile:

15

We recently moved and took the opportunity to do a similar inventory. We used an application called Delicious Library, which allows you to use the barcode to quickly scan and add books, dvds, games, etc to your library. As we had over 1,000 items, we wouldn’t have had time to manually enter them all into a spreadsheet/database!

It also calculates the purchase price and current value of your stuff, which we found really handy when we updated our contents insurance.

16

I’ve asked before: is there a general purpose, cheap, home-use database program that’s easy to use? (Access, or the db with Libre Office, aren’t!)

I remember Infocom’s “Cornerstone” database. That was a really sweet program! I miss it terribly. What’s out there, right now, that might work for general purpose light home db records?

Is Filemaker affordable? Does Oracle sell a stripped-down home version? I’m still wrestling with MySQL, and I don’t think I’m good enough to get much use out of it.

17

Unfortunately, no. The latest version is $329. I’m lucky 'cause I got mine through work from the beginning (FileMaker 2) so have never had to pay for it.

18

No db will be “easy to use” if you have no experience.
It’s not a deficiency in the product. A database is by design a powerful tool and requires some effort.

19

True. But I did teach myself FileMaker Pro completely on my own. I had zero experience when I first encountered it, and have never taken any courses. There are many advanced features of it now that I don’t use, but I believe I could incorporate those if I had some time to devote to it.

I can’t speak to other programs (again, I’ve never used Access, but what I’ve heard isn’t encouraging). I guess I’m making the point that mere mortals CAN learn FileMaker Pro and do some very sophisticated, or very basic things with it. I wouldn’t want the OP or anyone else to be scared off of databases generally by anything said here.

20

Skeered.

Down here in Louisiana, the word is ‘skeered.’ :wink: