Finding Grandma

I never knew my grandmother. As far as I know she never knew me, or even knew I existed.

I knew her name, but that was all. After she and my grandfather got divorced it’s as if she fell off the face of the earth. My mother refused to talk about her. When my grandfather wrote his memoirs, he didn’t even mention her name.

That sounds like a good entry-level genealogical project, doesn’t it? Finding your own grandmother. OK then, let’s get started.

The evidence I had to start out with was slim…

First there are the clues in my grandfather’s memoir, in which he states that he got married in 1932. Later in the memoir he talks about adventures he had with his “wife” in London during World War Two. This is clearly not the woman he claims to have married earlier, but he never says anything about how the transition from Wife #1 to Wife #2 came about. Rather confusing.

From my mother, I at least got a name: Mary Emma Berkley. She also gave me to understand that my grandfather had divorced her during or after the War. (Details were not forthcoming, but I was given to believe that he was the injured party). I got the names of Mary Emma’s parents, and where they had lived, but beyond that my mother clammed up tight. She claimed to not even know the date of Mary Emma’s death. She thought it might have been before I was born. She had no contact with her mother after the divorce, apparently.

I tried asking my father, but he had never met her and all he could say was that he never heard anything about her, either. He did remember one episode, shortly after he and my mother were married, though… He recalled my mother going to a funeral in Olds, and he thinks it might have been her mother’s. He remembers hearing that when my mother walked into the funeral home everyone was startled, she looked so much like the deceased it was as if she were standing there amongst them! Because of what I knew about my parents’ movements and residences before I was born I had to place that incident in 1961. So at least I had a tentative date, and I put a lot of weight in it.

I think I may have asked my mother about that funeral once, and she said she didn’t remember.

When I started getting my feet wet in online genealogical research one of the first websites I became familiar with was FamilySearch, mainly because it’s free. There I discovered something called a “Pedigree Resource File”, which is basically a member-submitted family tree. Here’s one that mentions my grandmother:

Whoever submitted this data certainly knew about my grandmother, since her parents match what I already knew. But it says that Mary Emma was born on August 29, 1911 and died on March 6, 1977. It gives no documentary source for those dates, and it has no information about her husband (my grandfather) or her children (my mother and her siblings). I took this information with a big grain of salt, especially because it contradicted the death year that I thought was correct, 1961. I thought it must be a mistake.

Since online research was a dead end I decided I needed to visit the Alberta Provincial Archives in Edmonton to get answers. What I discovered there blew the lid off everything I had previously thought about my grandmother.

The first big discovery I made was my grandparents’ actual marriage registration, which I got to hold in my own hands. I made a photocopy and brought it home with me:

Marriage Registration for John Elliott and Mary Emma Berkley, March 8, 1933

This gave me pause, because it contradicts my grandfather’s marriage year as stated in his memoir (1932). I also have a handwritten family tree of his in the notes that I inherited from him, where he states he was married in 1931. Wrong on both counts: he and Mary Emma were married on March 8, 1933.

Why would he mis-state his year of marriage? Didn’t he remember it? I’ll return to that in a moment.

My next discovery in the Archives was the decree nisi from my grandparents’ divorce proceedings, which contained a summary of the evidence presented at the trial and believe me, it’s a page-turner. I won’t get into details but it must have caused quite a family scandal back in the day (I’m starting to understand why nobody wanted to talk about any of this). Suffice it to say that it was Mary Emma who sued John for divorce (not the other way around), and in my humble opinion she was right to do so. The divorce was final in March of 1946.

I made those discoveries within the first hour or two of my initial visit to the Archives, and they knocked the wind out of me. As I was wandering around the place trying to get a sense of what other resources were available, a kindly researcher from the Alberta Genealogical Society noticed my wandering and asked if there was anything she could help me find. I described what I was looking for, and she led me straight to a row of bookshelves that contained the Local and Alberta Histories collection, a large set of books containing family and community histories, written by the locals themselves. There I quickly found the book that pertained to Hainstock, Alberta, a farming district near Olds that I had passing familiarity with because I knew that was where my great-grandfather received a homestead when he immigrated from Scotland in 1927.

In those pages, in black and white, I found an article all about my long-lost grandmother and her family.

