How can I build a database and interface for a book publisher's website?

August 9, 2006 10:53 AM

I work at a small non-profit book publisher, and I need to build a database for our website.

We have about 400 titles on our backlist, and each one of them needs to have an individual 'book page' with things like the book's description, the author's bio, the cover image, blurbs out the book, and so on. We also have a 'catalog' page that should be sortable by category (author, year, fiction/non-fiction, etc.)

Right now, all of those pages would need to be made individually, by hand. I don't want to do that. So I was thinking the best thing to do would be to put all that info into a mySQL database, and pull it out to the site dynamically with something like Ruby.

I know HTML and CSS, so the layout and design is no problem. The problem is, I don't know where to begin with mySQL and Ruby. I have 0 experience in working with either of those things. I've found a few Ruby guides on the net, but they don't seem to specifically address this kind of thing.

So, my question is: Is there a tutorial out there that would be good for this specific problem, or has someone already created a solution that I could use and tweak for our needs?

posted by mekanic to computers & internet (23 comments total)

Ruby is a general-purpose programming language. You probably want to look specifically at Ruby on Rails, the popular web development framework for Ruby.

The Rails site has some good quick tutorials and installation instructions that will at least point you in the right direction.
posted by mbrubeck at 11:01 AM on August 9, 2006

How is the data currently stored? Filemaker? Word?
posted by mattbucher at 11:17 AM on August 9, 2006

You might want to look into a content management system (CMS). There is a wide range of solutions out there. One that I personally use for my design site isn't officially a CMS, but that's how I use it: ExpressionEngine. It's easy to use and for building a site like you're describing, I think it would be perfect.

Again there are other products out there, so definitely look around and find the best for you.
posted by lunarboy at 11:27 AM on August 9, 2006

Thanks guys.

Some of the data is in Filemaker, but much of it exists in Word. I was hoping to dump the Filemaker database into mySQL somehow and then fill in the rest from the Word files by hand.
posted by mekanic at 11:27 AM on August 9, 2006

Obligatory WebGUI plug.
posted by baylink at 11:36 AM on August 9, 2006

I know you've mentioned Ruby, but the most popular free web programming language is PHP. PHP is free to use, rather easy to pick up, there's plenty of support available around the web, and it is designed to do exactly what you want to do - create a dynamic website using data from a database. It's popular enough that the acronym LAMP has been coined - Linux, Apache, MySQL and PHP.

Your task is really not hard at all. You might be able to find something that almost does what you want, but if you learn just a bit of PHP and MySQL you can do exactly what you want.
posted by jellicle at 11:53 AM on August 9, 2006

I love this link when my sql syntax is botched. PHP for the absolute beginner. I have no filemaker experience but there is probably some kind of export function to set up your data so you can create a sql table with it.
posted by shownomercy at 12:27 PM on August 9, 2006

Django is your friend. Jeff Croft's Django for non-programmers article should get you started.
posted by evariste at 12:41 PM on August 9, 2006

(Django and Ruby on Rails are sort of in the same general class of rapid web-app development tools, but in my opinion you will find it easier to get started with Django).
posted by evariste at 12:42 PM on August 9, 2006

Dabble DB and QuickBase are both online services that would be flexible enough to do what you need to do. QuickBase has a number of pre-built solutions that might work out of the box as well.
posted by kaefer at 1:01 PM on August 9, 2006

Might I also advance the suggestion that you look at PHP instead of Ruby. Ruby on Rails can incur a fair amount of overhead that can probably be avoided if you're just doing a small website for your back catalog. You'll only need two pages worth of script (one to make a query and render the answer; one to modify the database), and PHP is really quite easy to learn if you have previous exposure to programming.
I personally hate web scripting, so I always use PHP because it's the fastest way (for me) to get things done.
posted by kfx at 1:02 PM on August 9, 2006

