GraveRecorder - Fleshing out the Database
I spent yesterday scribbling in my notepad about how I thought v1 of this system might look but that cribbing I mentioned in my last post was just of a single table with no relationships. In addition, I didn’t think about grave ownership or how I would link to many cemeteries so it couldn’t be right, at least not quite. I needed to beef things up a little.
So, here’s my first thoughts on what the database should be able to represent using 4 main tables: cemetery, grave, image and user.
Cemetery
Each cemetery has a name. Later we might want to add address, images, location etc. but that can wait for now.
Grave
Each grave (or memorial) is associated with a person, which is represented as one or more first names plus a surname. Sometimes the actual first name or names aren’t known, so this can be optional or just represented as initials.
That person will be born in a year (sometimes, rarely, unknown) and died similarly in a year.
Some headstones, for instance, give actual dates of the year that the events happened. I’ll need to find a way to differentiate that in the UI but for now I only want to store two dates and not split it into year, month and day etc.
A grave has one owner. That is, the person that added the grave onto the site. On public sites, this tends to lead to all sorts of problems where someone doesn’t agree with how a grave is portrayed, or wants to edit it but can’t. Here, I’m really catering for people that may not want it to be public anyway.
Each grave belongs to (or resides in) a cemetery (or crematorium). I was thinking about this being a requirement, but actually, even though we know a person was either cremated or buried, we don’t always know where. In that case, I’m not sure this will be the best tool to record it, but nonetheless…
A grave may or may not be private. Private will mean that it cannot be found from the main site without the owner either logging in or expressly sharing it. The default will be private.
There will also be a plot. This is the location where you can find the grave and typically takes the form of a letter and numbers. This isn’t always known or shown on the grave, so is optional.
There shall be an inscription (e.g. Here lies…). It may or may not be present.
The position on mother Earth will be obtained using a latitude, longitude and what-3-words label, in case someone uses that to mark locations instead. Again, optional, but ideally, it will be filled in.
By the way, if you’re not familliar with what3words, it’s an alternative way of representing locations on Earth. In this case, it’s a name given to a 3m square area and consists of 3 easy to remember words, separated by a period. What 3 words…get it?
Lastly, each grave is linked to one cemetery (or crematorium). I can’t imagine that being any other way, can you? :-)
Images
Each grave may have zero or more images associated with it.
Each image is associated with one grave. This might be a problem later if we have a picture of a memorial which can consist of many names, not necessarily related. The workaround would be to upload the image again, but I’m not keen. Alternatively, perhaps we chop up the image of names to just show the pertinent one? I’ll need to think about this later.
An image is uploaded by a user. That user owns the image. This in itself is a minefield with many family history groups being quite unhappy about who owns the copyright etc. to uploaded images. My feelings are that they (the uploader) owns them but I can understand why sites want to monetise stuff. People behave as if there are no costs to these free sites, but of course that just ain’t so.
Moving on, an image may be the primary image. That is, the one first shown on the grave page. I did think it might be easier to base it on the earliest image uploaded, but what if you make a mistake. Been there.
Users
A user may register for the site and create no grave entries, but if a grave is created, it must be owned.
Lastly, in addition, all tables will have created_at and updated_at fields consisting of date/times. Oh, and before I forget to mention, I haven’t added in all the fields, especially not Laravel default ones.
So what does this look like as an ERD?
This was produced in Lucidchart in case you’re interested - an excellent free resource if you only create a few diagrams.
I’ve already hinted that I might need to adapt as time goes one, but for a first (second?) draft, it’s a good start.
Next step: create the migrations, throw a few seeded values in and see if it breaks.
Hi! Did you find this useful or interesting? I have an email list coming soon, but in the meantime, if you ready anything you fancy chatting about, I would love to hear from you. You can contact me here or at stephen ‘at’ logicalmoon.com