The book is Glimpses of the past, 1894-1994 and you can read it online (no need to travel to Edmonton to get it); the relevant passage, written by Janis (Kilpatrick) Berkley, begins on page 45. Here it is:

Glimpses of the Past (Bowden, Alberta: Hainstock Centennial History Book Association, 1994), pp.45-46.

This confirmed the dates in the Pedigree Resource File, and gave me a wealth of information about the Berkleys that I had had no clue of. Apparently my supposition that she died in 1961 was incorrect. It also told me she had been married to someone named Furguson (or Ferguson) before she died.

Just who was this Janis Berkley, and how did she know so much about my grandmother?

Part of the reason for my trip to Alberta, apart from researching at the Archives, was attending the funeral of my grandfather’s sister-in-law in Olds. I scarcely knew Aunt Esther but since she was almost the last survivor of my grandfather’s generation of siblings and their spouses, I considered it important to go. I might also meet cousins I never knew I had who might be able to give me more information about the things I was researching.

I also planned to use the opportunity to look up Janis Berkley, whoever she was, and interrogate her about my grandmother.

The first thing I did when I got to Olds was to visit the Hainstock Cemetery since I had a few hours to kill before the funeral. It’s a small cemetery and I was able to walk the length and breadth of it in under an hour. There I found the Berkley family plot, and nearby was an unkept grave with a broken headstone…

Well, there she was. And beside her with an identical headstone was her second husband George, who died the year after she did. I suppose in a perfect world he would have been my step-grandfather. I said hello.

Aunt Esther’s funeral was a success, from the perspective of my personal data-gathering goals. I learned from my cousins why my grandfather had fudged his marriage date– it was to hide the fact that his first child had been conceived out of wedlock, which was a big deal in his mind, although probably a lot more common than he was prepared to admit.

I also hung around outside the Olds Museum until it opened and talked to the archivist there. Wouldn’t you know, he did know Janis Berkley. She had moved down East but he promised to pass my email address on to her.

I waited against hope for a couple of weeks, and was delighted when she contacted me. She knows so much because she is married to one of Mary Emma’s nephews, and she took on the role of family historian back when the old timers were still alive and able to talk to us. I’m coming late to that game but fortunately Janis was more than willing to give me all the information I could handle, not only about my grandmother but about her siblings and their families. I was happy to reciprocate with information of my own, since they knew very little about us too.

I’m glad I was able to rehabilitate my grandmother’s memory and get to know her a little bit, so long after her death. It’s too bad this research came too late for my mother, who died before I embarked on this project and was never able to reconcile herself to her mother’s memory.

Here’s the best picture I have been able to find of Mary Emma. It came, surprisingly, from my grandfather’s collection.

John Elliott, Mary Emma Elliott (Berkley), 1940. Children (L-R): Sheila, Ruby, Archie, Boyd

My best guess for dating this photo is August 1940, just before the Calgary Highlanders departed Camp Shilo en route to the War, when my grandfather had a few days leave to return home to say goodbye to his wife. This portrait paints quite the lovely picture of a happy family, and while I now know their marriage was pretty much on the rocks at this point, perhaps this is the best way to remember them.


A few people have asked me, publicly and privately, whatever happened to Mary Emma. Eventually she married a man who was able to care for her more than my grandfather could. This was George Ferguson, and since he was Roman Catholic he had to wait a number of years before he could get a dispensation to marry (her being a divorced Protestant and all). They had no children, and lived out the remainder of their lives in relative seclusion on George’s farm, quite uneventfully it seems.

Meanwhile my grandfather poisoned his children’s minds against their mother. He rewrote the story so many times in his own mind that he started forgetting the details. But since he had literary aspirations, and since he was an officer of the Crown and a Freemason and a pillar of the community, his side of the story endured. Mary Emma’s just sank quietly into the earth.

Posted in Genealogy | 3 Comments

The Irish Connection, Part 2

In The Irish Connection, Part 1 we learned that my presumably Scottish family had its origins in Ireland. There’s more to the story, though.

To help keep things straight, here’s the family tree so far.

Elliott Family Tree

A is “young” John, the Scotophile who discovered he may have Irish roots. B is “old” John, young John’s grandfather, whom we learned in Part 1 was indeed born in Ireland (most likely in County Fermanagh, in the vicinity of Irvinestown), and emigrated to Paisley, Scotland before he was 5.

