Feeds:
Posts
Comments

Posts Tagged ‘SQL’

15 years ago in 2006 my first academic journal paper as a historian was published. Sole authored, it looked at the borrowing records between 1732 and 1816 of Gray Library in Haddington, East Lothian, an unusual example of an early free town library. The paper examined these borrowing records to see what they told us about the town’s reading habits at this time.

I thought it might be nice to do a retrospective blog about this journal paper. The paper was published in the Journal of Scottish Historical Studies, and the full published PDF version is available on my website, in green Open Access form on my publications web page. Note I had earlier co-authored publications from my computer science days, but this was the first academic journal paper I wrote fully myself, and my first history piece after retraining as a historian, picking up BA, taught MPhil and PhD history degrees.

The paper was written fairly early on in my part-time history PhD at Dundee University, investigating Scottish reading habits circa 1750-1820 (my full PhD thesis is also available freely online). I decided to write the paper to give me a push to write up this good case study, but it was also creating good analysis I could use in my PhD thesis.

I submitted the paper too late for that year’s competition by the journal for postgraduate students. I remember Callum Brown, the then JSHS editor and at the time a professor of history at Dundee University, asking me if I wanted him to hold my paper back for the next year’s competition. But my health then was so precarious with my neurological disease resisting treatment, so I asked him to just consider it for normal publication as soon as possible. So he did.

It’s common for academics looking back at their early published writing to find it naive or flawed in other ways. I’m actually really proud of this paper, and its breadth and depth of analysis. Admittedly I would struggle to write it now, as my neurological disease has progressed more. But I still think irrespective of that aspect that it stands up well to the test of time.

I was blessed with rich library borrowing records, though I had to transcribe these all myself, working on a microfilm copy of the manuscript originals at home (yes I have my own microfilm reader!). That and the subsequent checking took many months, but gave me over 5000 borrowings to analyse.

Using my genealogical skills and research, especially in the then National Archives of Scotland, I researched the library borrowers extensively, allowing me to identify hundreds of them confidently, and note their gender, occupation, birth and death dates, and address if more specific than (or different from) Haddington. Adding these genealogical details allowed me to examine the borrowers and their borrowings in myriad different ways and groupings, and was a very powerful tool.

Such analysis was only practically possible because I built a linked relational database of the library borrowing records and its readers. This is something that at that time was groundbreaking in a Scottish book history context, but even today would be unusual. The three linked relational tables of borrowers, borrowings and books were then loaded into a MySQL relational database system, where I could run SQL queries to search for the borrowings of specific groups of borrowers that I was interested in. For example the following query counts the most popular books among teenage boy borrowers:

SELECT LinkToTitle, Count(LinkToTitle) FROM
(SELECT *
FROM borrowings, readers
WHERE ((borrowings.LinkToBorrower=readers.ReaderID
OR borrowings.LinkToOtherReader=readers.ReaderID)) AND readers.AgeOfBorrower=”teenage” AND readers.Gender=”male”) AS tmptable GROUP by LinkToTitle
ORDER by Count(LinkToTitle) DESC

Comparing male and female borrowings at the library was very important, and allowed me to engage using this substantial data with academic theories and contemporary opinions about differing reading habits by gender. I also relished the way this system allowed me to examine other groups in detail. For example I was able to pull out the borrowings of teenage users of the library, both boys and girls, which led to a particularly satisfying section of my paper.

One branch of my family tree traces back to Haddington, and it was a delight to see ancestors pop up among the library’s borrowers. Two of them sneaked into my published journal paper: my 5xg-granny Jean Veitch (later Mrs Somner) and her father William Veitch, a watchmaker in the town.

My Haddington library records and database have recently been gifted to the Books and Borrowing project based at the University of Stirling. This means that other researchers can build on my work, indeed a number of them already are, which has been fascinating to see. And ultimately the Haddington borrowings I recorded will be available to view freely online.

The findings in the paper were numerous, ranging across changing reading habits, variation by gender and occupation, demonstrating the use of books to educate young minds, and different ways of fitting in the library into your working week. However I think its main contribution was as a proof of concept. Both for the power of relational databases to analyse library borrowing records in a myriad of ways, but also for the potential of enhancing the library borrowings by other genealogical and historical research to better contextualise the borrowers and their borrowings. However on a personal level it was also a proof of concept for me, re my ability to write and publish academic journal papers. Even if with it sailing through peer review with no revisions required prior to publication it perhaps gave me an overly optimistic and unrealistic view of the tribulations that might ensue in that process!

Again my journal paper about the Haddington library borrowers is freely available to download and read on my website, as a PDF linked on the publications page.

Read Full Post »

Two of my history journal papers recently went online freely under green open access rules. Prompted in part by that I thought I’d look back on the first of those.

