Could be made closer to normal form?

[cefn]

Love the project.

I suspect it would be better and easier to work with, if you used ChapterID as an indexed column within Paragraphs to establish the relation to chapter, instead of 'duplicating' this information in the Chapter table, hence avoiding the requirementfor a joint index across the Act and Scene columns for requests to be efficiently handled in the Paragraph table.

This isn't a bad reference for thinking about these design decisions. https://en.wikipedia.org/wiki/Database_normalization

[mozz100]

Awesome work getting it all into this form! Agree with cefn re: normalisation.

Not saying you have to do things this way, but I like Rails, and Rails by convention expects integers for id columns.

Since it is Shakespeare's birthday, I thought I'd fiddle with your data. Here's the results of 45 mins' messing around:

https://gist.github.com/2473564

(by the way, the SQL in that script took quite a while to execute on my computer - not the most efficient stuff in the world, but it was a one-off 'migration')

• I had to write my own table creation - the gist shows how I did it
• I used a couple of temporary tables to hold info while rejigging the ids
• The data ends up in a form that would make it easier to build a webapp on top of it
• It'd take a bit more effort to implement cefn's suggestion, and also to make a proper many-to-many table for the relationship between characters and Works

Dunno if I'll have time/energy to build anything else with this - let me know if you do!

Richard

[mozz100]

OK, Shakespeare's birthday has finished (at least, in British Summer Time it has).

I managed a Rails app built on your data. To quickly enable browsing I put rails_admin in. Paragraphs are there, but they're excluded from rails_admin views because there are a lot of them!

http://bardofavon.herokuapp.com/

Source code and my partially-cleaned-up data: https://github.com/mozz100/bardofavon

[mozz100]

@cefn I normalised the data according to your suggestion. Not pushed it into the Rails app on heroku yet, but I've dumped out the db onto github. I think that's what you meant? See https://github.com/mozz100/bardofavon#readme

Further suggestions welcome!