Switchboard

[orange]

Hi Steve,

This brings to mind the bacteria/strains storage study with Dave(accessed) back in 2016 -just fewer conditions and occupants.

[ssanfu]

@orange,

I had forgotten about that! It was really a puzzler - I think I had to have 3 days of bed rest...

I hope Dave (accessed) got help for all of the voices....


But, yes, this columbarium dB seemed vaguely familiar. Hopefully not as convoluted.

[Joelen]

I really need to take a break. Just trying to understand how to get a copy over to you has blown my little mind. I have got to pick up the pieces after taking the break. Thanks for all your interests!

[canyonraven]

Hello Joelen:

I agree with Micron, this is definitely a project for a single database. The issue you really need to solve is how to develop relationships between your tables. You might try googling "MS Access how to normalize tables" and look at some examples.

[Micron]

@joelen: I'm not clear on the relationship(s) between niche and owner or space and owner, and if others are too, then that has to be cleared up before the design can be finalized. Early posts (and I think posts 27,32 & 33) cover this issue but I can't find a clear answer. Perhaps if you created some fake data in Excel then copy and paste here as a table, or try to explain some other way. What seems to be unknown:

- if I own a niche that has one space, do I own 1 niche or 1 space? (the distinction is important)
- if I own a niche that has 2 spaces, do I own 1 niche and pay a higher price for it because it has 2 spaces?
- or do I simply own spaces - either 1 space or 2 spaces if the niche is divided into 2?
- if I only own a space, can you subdivide that niche and sell the other space to anyone you please - even someone who is not related?

I'm guessing that all niches have 1 space by default. I might pay $300 for the right to place one urn and if I want to add one later, I pay an additional fee and you convert it from 1 space to 2, which makes the data relationship dynamic. In that case, definitely need a space table (which I included in my initial design suggestion). Those entries would look something like

SpaceIdPK	NichIdFK	SpaceName
1	15	A
2	15	B
3	16	A
4	17	A
5	18	A
6	17	B

In the above, at some point in time niche 17 was converted to a 2 space niche. The use of the underlying form would have to prevent giving an added space the same name (can't have 2 A's) nor allow more than 2 spaces.

Until the situation is clear, I for one don't know how to address the possibility that a niche might contain 2 urns, one (or perhaps both) who are not registered as the owner. I imagine it is possible for a space (niche?) to have more than one owner.

All the questions you're getting might be a bit frustrating, but I'm confident that in the end you will decide that it was all worth it if you get one properly designed db out of it and can ditch the other 9. Looking after 10 db's for this is really a non-starter for anyone who has posted here so far.

EDIT - after about 50 posts, forgive me if this was addressed somewhere, but this columbarium is for people only and not pets? I know you said it was going to be run by a church, which indicates deceased people only, but I've learned to not take too much for granted here. I once worked out a solution for "shoes" only to be told it wouldn't work because - wouldn't you know it - it wasn't about shoes at all. A complete waste of my time there.

[moke123]

We can muddy the water even more...

Can cremated remains be combined?
Mixing two people's ashes his is also known as “commingling”. Commingling a couple's remains means that the two individual's ashes obtained after cremation (also commonly called cremains) are mixed, or “mingled” together in an urn, most often a companion urn.

[orange]

I agree with micron. He has some interesting but critical questions that need thought and reasoned answers before finalizing any design. I could not find a generic data model for a columbarium. But what I did find is that custom design and construction of these is a major business(es) everywhere. It also seems that expansion - new walls, new sites is quite common. It also seems that niche may be the "owned entity", but whether 1 or 2 or X (based on moke123's comment) occupants is unclear. My take was that changes from 1 to 2 urns could be a decision left to the future.

Some links:
growth/need
some definitions
prearrangement

[Micron]

The longer this goes on, the more interesting it becomes. Moke123, you just raised it at least 4 notches!
I also couldn't find any schema or anything resembling a template but I found 1 or 2 closed tenders for building one. In the meantime I've thought of two ways to handle this:

Until the situation is clear, I for one don't know how to address the possibility that a niche might contain 2 urns, one (or perhaps both) who are not registered as the owner.

1) a junction table of sorts that has OwnerFK and NonOwnerFK fields, NonOwnerFk being joined to NonOwnerPK where the details of the deceased are listed much like the owner table. That this table contains a lot of Nulls is inevitable. If one thinks of it like a PO and PO line item or Invoice thing it comes close but doesn't quite work that way. For every line item there is but one PO value (same for Invoices, even if multiple) so one field that never contains Null works for PO stuff. However, one who is inurned either is the owner (PO) or is not. Add to that the fact that an owner may never be inurned in the space/niche they own for a variety of reasons. I think this is a case where Null in one field in virtually every record is the price you'd have to pay to keep such an approach workable:

