on-line nomiation tool

[Charles]

I've started giving some thought to creating an on-line book nomination tool to use in conjunction with this group (and available to any other group that wants to use it). The idea is to organize the nomination and seconding process so that it does not require lots of human interaction, both to set up a nomination or to analyze the results.

The server-side will be developed in PHP using a MySQL database, and probably make use of the CodeIgniter PHP framework. Wherever applicable, it will try to make use of the GoodReads.com API, such as to add books, validate users, etc.

If you think you might like to contribute, let me know, and we can coordinate and delegate (I'll probably use GitHub.com to host the source code repository). Front-end developers (e.g. JavaScript, JQquery, etc.) are certainly needed, as my strengths fall on the PHP/MySQL side. Web designers, graphic artists and such would be welcome to make it look at least semi-professional. At some point, of course, testers will be needed, too. Other server-side/DB developers are welcome, too, though at this point (prior to feature bloat and requirements creep!), I'm not foreseeing the need for a whole army of developers.

[[deleted user]]

I'm willing to help. I'm mostly SQL server and some .NET, so may not be useful in development, but I can be a tester.

[Charles]

Greg: I'd be more than happy to let you look over the data schema once I get a first draft, and see if you have any suggestions. I'm a decent hand at DB stuff, but by no means a database/SQL god. :) (Right now I'm not expecting anything huge, but you know how these things tend to grow once you really get into it.)

[Charles]

Slowly starting to get into this. Found a set of PHP classes to handle the connection to the GoodReads API and OAuth, which looks useful -- though I'll want to fiddle around with it to see if I can make it fit nicely into the CodeIgniter framework.

Here's a first cut at the basics of the database design (click it to view full-sized). I'm sure there will be some other tables once I start getting into the real nuts and bolts of it.

[Charles]

Added a table for nomination comments (now reflected in the above image). Figured it would be a good place to note e-book availability issues, questions about applicability to the topic, etc.

[[deleted user]]

I think this is looking pretty good to start.

What database design software are you using? (And is it open source?) I want to be sure I'm reading the color coding of keys correctly. On the nomination table it looks like poll_id and user_id are pink and second_user is a faded pink. Are they just foreign keys or unique keys?

[Charles]

MySQL Workbench: http://www.mysql.com/products/workbench/

It's free, not sure if it's open-source or not.

Looks like the yellow light bulb is the primary key, solid diamond is "not null" while open diamond allow nulls, and pink/orange (whatever it is) is a foreign key.

[[deleted user]]

Did you want to handle the constraints (1 nomination, 1 second per user per poll) on the front end? Or did you want unique keys on the table?

Also, you might want to add a reject_user_id key to the nominations table for when it is determined the book is not valid for the poll. Only mods would be allowed to reject.

[Charles]

I'm all for any logical DB constraints, whether or not I enforce them in the app code. Seems like we'd just need a unique index across nomination.nomination_id and nomination.user_id and then another across nomination.nomination_id and nomination.second_user_id, right? (And then make sure I trap that error in the app code :-) )

[Charles]

Oh, and the reject thing makes perfect sense, too.

[Charles]

Charles wrote: "Oh, and the reject thing makes perfect sense, too."

Hmm...except if we want a user to be able to make a new nomination or second, then the unique constraints become an issue, at which point I may have to consider the one review/one second per user to be application logic only?