John Elliott (1859-1915) and his daughter Mary Andrews (Elliott) (1895-1981). This photo of “old” John is undated, but I suspect it might have been taken a year or two before Mary got married, so perhaps 1913 or 1914.

Old John’s father William was also born in Ireland; we confirmed this with his own statements on the Scottish Census records. William, who died in 1908 in Paisley, gives us some more clues via his death registration:

William Elliott's death registration, August 25, 1908

William Elliott’s death registration, August 25, 1908

According to this, William’s father was Archibald Elliott, a farmer, and his mother’s name was Mary Johnston. William was 70 years old at the time of his death, putting his year of birth around 1838. This information would have been supplied to the county registrar by A[rchibald] Elliott, William’s youngest son. We can use these names to delve into the Irish Census records.

The state of the Irish Census in the 19th century is, unfortunately, a mess. Many of the census returns were destroyed, for one reason or another, and what we have are only fragments of what used to exist. The National Archives of Ireland give a brief history of the destruction on their website.

We got lucky, though, and found what I believe to be the “right people” (i.e. my direct ancestors) in the Irish Census of 1841.

1841 Irish Census for the Archibald Elliott household (located in Arva, Drumalt, Killeshandra, Tullyhunco, Cavan)

1841 Irish Census for the Archibald Elliott household (located in Arva, Drumalt, Killeshandra, Tullyhunco, Cavan)

The clues connecting this family with my grandfather’s grandfather’s family are overwhelming.

  • The head of the family is named Archibald Elliott.
  • Archibald is a farmer.
  • His wife’s name is Mary (Mary Anne, to be exact).
  • There is a 43-year-old unmarried woman living with them, identified as “sister-in-law”, named Hariett Johnston. This tells us that Mary Elliott’s maiden name must have been Johnston too.
  • There is a 3-year-old boy living there named William, with a birth year of about 1838.

The only thing to keep this from being a perfect match, though, is wee William: he is identified as Archibald’s grandson, not his son. William’s parents are not identified.

There are 101 possible reasons why William might be living with Archibald and Mary Anne rather than his own parents. His parents might be dead. He might be a neighbor’s orphan. He might be the illegitimate child of one of Archibald’s other children. It’s hard to know, without more documentation, and for the time being this is all we’ve got. Based on these three pieces of evidence–

  • What William’s son Archibald knew about the age of his father and the names of his father’s parents;
  • The information on William’s marriage record; and
  • The evidence in the 1841 Census

— I am reasonably sure that this little boy is the same William who died in Paisley in 1908, my great-great-great grandfather, and that he believed that his parents were Archibald and Mary Anne. He might not even have been aware that he was actually Archibald’s grandson.

This takes our family back to 1781, the approximate year of Archibald’s birth. I consider this quite a discovery.

How William found his way to Paisley is unknown; our family’s oral history doesn’t mention it at all. But we can take a stab at it because his migration coincides with an important historical event: the Great Irish Famine.

Since William was only 3 when we find him living with old Archibald, it’s reasonable to assume that he was born on or near Archibald’s place of residence, which was the town of Arva within the Townland of Drumalt, Parish of Killeshandra, Barony of Tullyhunco, in County Cavan, Ireland. William would have been 9 years old when the Famine arrived at his doorstep in Killeshandra Parish in 1847, and while he lived in a relatively prosperous area, it appears that there was hardship nevertheless (see this brief article on Killeshandra and the Famine).  It is logical that young William would have journeyed 40 miles away to nearby Fermanagh to seek his fortune, where there was a seaport, a bustling industry, and many other people who shared the surname Elliott (who may have been related, although I don’t yet know how). There he married Mary Ingram, the butcher’s daughter, and started a family, but times were no doubt tough. People were leaving Ireland in droves in the 1850s, bound for North America and the industrial port cities of Scotland and England.

For reasons we can only guess at William and Mary chose Paisley, Scotland as the new home for their small but growing family, and the rest is history.

Posted in Genealogy | Leave a comment

The Irish Connection

John Elliott in 1981.

John Elliott in 1981.

