Database Design on a Burial Register (1 Viewer)

cjc1867

New member
Local time
Today, 15:11
Joined
Nov 28, 2020
Messages
8
Hi
I haven't designed nor built an Access database in 20+ years. Last time was at Uni, so now I am struggling. I would like to get a working model in Access and then see if I can convert it to a format that can be used in a WordPress website i.e. mySQL and PHP.

Just wondering if anyone could help and advise on what I have done so far in the Relationships image (Attached).

Attached shows you the burial register and the relationships of the tables. I have assumed the following below:

  1. A person can only be buried in one grave as in this cemetery, as shown in the burial register.
  2. Many people can be buried in the same grave.
  3. Abode: Many people can live at 1 address.
  4. Date: A person will only have one burial date.

I want to include Grave numbers (1 to 101, so far) that are shown on a map of the cemetery.
I want to include the Grave Inscription.
I want to include Burial register number, which can be used as an unique ID.
Just for this burial register I just want to include the main Vicar, I know from newspaper articles, that at times more clergy performed the burial.

The burial register has 1 to 282 people buried between 1813 to 1970, but there isn't enough graves that are visible on the map (produced in 1986) or today. At the moment I have just given them a grave number of 0 (Zero) as it was easier to group them together. I may have to give them a grave number at a later date.

I hope I have explained it properly.

Thanks

Colin
 

Attachments

  • 15-Llandyfeisant-Church-1813-page-28-29.png
    15-Llandyfeisant-Church-1813-page-28-29.png
    1.2 MB · Views: 350
  • relationships.png
    relationships.png
    26.7 KB · Views: 364

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:11
Joined
Feb 28, 2001
Messages
27,003
OK, a design question here. The topic is "candidate keys."

You say the "burial registry number" is a candidate. But I am thinking of something that happened at my wife's family plot. A family member was buried in the family crypt. In south Louisiana, the habit is that you can bury as many as you want in the crypt. They must be buried in a wooden box that has not been treated in a way to prevent decay. When the crypt has two coffins in it, the next burial causes the oldest remaining coffin to be dumped into the pit under the crypt and the now-empty coffin will be burned. Then the more recently placed coffin is moved down and the new coffin is on top. HOWEVER, sometimes a family member realizes that the loved one will be "dumped" and gets kind of crazy. SO they arranged for their loved one to be moved to a "permanent" crypt where the dumping doesn't happen. If that case arises, what effect if any does moving the deceased to a different place have on your burial registry? I am asking because I want to verify the validity of the burial registry number as a potential key.

In your diagram, you have a few questionable cases.

1. You have "pagenoID" and "pageumber" in a table with only two fields. Why do this when you can just store the page number directly? It will take up no more room than the ID, in all probability.

2. This is just a question that I suspect might be true, but I'm verifying. When you have a pointer to the deceased's street address, how often does it occur that you repeat this information by having another deceased from the same address? I know that in small country cemeteries there are a lot of family members. If you have some repetition or even if you anticipate some repetition, then you aren't wrong - but consider this just a second pair of eyes checking your work.

3. You have "graveID" and "grave number" in a table with only two fields. Again, why do this when you can store the grave number directly.?

4. There is a question in my mind about titles. This is actually a reason for you to keep the grave-number table separate, because I don't think the title applies to the deceased, it applies to the grave. So that at least appears to be a misdirected relationship. Surely the same title would apply to the grave regardless of whom you buried there.

If this sounds suspiciously like I know too much about country cemeteries, it is because that scenario I mentioned above applied to a member of my wife's family and I had to help her get it all straight.
 

cjc1867

New member
Local time
Today, 15:11
Joined
Nov 28, 2020
Messages
8
Hi and thank you for responding.

I live in the UK.

When my stepfather was buried, my mother said to the Funeral Director that she and my younger sister would be buried in the same plot or grave. To allow this, they made the plot at least 3 coffins deep, one for each coffin.

So when my mother died last year, the plot or grave was opened up to at least 2 coffins deep. When my sister dies, they will open the plot or grave to 1 deep. No more graves can be added now to that grave when that happens.

The burial register number is unique to that one person so that will be a good ID to use.

Yes some tables only have 2 values to save but I want to leave room to add more fields if needed i.e. A grave has a headstone inscription