Why not just use an e-commerce shopping cart like Miva Merchant, which is very customizable and not much different from a database? You could set up the fields you need, you can sort into categories and catalogs and, best of all, you can sell the books directly to the consumer and raise money for your non-profit. You upload a spreadsheet or database to populate the fields, and the pages are generated on the fly. A license isn't real cheap, but many webhosts provide it (and similar shopping carts) as part of their packages. I've seen a number of small presses go that route (shopping cart software), because it provides all the information and leads to sales too. (If your press doesn't sell direct, you can link to whoever does your sales for you, even Amazon.)
posted by bigmuffindaddy at 1:04 PM on August 9, 2006

I have to 3rd the PHP suggestion, but I recommend picking up PHP and Mysql Web Development - not only because it's an excellent tutor for this kind of thing - but I think one of their examples is almost this exact setup - books, authors, genres, etc with a web interface - (The book is not right in front of me at the moment, tho. Argh!)
posted by bhance at 1:26 PM on August 9, 2006

I second bigmuffindaddy's idea. Yeah, PHP/MySQL would work fine, but why not just get those titles into a working shopping cart? Think of a shopping cart as an ecommerce-ready database—that's all it is. You're going to want to do that sooner or later; why waste time and effort putting the data into a non-ecommerce-ready db? And if you get the right cart, you should be able to import your data (after a little massaging, probably) without ever having to learn any scripting at all.
posted by bricoleur at 1:48 PM on August 9, 2006

This sounds to me like a good use for Drupal, one of the many content management systems out there. I'm sure dozens of others would also be good but Dru is what we do a lot of work in.

Were I approaching this, I'd do a fresh Drupal install and find a theme I found reasonably presentable.

I'm lazy and you don't sound like you intend to extend this later, so I'd just edit the template for the 'page' or 'story' type to make a presentation I found attractive, then create one node each of that type for each book. If you want to be a little more thorough you would install the flexinode module and have a type specifically for books.

The nice thing about Drupal for your purpose is that you can set up the taxonomy stuff to match whatever categories your organization already puts books into and set the system to allow multiple select so a node can have multiple terms. So you could have 'whacko,' 'paranoid,' 'adams apple' for Ann Coulter's book, for example.

Then you can set up pages that show everything in that category, so people would get to that book if they looked at the page for any of those categories.
posted by phearlez at 2:12 PM on August 9, 2006

mekanic posted "The problem is, I don't know where to begin with mySQL and Ruby. I have 0 experience in working with either of those things. "

In fact, so little you don't even know if MySQl and Ruby are the best choices for what you want to do. (They're not.)

Don't go with MySQL. Even in MySQL 5.0, you lack a number of features that make databases programmer-friendly. And while PHP is easy to use, it's also easy, especially for a non- or novice programmer, to write code full of security holes.

The first time you leak your customers' credit card numbers, VISA and MasterCard will stop allowing you to make any charges.

You're a smart guy, I'm sure, but you're not a programmer. Would you re-wire the office's electrical system? Would you eschew a CPA, instead doing your nonprofit's accounting and taxes yourself? Would you do your own legal work with being a member of the Bar, much less even having a JD?

Programming is something any bright person can pick up. So is accounting or the law. But when it's a mission-critical task that could put your customers and your non-profit at risk, you need to hire a professional, or at least get an existing shopping cart package configured by a professional.

I'm a professional programmer; I've seen the kind of mistakes fellow professionals can make. You've never done any programming, you'll inevitably make mistakes. Big ones and small ones. Starting with something so big, that could cause great damage if any part of it is misconceived or misconfigured, is too big a risk to take.

This isn't your personal project; it's the nonprofit's. This isn't your risk; it's the non-profit's. It's a risk of the money and time donors have contributed to build the non-profit. if you accidently release customer information, the non-profit could be sued out of existence.