My grandfather John was a big fan of all things Scottish. He continued to speak with a brogue (or tried to) despite living in Canada for 64 years, played the bagpipes (until he chopped two fingers off, that’s another story), and wore a kilt at every opportunity. He styled himself “Head of the Clan” and hosted large family reunions at his acreage near Rocky Mountain House where there was bagpipe music and plenty of Johnny Walker Red (although purists might not regard that as proper Scotch at all, but I digress).

There’s a good reason for all that Scottish-ness. He was, after all, Scottish. He was born in Paisley, an industrial city on the boundary between the Scottish Highlands and Lowlands. He grew up in the Highlands, though. His father was employed as a gamekeeper for the great estates and John, as the eldest son, helped his father as much as he could. The family lived in several locations across the Highlands in the decade following World War One (here is a Google Map I prepared of all the places they lived in those days) and John drank in the culture of those parts with gusto. It was a formative time for him and he remembered it with great longing for the rest of his life.

In my post about The Family That Almost Was, I presented a letter wherein John alluded to a rumor that his grandfather had actually come from Ireland. I have subsequently conferred with various cousins and they, too, received wind of this rumor through their forebears, John’s brothers and sisters, so it must have been fairly common knowledge to that generation. Nobody, though, has been able to give me any details, so I suspect that a rumor is all it was to them. How to confirm or dispel that rumor?

John’s grandfather (the alleged Irishman) was also named John Elliott, so for the sake of convenience let’s refer to him as “old” John. While I had little information about him I did know his date of marriage and his wife’s maiden name, and that led me to their marriage registration:

John Elliott and Margaret Milligan marriage registration, December 31, 1889

John Elliott and Margaret Milligan marriage registration, December 31, 1889

This contains lots of fascinating details, but the one to focus on here is the names of old John’s parents: William Elliot and Mary Ingram. Where did they come from?

A quick search of the Scottish archives doesn’t turn up any likely marriages between William Elliot(t) and Mary Ingram. If, however, we make the assumption that they were married in Ireland, we find the following record in the LDS Church’s database known as Ireland Marriages, 1619-1898:

Name William Elliott
Spouse’s Name Mary Ingram
Event Date 09 Nov 1857
Event Place Lowtherstown, Ferm, Ireland
Father’s Name Archibald Elliott
Spouse’s Father’s Name David Ingram

“Ireland Marriages, 1619-1898,” database, FamilySearch ( : 12 December 2014), William Elliott and Mary Ingram, 09 Nov 1857; citing Lowtherstown, Ferm, Ireland, reference 506, index based upon data collected by the Genealogical Society of Utah, Salt Lake City; FHL microfilm 101,388.

We cannot be 100% certain that this is the marriage we’re looking for yet, because those were extremely common names in that area in that time. But it gives us some clues: namely, that William Elliott’s father may have been named Archibald, and Mary Ingram’s father may have been named David.

Making a long story short, I worked my way through all of William and Mary’s children using census, birth, death and marriage records, and discovered that the marriage record found on the LDS website is, in fact, correct: they were married on November 9, 1857 in Lowtherstown, County Fermanagh, Ireland, part of what was then referred to as the Ulster Plantation. While I have been unable to find a birth or baptism record for old John (the Irish vital statistics search tools are not, I find, as easy to work with as the Scottish ones), it is clear from the Scottish Census that old John was indeed born in Ireland; the family first appears on the Scottish Census in 1871 when old John is 12 and is recorded to have been born in Ireland. He had a younger sister, Mary Jean, also born in Ireland about 1861. Between 1861 and 1864, it seems, the family moved from Ireland to Paisley, and 6 more children were added to the family, all of whom I’ve been able to trace through their birth, marriage, and death records.

So, paydirt! We’re actually Irish. It’s almost enough to make “young” John (my grandfather) spin in his grave.

But that’s not the end of the story. Read The Irish Connection, Part 2.

Posted in Genealogy | Leave a comment

Private Frank Elliott, 1897-1917

Today is the one-hundredth anniversary of the death of my great-uncle Frank (Francis Milligan) Elliott, 1/6 Argyll & Sutherland Highlanders, who died in a field hospital in France on May 21, 1917.

In commemoration, here is a page I wrote that summarizes everything I have been able to find out about his short life.

Francis Milligan Elliott June 3, 1897 – May 21, 1917

Posted in Genealogy | Leave a comment

The Family That Almost Was

I’ve decided to resurrect this old blog of mine with musings about what I’m learning from genealogy.