1. I wanted to add all the page numbers 1 to 36 to a combo box so that I could just select it and wouldn't have to retyping it.

2. Deceased Address: There are quite a number of people who belong to the same family and were living at the same address. Again, I could have added the addresses via a combo box.

3. Grave Number: Graves are from 1 to 101 at the moment and again a simple combo box could be prepopulated.

4. As I have mentioned before, I want to use a combo box with Mr, Miss, Mrs, Ms, Dr, Lord, Lady etc. The cemetry has 4 Lords and their wives plus children.

Using a combo box would mean I wouldn't have to keep typing the same information over and over again. We were taught it this way to save time.

Like I say, I haven't built a database for well over 20 years so trying to remember what I used to do.

Kind regards

Colin
 

mike60smart

Registered User.
Local time
Today, 15:11
Joined
Aug 6, 2017
Messages
1,899
Hi and thank you for responding.

I live in the UK.

When my stepfather was buried, my mother said to the Funeral Director that she and my younger sister would be buried in the same plot or grave. To allow this, they made the plot at least 3 coffins deep, one for each coffin.

So when my mother died last year, the plot or grave was opened up to at least 2 coffins deep. When my sister dies, they will open the plot or grave to 1 deep. No more graves can be added now to that grave when that happens.

The burial register number is unique to that one person so that will be a good ID to use.

Yes some tables only have 2 values to save but I want to leave room to add more fields if needed i.e. A grave has a headstone inscription

1. I wanted to add all the page numbers 1 to 36 to a combo box so that I could just select it and wouldn't have to retyping it.

2. Deceased Address: There are quite a number of people who belong to the same family and were living at the same address. Again, I could have added the addresses via a combo box.

3. Grave Number: Graves are from 1 to 101 at the moment and again a simple combo box could be prepopulated.

4. As I have mentioned before, I want to use a combo box with Mr, Miss, Mrs, Ms, Dr, Lord, Lady etc. The cemetry has 4 Lords and their wives plus children.

Using a combo box would mean I wouldn't have to keep typing the same information over and over again. We were taught it this way to save time.

Like I say, I haven't built a database for well over 20 years so trying to remember what I used to do.

Kind regards

Colin
Hi Colin
Can you upload a zipped copy of your database?
 

plog