Entitled “Glimpses into a Town’s Reading Habits in Enlightenment Scotland: Analysing the Borrowings of Gray Library, Haddington, 1732-1816”, this paper was published in the Journal of Scottish Historical Studies in 2006. At the time I was about half-way through my part-time history PhD. Every year the Economic and Social History Society of Scotland runs an essay prize for postgraduate students, with a money prize, and the winning paper published in their journal, the Journal of Scottish Historical Studies. History postgraduates at Dundee were encouraged in my time to enter. I wrote up my then research, but didn’t complete it in time for the competition deadline because of being particularly ill at the time, causing a delay. But I sent it in anyway. The then editor, one of our Professors, asked me if I’d like him to hold it back for the competition in nearly a year, but I said no, please just consider it as a journal paper submission now. With my life-threatening condition I was keen to get on with things sooner rather than later, and a delay would not help.

My paper was accepted without any revisions, which is rather rare in academic publishing. With hindsight I think it gave me an unrealistic impression of journal publishing as an easy thing to do! I’ve certainly found it harder since, not least as I’ve aimed for more and more ambitious journals. But it at least gave me confidence to try more publishing, and it was a delight to see my research in print, only halfway through my PhD. I remember how thrilled I was to hold the print issues. Even the digital PDF was exciting. I had earlier co-authored publications from my computer science time, including some published after I had to leave that full-time PhD as my neurological illness struck. But this was the first time I had a sole-authored history journal paper, and it was a huge achievement.

The paper was based on research I was doing as part of my PhD on Scottish reading habits. In particular it looked at the borrowing records over 80 years or so of a free town library in Haddington, East Lothian. It was very unusual to have a free library at that time, and one that was open to the whole inhabitants of a town. It opened up all sorts of possibilities for contextualising the borrowings, and also researching the borrowers further.

The core part of the research involved transcribing the Haddington library’s borrowing registers and building up a database of library borrowings. For this I used the relational database system MySQL, drawing on my computer science degree and training. I had three linked tables: one recording the details of borrowers, one recording books in the library, and a third table linking the two, recording details of borrowings. And then I could write SQL queries to interrogate the database, and quickly produce answers to different questions.

For the borrowers, about 700 of whom could be identified, I researched in other local records to find out more about them. Parish registers, both Church of Scotland and other denominations, were useful, as were tax records, wills and inventories, later census returns, and so on. For this I was able to draw on my skills as a genealogist, used to working through such records, and was able to discover significant new information on over 240 of the known borrowers.

This extra information, such as occupation, age, family connections and so on together with the relational database allowed me to analyse the borrowings in a number of different ways. A simple analysis was to look at the numbers of borrowings over time, or, having categorised the library books roughly by subject, the changing subjects borrowed by the library users. Another analysis let me pull out the most popular titles, borrowed the most frequently, in specific decades. But I could also analyse the borrowings of specific occupational groups, or, for example, young girl borrowers. All were easy to pull out using the database structure I had built, allowing queries that would be impossible otherwise.

Results, such as differences between male and female borrower choices, could be compared with findings of other scholars elsewhere. And because I knew so much about many of the borrowers I could also write meaningfully about them. For example I was able to identify a watchmaker father and his daughter borrowing books together. As an added bonus this pair were my own direct ancestors.

I was able to show borrowers working through a multi-part title in sequence, getting hold of successive volumes as best they could, and clearly reading them. Clearly there was often competition for different volumes in the same sequence, but I could trace readers trying their very best to borrow the next volume they needed, and this wasn’t an isolated example. Some other book historians, particularly those associated with the Reading Experience Database, are sceptical about the use of library borrowing records as evidence of reading. But I would argue that the records I studied, with their clear evidence of reading sequentially like this, are very much evidence of that, and should not be dismissed so readily. Significantly they also cover a very sizeable local population, which permits a much greater range of analysis than a single isolated reading reference can.

Another nice thing that the Haddington library records showed was the extent of female reading. Many other Scottish reading institutions at this time were restricted in their membership, and often dominated by men. But the Haddington library was open to all genders, and asked borrowers to indicate when they were borrowing a book for someone else. So there are many loans recorded for female readers, allowing a comparison between male and female borrowing choices – and there was quite a difference – and, as noted already, a study of young female readers, who seemed to congregate in the library, particularly on Saturdays.

Overall I’m very proud of the paper, and still think that the research it presents stands up to scrutiny. I’m also pleased that I was able to use my computer science training in building up the databases that it relied upon. And although it gave me perhaps an overly optimistic view on academic publishing, I think without its experience I probably wouldn’t have gone on to do so much more.

The open access PDF copy of the paper is available from my publications page in my personal website.

Read Full Post »