My grandfather, who died over a quarter-century ago, got bit by the genealogy bug a few years before he died, and he left behind some notes and charts of his research. Nobody else (including me) was at all interested in the family tree at that time, so his notes lay in a box all these years and never saw the light of day until my mother died recently and those papers came to me. I had already started my own research by then, and his notes were a welcome addition to my own growing body of knowledge.

My grandfather, having left Scotland at the age of 17 and spending most of his life in Canada, had little knowledge of his roots beyond his own parents and grandparents and his cousins and uncles and aunts back in the Old Country that he kept in touch with (albeit infrequently). He started getting curious in his old age, so in early 1990 he wrote to his Uncle Archie back in Paisley with questions about the family history, and Archie provided a few details, but all in all it wasn’t much. My grandfather gathered up this information and sent it to the Elliott Clan Historian in Scotland, thinking that he might find something out that way. I have a carbon copy of that letter; it contains some helpful names and dates, and a couple of tantalizing pieces of news that I hadn’t known about.

Letter from John Elliott to Brian Piercy, April 2, 1990

April 2nd 1990
Mr Brian Piercy
Dear Brian.

I have gleaned some more information of the family which may give you a hand for your research. There is record of some members using Elliot one t. The i was not pronounced in my knowledge and time but in that area pronounced Ellot without the i.
John Elliott – married 1866 – 31st Dec. Margaret Milligan. I remember her. had 14 children. listing those who reached maturity in sequence. mother my Great Grandmother.

William, married Robina Woods, 1909, my father and mother
Mary – married Robert Andrews

Family legend is that my Grandfather John came from Ireland.
Grandfather John born 1859
Grandfather John married age 30 to married 1889 31st Dec. born 1866 Margret Milligan married age 23 this may have been Grandfathers second marriage.
I trust the above information may be of some use. Perhaps uncover some skeleton of intrest indeed!!
Thanking you in anticipation
J Elliott

Mr. Piercy wasn’t able to throw much light on the matter, since it appears from his reply that all he had to go on in those days was the IGI (International Genealogical Index, a rather rudimentary list of names published by the LDS Church). I, however, with the magic of the Internet at my fingertips, was able to hunt down all these people, and many more besides, using the extremely helpful vital statistics archive published by the Government of Scotland, ScotlandsPeople. But the two tidbits my grandfather had learned from his relatives back in Scotland, that piqued my interest, were these:

A Family legend is that my Grandfather John came from Ireland.

I’ll write more about that in a future post.

What I wanted to mention here is

B this may have been Grandfathers second marriage

“Grandfather”– i.e. John Elliott Sr., 1859-1915, my great-great-grandfather, about whom I now know quite a bit– probably had very little schooling and worked at manual labor his whole life. I’ve been able to trace his various addresses and occupations through the years using the Scottish Census and the birth and marriage records of his many children. For most of his life he was a brickfield laborer, and as he got older he became a “roadman” (a polite word, I believe, for ditch digger). He died on April 9, 1915 after suffering with a perforated gastric ulcer for a week. He was scarcely 56 years old when he died.

His wife Margaret survived him by almost 15 years and she was an iconic figure amongst the children of my grandfather’s generation. Known as Grandma Elliott or just Granny, it was she who stood on the dock at Port Glasgow and watched her eldest son (my great-grandfather) and his family embark for Canada in 1927. She gave birth to a very large family, and I guess her descendants are still going strong over in Paisley and Glasgow, although I don’t have any contact with them and the ones I have any knowledge of are surely long dead. (But if any of you are reading this don’t be shy about contacting me! I’d love to get caught up.)

John and Margaret were married in Paisley on December 31, 1889, when John was 30 and Margaret was quite a bit younger. Who could this mysterious first wife have been? Starting with the clue in my grandfather’s letter, I searched the ScotlandsPeople archive for John Elliotts who had been married in Renfrewshire prior to 1889 and after, let’s say, 1877 (when John would have been 18, a likely time to start thinking about marriage). What I came up with was this marriage registration, indicating that John had been married just two years previously, on December 9, 1887:

Marriage of John Elliott and Agnes Scott, December 9, 1887

Marriage of John Elliott and Agnes Scott, December 9, 1887