tblInured
InuredIDpk	OwnerFK	NonOnerFK	SpaceIDfk	InuredDate	etc.
1	11		44	2/15/20
2		5	5
3	28		12
4	2		50
5		25	22
6	9		8

2) stop thinking of this as owner/non owner and think of an appropriate name for one table which has all personal data and an integer flag to ID owner or not. I'm leaning towards tblConstituent:

tblConstituent
ConsIDpk	Fname	Lname	etc.	Owner
1	John	Smith		-1
2	Mary	Brown		0
3	Moe	Green		0
4	Joe	Black		-1

Now ConstIDFk in tblInurned (which was not included in the posted spreadsheet due to not knowing how to approach this problem) provides all the details about the inurned and identifies them as an owner or not. Space field in tblInurned shows which space they are in and the links would be

Space > Niche > Wall.

I think this is the way I'd build this, notwithstanding any other questions that might arise about the business. The commingling idea is another good one and I think that anyone who goes to the lengths required to make a db work would be wise to consider it unless the outcome is to be tailored only to the OP's needs.

EDIT - believe it or not, I would give some thought to making ConstIDFk a multi value field (like 55,56) to address commingling! Not saying that's the answer, just saying it's just a thought at this point.

[orange]

I think the OP has a lot more analysis to do. We can only guess the underlying need and business requirement. Rather than NULLs, there may be options for use of things like UnOccupied, Not applicable, isSingle, isDouble...
As you have said, 50+ messages in and we're still trying to ferret out basic requirements.
Steve (ssanfu) said he was working on something, and wanted info from the OP. So he may have some ideas.

Here are 2 older threads with "quasi-similar" theme and dialog.
https://www.accessforums.net/showthread.php?t=60035
https://www.utteraccess.com/topics/1996476

[ssanfu]

@orange,
I was just playing around with the design that Micron proposed/suggested/devised in the spreadsheet as a starting point. The old dry marker on the window thing.

I have asked a few times about how many churches/cemeteries might be in the dB, but so far no response.

I also asked for a copy of the existing dB to try and get more insight, but no response to date.
Hopefully the dB would have a few records (Bugs Bunny, Daffy Duck, Yosemity Sam, Elmer Fudd) to see how the relationships might work.

From Post #7

<snip> With 10 Walls , each having 7 Queries, 8 reports and 2 forms, (each unique to it's own db) wouldn't this be a huge consolidation?

These would/could affect the design/relationships. At least they would provide more insight to the project.

So I'm going to back burner this until more info is available....

[Micron]

I have asked a few times about how many churches/cemeteries might be in the dB, but so far no response.

I think that was answered, and maybe other questions too?

The Columbarium (a place where one can place an Urn of ashes of a deceased person) will be associated with only one church.

What I posted was but a kernel of an idea at the time. Since then, I expanded that to 13 tables which seems to cover most of the musings that arose throughout. At this point, I think the OP has either been detained (it's Christmas after all) or has decided to move on, or who knows, perhaps has posted this elsewhere.

In the end, I found the subject matter interesting and a good exercise for the old noodle. I imagine there's a possibility to fill a void out there, given that I found nothing in the way of db schemas or templates, but I guess that remains to be seen.

[ssanfu]

@Micron,

I think that was answered, and maybe other questions too?

Not that I saw. (but maybe I missed it).

The Columbarium (a place where one can place an Urn of ashes of a deceased person) will be associated with only one church.

Yes there is 1 Columbarium per Church/cemetery, but could the dB be used for multiple Churches/cemeteries?
A diocese can have many parishes; each parish having its own Church/Columbarium.
So if a diocese consisted of 5 parishes, would the dB used for all 5 Columbariums or would each Columbarium have a copy of the dB?



13 tables? I am woefully behind. (I'm up to 6)

[June7]

Please, the word is inurned, not inured. Two completely different meanings.

[Micron]

June7, thanks for pointing that out.
I don't see the 'many churches' thing being worthy of consideration. If management of a columbarium is at the diocese level, they could get by without using tblChurch or whatever it would be called. If you're building something, just add such a table and if the management is not done by the diocese/parent then they don't need to use it?

EDIT - actually, I suppose they would use a table that lists the management entities even in a one church scenario. There would only be 1 management entity name in it (e.g. church name, but could be funeral services company or whatever) and its PK value would be a FK value in every record in tblColumbariums. If the db was used at a diocese level, there would be several church records in a managing entity table.

[orange]

Seems the OP has been busy elsewhere. Let's see what evolves if/when the OP returns.