help with database design and relationships

[Jen0dorf]

Hi clever people

I know this is a bit of a cheek so I’ll apologise in advance

Sorry;-)

Ok As a retirement project I’ve taken on a Cemetery Database that need updating.
From my previous posts I have discovered that the Database is controlled by an exe written in VB or C++
So it seems to me that it’s best to “start” from Scratch and get rid of the EXE part as I have no access to the code – and wouldn’t understand it anyway!
The two tables seem to contain duplicate fields which seems pointless in a relational database
The criteria I have is
Each plot name is unique


Grave numbers are not unique and may be repeated across plots
Deceased names are not unique
Memorials are not unique to one single Grave
Each grave can have multiple occupant’s
Each grave can have multiple memorials
So I’ve spent the day surfing the web and youtube and discovered linking tables and I’ve come up with the attached plan

Is this viable and will all 39201 records have to be re entered or can I do this using the two tables and just import the exisiting tables as below and still extract all the information needed?

Ie Search by name and show all deceased with that name
Search by grave and show all occupant’s and all memorials for that grave.
search by memorial and show all related graves
Really do appreciate the help

Regards

Ian


Graves Table 39201 records

Mlink

Plot

Graveno

Links

Surname

Forenames

DayOfDeath

MonthOfDeath

YearOfDeath

DayofBurial

MonthOfBurial

YearOfBurial

Age

Inscription

PP

Notes

Memorial table 17861 records

Plot

Links

GraveNo

Mlink

Inscription

[ranman256]

Looks pretty good.
now build a form to search on
name
or
plot
or
grave

this will open a continuous form, then you can pick one in the list to open the detail form to see all info on 1 item.

[orange]

A few questions:

Will there be new Plots? Are there queries by Plot vs Grave?
What is mLink?
Why store Day, Month and Year as separate fields? Seems DateOfBirth, DateOfDeath, DateOfBurial covers all.
With DateOfBirth and DateOfDeath, Age can be calculated, and not saved necessarily.

How many users? Who will maintain/operate the database/application.

You can mock up a database based on your model --occupants Porky Pig, Daffy Duck etc.

Then mock up some queries to see if you can achieve your requirements.
Most of this can be done with pencil and paper with your model.

You may get some ideas from this post dialog concerning a "similar" project.

If you would like a refresher on database/table design, here is a tutorial to work through.
It has a solution, but the key is the underlying procedure/process he uses. It can work with any database.

Get your tables and relationships established to meet your needs. Then, move to forms etc.
Good luck.

[Jen0dorf]

Hi

thanks for the encouraging responses

There wont be new plots but may be a few new graves but not many in reality the database is to help family history research rather than current deaths. However in the interest of accuracy It will be kept up to date by me every so often. In the UK graves are not "for Life" but a specified no of years after that another person can be buried on top so to speak.

As to the separate day of death, month of death etc that puzzled me, as I could see no reason but thats how the original tables were set up.

Mlink is a linking number letter sequence which is common to both tables - I assume short for Memorial link. On first inspection I thought it was the primary and foreign key in a relationship but I thought wrong - a fact confirmed in another post.

Point taken about storing age!! I have so much to learn.

Not sure what you mean by queries plot v graves but I suspect not. Simply the cemetery is divided in plots, red, yellow, blue, green, memorial garden etc etc each of those plots will have numbered graves 1,2,3,4, .

Would be interested in the tutorial can you post the link please.

Once again thanks guys you input is greatly appreciated.

Ian

[orange]

Good stuff Ian. Sorry I forgot the link to the tutorial. Here it is and there are others on this page see ExamplesThe question re plot vs grave was if there was anything specific to store with tblPlot.
For example -- how many Plots exist. That would e a query against Graves as it is laid out. If there was a tblPlot, it would be a count. But if Plot per se is not a major item of interest, it wouldn't need to be a separate table.

Did you look at the messages in the post (link) I gave? I gave links to other Cemetery/Grave software.

Here is a link to a very useful Search form and code.

[Jen0dorf]

HI

thanks for the reply

I have looked at the other post very interesting, I messaged the poster so see if we would talk.

As to the number of plots I am wary because it is controlled by our local council and whilst at the moment there are 33 plots with varying numbers of graves there is nothing to prevent them splitting a plot in the future to 2 new plots if you follow.

I did look at the software you listed and apart from being costly - in the clubs terms - I sort of hate retirement and like to expand my knowledge to prevent the onset of day time TV disease.! in my past lives I did web sites etc so had/have a limited knowledge of mysql and alpha databases.

As this project came up I thought why not, so I'm using it as a learning curve and hobby.

And once it is done then it will assist anyone in the world who needs that information as the society give the information our for free via their forums.

Anyway I'm off to add some sample data as per previous posts and search for a way of combining the d, month and year fields into one fiels and show it as a date.
I found a merge button so ....

thanks again for the assist

Ian

[orange]

Ian,

The key is to get your tables designed to meet your needs. If the tables and relationships are incorrect, you'll be doing "workarounds" for most things. So, tables first.

Good luck. We're here to help if you have any questions.

[orange]

Ian,

How are things going?
What is current status?
As mentioned previously, get your tables and relationships set up to meet your requirements.
Create a model; vet and adjust the model with some sample scenarios; then develop the database.
Don't be too quick to jump into Access.

[Jen0dorf]

Hi

things are progessing. I followed your advice and setup some test data and created some basic reports and forms - ok really basic but managed to extract the information required.

I decided not to combine the day, month and year fields as advice on the forum seemed to indicate that access has issues with non UK date format and a lot of the searches are based on deaths in a year, or deaths in a month or deaths in a date and a year.

Current status is working on importing the two original tables into three with a joining table. Had lots of ideas - no doubt all good - and it seems as iff there are many ways to achieve something.

Anyway as it stands I'm about to try to import 32000 records into two tables and create the links.

JUne7 has posted ideas which I'm using.

regards

Ian