I knew I had the right John Elliott because his parents’ names, William Elliott and Mary Ingram, matched what I had already learned.

But the bride’s name was completely new to me: Agnes Scott, aged 30 (a wee bit older than the groom). She was a threadmill worker– Paisley was famous for the Coats Threadmill in those days (amongst other important industries). The bride’s mother, Agnes Gibb, was deceased by the time of the wedding (she died in 1875 after suffering from “Paralysis” for two and a half years). The bride’s father, William, was a Shawl Weaver. William, I have learned from various Census records, was born in Paisley around 1809, and his wife Agnes was born in Glasgow around 1813.

The wedding ceremony took place at 64 Millarston Drive, Paisley, which was also the residence of both the bride and groom, so it seems they had already moved in together. The wedding took place according to the forms of the Free Church of Scotland which is interesting because I know from other sources that John’s family were Anglican, so Free Church of Scotland must have been the Scott family’s denomination. Witnesses were William Elliott (either John’s father or his younger brother, both of whom bore that name) and one “I. (or J.) Scott” (possibly a cousin of Agnes’; in the 1861 Census there is a Janet Scott living in William Scott’s household, identified as his niece).

If we dig back in the Census records we see that 64 Millarston was actually the home of Agnes’ father William. In 1881 William’s household consisted of young Agnes (then aged 24, working at the threadmill), along with his son Robert (a Fire Clay Moulder) and Robert’s wife Mary, and their two children aged 5 and 7.

John, 22, was still living with his parents and seven siblings in the 1881 Census. And here’s the discovery that made me sit up and take notice: John and Agnes were next-door neighbors. John’s family and Agnes’ family appear on the same page of the 1881 Census; the Elliotts lived just a few doors down (at 62 Millarston). Furthermore, both John and his father William worked in the same industry as Agnes’ brother. I later confirmed (using birth records) that the Elliotts resided at 62 Millarston as early as 1867, when John would have been 8. The earliest mention I can find of the Scotts living at 64 Millarston is the mother’s death registration in 1875, when young Agnes would have been about 16 and John would have been about 14.

The bottom line here is that the Elliott and Scott families were neighbors for at least 12 years, and perhaps longer. John and Agnes may have known each other since childhood.

So, they finally decided to tie the knot late in 1887. Maybe John got Agnes pregnant and the wedding was forced; it’s not unlikely but impossible to know for sure. What happened then?

Agnes and John went on living in her father’s household after their marriage. But sadly, less than four months after the wedding, Agnes quite suddenly died.

Death Registration for Agnes Elliott (Scott), April 4, 1888.

Death Registration for Agnes Elliott (Scott), April 4, 1888.

In mid-March she came down with something called Puerperal Peritonitis. This is (so I have read) a post-partum infection, not necessarily fatal, but frequently so back then. Since a stillbirth should have generated a registration of death, and I can find none in the archives, I surmise that Agnes must have had a miscarriage and then, on top of the sorrow of losing her baby, suffered from an infection for two weeks before dying at home.

And with that, Agnes vanishes from our family history, along with the rest of the Scott family.

This is pretty heady stuff to think about. Agnes might have been the mother of a big family, but instead she died and Margaret came along the following year and took her place. John and Agnes knew each other for a long time before getting married… What kind of bond did they have? Agnes’ father and John’s father must have known each other for at least as long; what was their relationship? Was religion a point of contention between them? Did the fact that Agnes’ father was a (skilled) weaver whereas John’s father was an unskilled fireclay laborer ever come between them? Or the fact that Agnes’ father was twenty years older than John’s father? Did the fact that William was born in Scotland but John’s family were immigrants from Ireland figure in it? I am throwing out these wild speculations for a reason: It’s puzzling that John and Agnes knew each other for so long but waited and waited and waited before getting married. They were, after all, unusually old (29 and 30) at the time of their marriage.

My pet theory is that John and Agnes were long-time sweethearts but her father forbade them to marry because he didn’t think John was “good enough” for his daughter. When he finally relented, her advanced age contributed directly to complications that led to her death, obstetric medicine being as poor as it was in those days. Keep in mind that this is conjecture on my part, based on highly circumstantial evidence. There is absolutely no family oral history that I know of that supports my conclusion, and there are other ways to interpret the evidence.