Depending on your position with the non-profit, you might very well have a legal fiduciary responsibility to exercise due diligence (in which case it's your risk also).

You'd hire an electrician, a CPA, a lawyer. Hire a programmer.
posted by orthogonality at 2:22 PM on August 9, 2006

Orthogonality, you raise good points, but mekanic gave no indication in his post that he intended to write his own shopping cart application. And no subsequent poster has suggested that he do so. He just wanted to make the information in his catalog web-accessible, and bigmuffindaddy suggested (and I seconded) that he really ought to go ahead and set up his catalog in an off-the-shelf shopping cart. For his situation, it just makes sense. I don't think he needs to hire a programmer.
posted by bricoleur at 2:39 PM on August 9, 2006

Also, David Ricardo's theory of Comparative Advantage; it'll cost you more to do this, when you factor in the stuff you are trained to do that you won't re doing while you try to do this, than hiring it out to a competent professional who already knows how to do this and may also be able to add in economies of scale.

It might be cheaper for you to raise your own chickens rather than buying Perdue's, but the cost of not doing that which you do most efficiently, that which brings in the most money per time and effort expended, for you.
posted by orthogonality at 2:40 PM on August 9, 2006

bricoleur writes "Orthogonality, you raise good points, but mekanic gave no indication in his post that he intended to write his own shopping cart application."

Which lessens the risk, but not the cost. a good web developer could set him up in a week; he'll take four times as long, have no idea what to do when it falls apart, and non of his novice code will be useful to or maintainable by a professional when, inevitably, the professional has to be called in. an meanwhile, mekanic's regular work, work that he's good at and knows how to do, won't be getting done.

But I've got to go: I'm need to figure out how to replace the brakes in my car, which I'll do as soon as I butcher my own chicken for dinner, right after I finish cutting my own hair, which I'll do as soon as I stitch up my own ear, which I accidently cut instead of the hair.

I'm not trying to be a dick here, but it amazes me how people with no programming training and experience figure they can "just do it", when they'd never dream of taking on other professional tasks. Sure, if you want to program, take it up. with time and effort and after mistakes, you can make your own site (just like Mathowie has done). But it takes time and effort and a lot of discipline and learning by erring.
posted by orthogonality at 2:48 PM on August 9, 2006

I can't believe nobody's asked the OP what "our website" is.

Presumably the company already has a website, some kind of hosting service? Very few of them have Ruby on Rails, but most of them have PHP and Perl and so on and most of them have MySQL.

So, mekanic, what sort of services does your current website setup offer you? Who are you hosted with and what plan?

orthogonality over-reacted a bit to the thought of doing it with MySQL. It may not be up to his professional standards, but for a system with one user and no critical, secure information, it's perfectly good.

But he's right about "hire a programmer". If you just want to get the job done, then do that. If you really want to learn about databases/CGI/templating systems and so on, do it yourself, but estimate how long it will take and multiply by four.

[Email me if you want to hire me to do it by the way.]
posted by AmbroseChapel at 4:47 PM on August 9, 2006

AmbroseChapel writes "orthogonality over-reacted a bit to the thought of doing it with MySQL. It may not be up to his professional standards, but for a system with one user and no critical, secure information, it's perfectly good."


From the MySQL 5.1 docs: "Currently, you cannot update a table and select from the same table in a subquery."

As it happens, I'm doing a little job for another Mefite right now, and this particular limitation (one that doesn't exist in any other mainstream database, to my knowledge), is forcing me to do a 50 million row update in two parts -- more than doubling the time to do it, and worse, making the code that does it unnecessarily complicated and byzantine and that much less robust.

And the OP doesn't want a single use db; he wants to present the data dynamically. While an over-hasty programmer might make only on db connection for multiple web users, a far more robust solution would involve multiple connections (and connection pooling).
posted by orthogonality at 6:06 PM on August 9, 2006

He works for a "small, non-profit book publisher", not Amazon!

He's going to have 400 rows, not 50,000,000!

He needs one user, himself, to be able to access the database and he also needs to display the database online.

I appreciate your expertise in this area, really I do, but you're telling him to use a very large series of sledgehammers, none of which he has access to, to crack a nut.

For a business and website this size, MySQL is going to be perfectly adequate.
posted by AmbroseChapel at 6:24 PM on August 9, 2006

For the record, mekanic, I'm a Web developer myself. If someone came to me with your needs and wanted to hire me to do it for them, I would encourage them to find a good off-the-shelf shopping cart and go from there, on their own. The fun--and the need for a programmer--starts when you want to start customizing the off-the-shelf solution. But your needs, as you describe them, are sufficiently generic that that would not be necessary.

Teaching yourself PHP/MySQL (or any other scripting/CGI + database combo) is emphatically, as orthogonality has pointed out, NOT a good idea unless you really want to invest a LOT of your life in learning this new skill set. That, and the fact that as sure as rocks your non-profit is going to want to exploit the ecommerce advantage in the near future, makes a commercial shopping cart application look very attractive. A good one will have tools to help you import your data, and there are hosts who offer one-click installation (or something like it) of popular shopping cart packages. If you really don't want to take the ecommerce plunge right away, you could hire someone (probably the original developer) to disable the sales functionality until you're ready for it, and just use the interface to showcase your inventory. That would be much cheaper than commissioning a custom application.

And one last word, about hiring a programmer. You almost have to be one to hire one, if we're talking about an ecommerce solution. There are a lot of programmers who will be happy to do it, but many of them lack the requisite expertise to guarantee the security of your sensitive data. How will you know which ones have that expertise?
posted by bricoleur at 6:50 PM on August 9, 2006 [1 favorite has favorites]