Banishment Pending
Local time
Today, 10:11
Joined
May 11, 2011
Messages
11,613
Tables with only 1 real field of data (auto numbers aren't real data) shouldn't exist. You don't need a table to populate a combo box, for example:

1. I wanted to add all the page numbers 1 to 36 to a combo box so that I could just select it and wouldn't have to retyping it.

Omit the In the [Grave Numbers] table and change the [GraveID] field in [Deceased] to [GraveNumber] (don't use spaces in field/table names). Then, in Design View of the table go to [GraveNumber] click on the Lookup tab at the bottom and set these items:

Display Control -> Combo Box
Row Source Type -> Value List
Row Source -> 1,2,3,4,5,6...36

Save the table and the table itself will then have a [GraveNumber] field with a combo box with 1-36 values for it. Then when you create a form based on that table using the Wizard it will know to use a combo box with those values as well. Even without using the Wizard you can use the above process to set your Form's combo box to achieve that result.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:11
Joined
Feb 19, 2002
Messages
42,986
As long as Title is the form of address, I agree with it being a separate table so you can control its values using a combo.

Do you want to keep the church and address with Reverend? This would be helpful to people doing genealogical research since some churches keep extensive records and this would save a step for a researcher.

I probably wouldn't have separated the address into a separate table. This is normally done in applications where you have multiple reuses of the addresses. You might reuse the address if multiple people of the same family are buried in the cemetery but I'm guessing that you won't actually be doing this because the Address table would actually need a family designation to make it useful to reuse. So, don't separate address into a separate table.

How is the RegisterNo different from the GraveID? It doesn't seem that you would need two of them unless the Register number is intended to be a junction table between Deceased and Grave in which case, you have the relationship backwards.

Cemeteries are generally broken up into blocks (I don't know what they are actually called) so you might include a block table and a BlockID reference in the Grave table. This also helps people to locate a grave more easily. The blocks should have plaques with their number/name on them to help mourners find their loved ones.

Does it make sense to have a next of kin table that gives you a way to contact someone if something should happen to the grave that the family might want to know about?

I can understand why you want a table for Page Number and I'm fine with that.

I also see where Doc was going with "Title". Someone owns the grave and it may not be the person buried there so it is important to have contact information for them should you need to communicate with them between the time of purchase and when the grave is filled. Maybe this should go in the Grave table. You will probably have to deal with multiple owners so plan for that.

I've mentioned two potential other sets of people that you might want to keep track of. They have many of the same characteristics. That makies them candidates for a single table. I would include a type code for Reverend to facilitate building a combo. As far as the others go, they can have multiple uses. They can be related to "Owner" of the plot. They can be related to someone buried in the plot. They can be the reverend who officiated.

And some final points.
1. Never use special characters or embedded spaces in your object names. You have spaces in both table and column names. Removing them is best.
2. Keep DOD in addition to DateBuried.
3. Keep DOB rather than age. It is almost always engraved on the marker so it is generally known. Keeping an age field doesn't hurt and age can be calculated from DOB or just entered as an estimate if DOB is unknown.
4. You could keep a sequence number on the Deceased table to indicate the order of the burials. You can guess this from the DateBuried but not if multiple people were interred on the same date.
5. I like to prefix my table names with "tbl" to separate them visually from queries which are prefixed with "q". Not required, just helps when you are writing code to clarify that you are opening a table or a query.
 

cjc1867

New member
Local time
Today, 15:11
Joined
Nov 28, 2020
Messages
8
Tables with only 1 real field of data (auto numbers aren't real data) shouldn't exist. You don't need a table to populate a combo box, for example:



Omit the In the [Grave Numbers] table and change the [GraveID] field in [Deceased] to [GraveNumber] (don't use spaces in field/table names). Then, in Design View of the table go to [GraveNumber] click on the Lookup tab at the bottom and set these items:

Display Control -> Combo Box
Row Source Type -> Value List
Row Source -> 1,2,3,4,5,6...36

Save the table and the table itself will then have a [GraveNumber] field with a combo box with 1-36 values for it. Then when you create a form based on that table using the Wizard it will know to use a combo box with those values as well. Even without using the Wizard you can use the above process to set your Form's combo box to achieve that result.
Hi
I left some tables with only one real value as I wanted to expand or add more fields at a later date. The Grave Number table, I have renamed and called it Grave.

Now, I can add the Grave Number as the Primary Key as it will be unique i.e. 1 to 100. Graves that aren't on the map but in the burial register, I will need to give them a Grave Number to. We do have 1 grave without a headstone and the grand daughter has shown us where it is located, so I added that to the map. Other graves not on the map, so far have not been identified.

So my table so far:

GraveNumberID - Primary/Autonumber - Location on map
OriginalInscription - Long Text - Add original Headstone inscription
AmendedInscription - Long Text - Amended Headstone inscription
HasHeadstone - Yes/No - Was a headstone placed?
Headstone Visble - Yes/No - Is the headstone visible or has it fallen over?
Photo - Attachment - Add photo of headstone if it exists.
Comment - Long Text - Any text

Some headstone Inscriptions were incomplete or contained errors when taken in the 1986 Project. After doing the research, the inscription can be amended.

For example, Grave 1's original Inscription:

John Lewis Thomos of Caeglas
Died August 13th 1910 Aged 48

After some research and amended:
John Lewes Thomas of Caeglas
Died August 13th 1910 Aged 48

So it would be better that we remain both versions.

The attached PDF file shows what information St Teilo Church's databse has per headstone but it's just a document and I don't think I need or want to go that far with it.

Colin
 

Attachments

  • Lewis-Price-headstone-information.pdf
    1.7 MB · Views: 318

mike60smart

Registered User.
Local time
Today, 15:11
Joined
Aug 6, 2017
Messages
1,899
Hi Mike
As requested a copy of the database.

Colin
Hi Colin
I amended the Form you had created as it was based on all of the tables in your database.

I created a Main Form based on the Grave Number table
I then created a Subform based on your Deceased table.
These are linked by the GraveID

I then created a Subform within the Deceased Subform to record details of the Address.

Come back with any questions.
 

Attachments

  • Burial-Register-1813-1970.zip
    46.3 KB · Views: 319

cjc1867

New member
Local time
Today, 15:11
Joined
Nov 28, 2020
Messages
8
As long as Title is the form of address, I agree with it being a separate table so you can control its values using a combo.

Do you want to keep the church and address with Reverend? This would be helpful to people doing genealogical research since some churches keep extensive records and this would save a step for a researcher.

I probably wouldn't have separated the address into a separate table. This is normally done in applications where you have multiple reuses of the addresses. You might reuse the address if multiple people of the same family are buried in the cemetery but I'm guessing that you won't actually be doing this because the Address table would actually need a family designation to make it useful to reuse. So, don't separate address into a separate table.

How is the RegisterNo different from the GraveID? It doesn't seem that you would need two of them unless the Register number is intended to be a junction table between Deceased and Grave in which case, you have the relationship backwards.

Cemeteries are generally broken up into blocks (I don't know what they are actually called) so you might include a block table and a BlockID reference in the Grave table. This also helps people to locate a grave more easily. The blocks should have plaques with their number/name on them to help mourners find their loved ones.

Does it make sense to have a next of kin table that gives you a way to contact someone if something should happen to the grave that the family might want to know about?

I can understand why you want a table for Page Number and I'm fine with that.

I also see where Doc was going with "Title". Someone owns the grave and it may not be the person buried there so it is important to have contact information for them should you need to communicate with them between the time of purchase and when the grave is filled. Maybe this should go in the Grave table. You will probably have to deal with multiple owners so plan for that.

I've mentioned two potential other sets of people that you might want to keep track of. They have many of the same characteristics. That makies them candidates for a single table. I would include a type code for Reverend to facilitate building a combo. As far as the others go, they can have multiple uses. They can be related to "Owner" of the plot. They can be related to someone buried in the plot. They can be the reverend who officiated.

And some final points.
1. Never use special characters or embedded spaces in your object names. You have spaces in both table and column names. Removing them is best.
2. Keep DOD in addition to DateBuried.
3. Keep DOB rather than age. It is almost always engraved on the marker so it is generally known. Keeping an age field doesn't hurt and age can be calculated from DOB or just entered as an estimate if DOB is unknown.
4. You could keep a sequence number on the Deceased table to indicate the order of the burials. You can guess this from the DateBuried but not if multiple people were interred on the same date.
5. I like to prefix my table names with "tbl" to separate them visually from queries which are prefixed with "q". Not required, just helps when you are writing code to clarify that you are opening a table or a query.
Hi Pat
I did reply to you earlier on and then managed to delete it as it got mixed up with another reply to Plog.

I think the Reverend (Vicar or Curate) would have his own address i.e. The Vicarage, Llandeilo and maybe the Grave would have it's own address i.e. Llandyfeisant church, but the database will only be used for this cemetery so don't know if that's needed or not. The address table could then cover Grave address, Person being buried's address and the Reverend or Vicar's address...

The addresses are reused as some of the graves are family graves and they lived in the same house so I thought it would be better to have its own table.

The grave number is only on a map that was produced in 1986 and it should be linked to the burial register as a location for each person buried there. I need to link the grave number to all of the burial register numbers especially if it's a family grave. There are only a few graves that have only one person in so only one address or abode.

Some of the people buried at the cemetery and are mentioned in the burial register are not on the map. Maybe they didn't have a headstone, or that it has fallen over and under the grass/weeds etc or another grave was put there and now has someone elses headstone on it. We have another burial register from 1784 to 1813 and only a few have a marked grave so where are the other's buried? It has been mentioned that when they were going to bury someone, they had to find a place that was vacant by another grave. In doing so they revealed a roman wall and now it is known that the church sits on an old roman site.

Llandyfeisant Church has been closed since 1961 and hasn't been used as a church. It was reopened in the 1990's by the Wildlife Trust as a Visitor centre and it closed again after a few years. Some of the graves are only visited by some of the family but the majority of the graves have been forgotton.

You're quite right Pat about naming conventions, I realise that now so I will change the tables with a prefix of 'tbl' and if I get to that stage will have queries with 'q' but I think we use to use 'qry' then the name of the query.

Separate table for Titles: Just thinking from Datasheet View or Form View, if we need to add another person's title that's not in the combobox then it would be easier to view the Titles Form and add it there. Carolyn, wouldn't know how to add new titles so this way, she could go to the Titles form and add there. We have so far, Mr, Mrs, Miss, Ms, Rev, Lord, Lady, Hon., for the moment.

Sadly, not many people visit their family graves, although I have contacted a few on Ancestry and Find My Past before and they have been interested in visiting their family. I have normally have contacted them to point out that they have made errors and would they please check them. About 5 public family trees on Ancestry all have the wrong death for Rev Lewis Price's daughter. She died in 1886 and they have 1930's with the wrong family for her. One person got back and said thank you and that has given him the motivation to update his tree.

Colin
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:11
Joined
Feb 19, 2002
Messages
42,986
If you are going to reuse the address table, and I don't recommend that you do, you need a way of finding the family associated with it. But the biggest issue is that if you change the address because someone moves, it changes the address for EVERYONE ELSE unless you handle this correctly. For what you are going to save, I really would not use a separate address table. This concept makes a lot of sense in a big application where you have dozens of contacts for example with the same company address.

The sample done by mike has the deceased as the 1-side so therefore, the address can never be used by any other person. The relationship needs to go the other way if you want to assign the same address record to multiple people. Also, if you are planning on using the address table for multiple classes of people, they all need to be in the same table so the deceased, the reverend, the owners, the next of kin ALL need to be in a "peoples" table to make your Address table concept work.
 

cjc1867

New member
Local time
Today, 15:11
Joined
Nov 28, 2020
Messages
8
Hi Colin
I amended the Form you had created as it was based on all of the tables in your database.

I created a Main Form based on the Grave Number table
I then created a Subform based on your Deceased table.
These are linked by the GraveID

I then created a Subform within the Deceased Subform to record details of the Address.

Come back with any questions.
Hi Mike
Thank you for creating the database for me, I have had a look and it looks great.

My copy of Office is now saying that I don't have a proper licence, so I can only view it in read only mode.

I need to get a genuine copy of Office before I can start using it.

Colin
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:11
Joined
Feb 19, 2002
Messages
42,986
Don't forget what I said about the relationship with the address table in the sample being backwards. It will not work as Mike built it for your purpose.
 
Last edited:

cjc1867

New member
Local time
Today, 15:11
Joined
Nov 28, 2020
Messages
8
Hi
I have got the genuine copy now so I will look at the database again that Mike kindly develop for me and see if it helps me remember what I did the last time.

Most importantly, read through all these answers again.

Thanks everyone for the help.

Colin
 

cjc1867

New member
Local time
Today, 15:11
Joined
Nov 28, 2020
Messages
8
Hi Colin
I amended the Form you had created as it was based on all of the tables in your database.

I created a Main Form based on the Grave Number table
I then created a Subform based on your Deceased table.
These are linked by the GraveID

I then created a Subform within the Deceased Subform to record details of the Address.

Come back with any questions.
Hi Mike
The burial register number is unique per person in the burial register so I would have thought the relationship between the 2 tables would be 1:1.

Also, the address table's relationship is the wrong way round, as I will be using the same address a number of times.

I will have a go changing the deceased and address table but I can't remember how to do the other 1:1 table's relationship.

Again and thank you for creating this database as it will help me remember what I used to do.

Colin
 

mike60smart

Registered User.
Local time
Today, 15:11
Joined
Aug 6, 2017
Messages
1,899
Hi Mike
The burial register number is unique per person in the burial register so I would have thought the relationship between the 2 tables would be 1:1.

Also, the address table's relationship is the wrong way round, as I will be using the same address a number of times.

I will have a go changing the deceased and address table but I can't remember how to do the other 1:1 table's relationship.

Again and thank you for creating this database as it will help me remember what I used to do.

Colin
Hi Colin

The burial Register Number would need to be changed from a Combobox to an Input Control where each Number would be Unique.

I have also just sent you a Conversation message.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:11
Joined
Feb 19, 2002
Messages
42,986
1:1 relationships are PK to PK. One of the PK's (the "master") is an autonumber and the other is a long integer.
 

Users who are viewing this thread

Top Bottom