I wonder how much of this story was known to John’s second wife Margaret. Margaret, though much younger than Agnes, worked at the Paisley threadmill too; could they have known each other? Been friends? Or is it possible that I, nosing around in the archives over a century later, actually know more now about John’s past than my great-great-grandmother Margaret did?

For me, discovering all this was intensely sad, but at the same time exciting. I was glad to be able to bring Agnes’ brief story to light at last. Previously she had been nothing more than a rumor in a letter that few people knew about, but now at least she has a name.

Posted in Genealogy | Leave a comment

Modality? What’s Modality?

Here’s a comment I received from Paul the other day about my database normalization tutorial:

Very nice. Excellent. The only thing I would question would be that a customer could have zero orders. In order to exist as a customer in your system, they must have placed an order at some point. So, the modality should be one.

I always like hearing nice things about my work, but the remark about modality gave me pause. What does that mean? That’s not a word I ever learned at the Database School of Hard Knocks. What is Paul talking about?

Modality, it turns out, is another word for what we database people call cardinality, or “the number of unique things in a set”. You might also hear it referred to as degree amongst erudite set-theory folks. When we talk about one-to-one relationships (or one-to-many, many-to-many, etc.), we’re talking about the modality or cardinality of the relationship between things in different sets. In other words, one row in a table can be related to rows of data in another table. It doesn’t have to be, but it can be, depending on what real-world relationships you are trying to model.

For instance, suppose you have a database of people, and EMAIL_ADDRESS is an optional field in your database. A person might have an email address, or she might not. We say there is a one-to-zero-or-one relationship, because one person can have zero or one email addresses.

If, on the other hand, EMAIL_ADDRESS is not optional, the relationship becomes one-to-one because one person must have one email address.

If, on the third hand, a person can have any number of email addresses, we call that one-to-many, because one person can have many email addresses. If the email address is still optional, we call that one-to-zero-or-many.

Each of these possibilities can be shown in an Entity Relationship Diagram or ERD, which is a visual representation of the relationships in your database. There are different ways of showing the relationships on a diagram, and terrible religious wars have been fought over which system is best. The system I chose for my tutorial uses the following symbols:

  • Every A has exactly one B.
  • Every B has exactly one A.
  • Any A can have no more than one B; maybe none.
  • Any B can have no more than one A; maybe none.
  • Every A has one or more Bs.
  • Any B has no more than one A; maybe none.
One and only one-to-many
  • Every A has one or more Bs.
  • Every B has exactly one A.
One-to-zero or many
  • Any A can have any number of Bs; maybe none.
  • Any B can have no more than one A; maybe none.
  • Every A has one or more Bs.
  • Every B has one or more As.

Notice how the symbols (which are sometimes called “crowsfeet” for reasons that should be obvious) change the cardinality of the relationship. You should understand that this is not an exhaustive list of entity relationships, and again, there are many different sets of symbols out there.

So what’s all this got to do with my tutorial?

Check out Figure J, the diagram representing Third Normal form. Originally I had drawn it like this:

Third Normal Form: incorrect relationship

See if you can spot the problem.

Give up?

The symbols connecting ORDERS with CUSTOMERS say,

Every order has exactly one customer


Every customer can have any number of orders; maybe none.

In other words, a customer could exist in the database without ever placing an order.

I call this a “problem” rather than a “mistake” because there are lots of databases out in the real world that have customers who have never placed an order. Although logically, the definition of “customer” is “someone who has bought something from you”, I can think of all sorts of reasons why you might want to keep track of order-less customers… maybe their order is pending. Or maybe the customer is just a sales prospect, in which case they should perhaps go into a PROSPECTS table, and then migrate to CUSTOMERS after the order is made… or maybe you could add a STATUS field to the table to differentiate between pending customers, prospective customers, and actual customers… The possibilities are delightfully many, as are the possible solutions.

But guess what? I don’t care. At least not in the context of my tutorial. I wanted things to be clean and simple– I just wanted to model a stack of invoices, where every invoice has a customer, and every customer has made a purchase. But by selecting the wrong crowsfoot symbol, I introduced a whole big mess that would need considerable explanation that would dilute the simplicity of the tutorial.

So, what to do?  Easy; just change the symbol connecting CUSTOMERS to ORDERS. Here’s my new Figure J.

Third Normal Form

Which says,

Every order has exactly one customer


Every customer has one or more orders.

I’m grateful to Paul for pointing out this inconsistency.

Posted in Databases | 3 Comments

The whole truth behind candidate keys

A reader of my tutorial on the 3 Normal Forms recently asked:

The question is: why was customer_id not used as part of the composite primary key (order_id, customer_id , item_id) in 1NF in the first place?

It’s a good question. The questioner identified one of the normalization issues that I simplified in order to make (I hope) my tutorial more readable.

Going back to the tutorial, we said that every table row has to have a column or group of columns that uniquely identifies it, right? We called this the Primary Key. Formal database jargon refers to this as a “candidate key”. There could be more than one candidate key in a table; the one that we actually select becomes the Primary Key.

In truth, that’s only part of the story. The candidate key has to have two properties: First is Uniqueness (as we just stated); the second property is called Irreducibility (or Minimality).

Let’s consider some examples of the three columns the questioner mentioned: order_id, customer_id, item_id:

 order_id  customer_id  item_id
125 56 563
125 56 851
125 56 652
126 2 563

Does this set of columns satisfy the Uniqueness property? Yes, it does. Every combination of {order_id, customer_id, item_id} is unique in our table, and this makes sense according to the invoicing rules of our imaginary business: Every item ordered by a customer appears once and only once on an invoice, and invoices are never duplicated (if a customer makes a duplicate order, we issue a new invoice).

The trouble is, it is redundant to say this. We don’t call {order_id, customer_id, item_id} the candidate key because customer_id isn’t required to satisfy the uniqueness requirement; each row is already uniquely identified by {order_id, item_id}. This is what is meant by the Irreducibility property: If a set of columns can be reduced to a smaller set of columns that still satisfies the Uniqueness property, then it does not qualify as a candidate key.

Here’s a somewhat more formal definition of these two properties (quoted from C.J. Date’s An Introduction to Database Systems, 7th Ed., p. 258):

Let K be a set of attributes of relvar R. Then K is a candidate key for R if and only if it possesses both of the following properties:

  1. Uniqueness: No legal value of R ever contains two distinct tuples with the same value for K.
  2. Irreducibility: No proper subset of K has the uniqueness property.

If you’re unfamiliar with database set theory terminology, here relvar (“relation variable” in case you’re curious) means “table” and tuple means “row”. A proper subset is a subset that does not include the whole set itself (in other words if you have a set of things {a,b,c} then {a} is a subset, {a,b} is a subset, {a,b,c} is a subset, and so forth; a proper subset includes {a}, {a,b}, etc., but does not include {a,b,c}).

I don’t think it’s necessary to get into this kind of detail in the tutorial itself, but it’s worthwhile mentioning and I’m glad someone asked.

Posted in Databases | 2 Comments

3 Normal Forms Tutorial update

Many people have, over the years, both in the blog comments and in private messages, objected to my contention in the Normal Forms tutorial that ITEM_PRICE leads to failure of Second Normal Form. I always go back and forth about this, and I generally tell people something like “it depends on the business rules, it’s not a matter for Normalization to solve, I wanted to keep things simple,” etc. Enough people have pointed this out that I finally decided to spell out the problem in the tutorial itself, under the NF2 discussion. Hopefully this will not prove too confusing for beginners, while at the same time keep the advanced readers satisfied!

Posted in Databases | Leave a comment

3 Normal Forms: Spanish Translation

I am happy to announce that my Three Normal Forms tutorial has been translated into Spanish by Roberto Fernandez. You can download it here.

Posted in Databases | Leave a comment

Ancient Treasure

In my apartment building people often leave books they no longer want in the lobby for others to pick up if they want. Usually it’s nothing interesting, but the other day someone left a truly incredible artifact: the Microsoft Excel Version 1.04 manual, from 1986.

This is a true historical document. It’s not the absolutely first version of Excel, but it’s almost the first. It includes the installation media (800KB floppy!) and it’s absolutely pristine. It feels like this thing belongs in a museum.

But the most interesting part is the very last page: a bug report form, for sending “software bugs, documentation errors, or suggested enhancements”. Best of all: Mail the form to Microsoft!

This thing is a treat. Shall we start the bidding at, say, $0.43?



Posted in Computers | 1 Comment