Self Refferential Table

[JAL3]

Please be gentle; I'm learning.

Imagine a table that lists denomination. Each denomination has a name and an alphanumeric code that is the key. There is some other information but I don't think it is relevant to my question.




Some times multiple denominations merge together to form new denominations. These denominations would be in the table.

Sometimes, there are a few people in one or more of the "parent" denominations who do not go along with the merger and maintain a smaller version of the parent denomination. These truncated denominations would be in the table.

Sometime a denomination fractures into one or more child denominations. These too should be part of the table.

Just to keep things extra special, each of these relationships could be Many to Many.

so it is that in trying to keep track of parent and child denomination, the refferences would all be back to the original table. The table i thus describe basically keeps referencing itself.


+++++++++++++++++++++++++

Does this set up any problems. Is there a better way to encode the relationships I described? I used the example of denominations but it would be applicable to publishers, religious orders, endorsing organizations and many other similar organizations.

Thank you for your consideration.


JAL

[CJ_London]

You can have a parent/child relationship between two records in the same table (1-many) but not many to many. For that you need a separate table

[JAL3]

You can have a parent/child relationship between two records in the same table (1-many) but not many to many. For that you need a separate table

Thank you!

So would this be a reasonable approach?

1. define tblDenominations as described in the original post
2. define tblParentDenom with just the ID/key and name
3. define tblChildDenom with the same parameters as tblParentDenom
4. define junction tables for the two type of relationships
5. write subroutines in the interface to make entries in the junction tables as needed.

Please be aware that what I wrote above seems to make sense to me but I have zero real world experience and the creation of the data structures indicated will not be a trivial matter for me. It seems doable and the figuring and doing would likely be good learning experiences but I don't want to head down the wrong path if there is a "correct" way to handle the situation.

[CJ_London]

Without some example data, difficult to say. Tables define an entity with the same characteristics, be it person, company, product. I don’t understand what your definition of a denomination is. Whether it is a collection of people and the people move from one denomination to another or something else. Or the implication of fractures and what you are trying to track/record

A picture paints a thousand words - the the database world, the data is the picture

[JAL3]

I thank you again. I have limited time before I'm due for my shift at the hospital but I will try to explain.

I am trying to track the "organizations". The data in their tables include such things as dates of founding, publishing houses associated with them, regional strengths and that sort of thing. The actual purpose of the database is the tracking of worship resources such as hymnals, prayer books, published rites etc. Often times, a denomination authorizes the continued use of something from one of it's predecessor organizations.

If a denomination splits up because of some sort of disagreement, that authorization might only cover resources up to a certain date.

In reality, most churches are free to use what they wish from most sources but tracking the "official" stuff gives a view to the attitudes and theological leanings of the group as a whole.

[mike60smart]

As Ajax has said we need some data to work with.

Are you able to upload an Excel sheet with some sample data?

[Micron]

maybe this is along the lines of what you need

http://allenbrowne.com/ser-06.html

[JAL3]

maybe this is along the lines of what you need

http://allenbrowne.com/ser-06.html

That was an interesting article and the use of the term "pedigree" fits very well with what I am trying to describe in many cases. The problem seems to be with the many to many aspect which would not be applicable to a biological pedigree.


An example that will be in the excel spreadsheet I will upload shortly may demonstrate.

In America, the Methodist Episcopal Church, MEC, was formed in 1784. In the early 1800s, a group split off over the issue of episcopal governance and were called the Protestant Methodist Church, PMC. A few years later, the MEC divided with northers remaining in the MEC but southerners becoming the Methodist Episcopal Church South. Slavery was the issue but the immediate issue was the following of state laws in the south. In the early 1900s, the MEC, MECS and PMC reunited to form the Methodist Church, METH. Sounds simple enough and all of the MEC and MECS were part of the merger but not all of the PMCs were and the PMC who did not form the METH are still around today. In the 60s, the METH merged with the Evangelical United Brethren, EUB, to form the United Methodist Church, UMC. The EUB have their own history of mergers and I left out plenty of "child" denominations along the line. It can get pretty convoluted.

[JAL3]

As Ajax has said we need some data to work with.

Are you able to upload an Excel sheet with some sample data?

Here is a spreadsheet PDF that has a complicated example with multiple parents and children. I tried uploading the .xlsx file and found that was not allowed. I do not have any zip software so created a PDF.

I thank all for their input and will check again tomorrow. I'm due at the hospital at 3am so I'm hitting the rack now.

[mike60smart]

Hi
You can compress the spreadsheet by Right-Clicking on the file and selecting - Send To - then selecting Compressed (zipped) Folder.

The spreadsheet would be easier to understand.

[JAL3]

Hi
You can compress the spreadsheet by Right-Clicking on the file and selecting - Send To - then selecting Compressed (zipped) Folder.

The spreadsheet would be easier to understand.

Thanks!! That helps!

[JAL3]

Here is the zipped spreadsheet

[mike60smart]

Hi

What do the following terms mean?

DenomNamecon
ParenentDenom
ChildDenom

[mike60smart]

Is this how you would want to display your data for a specific Church?

[JAL3]

Hi

What do the following terms mean?

DenomNamecon
ParenentDenom
ChildDenom

DenomNamecon is a typo but with the final "n" replaced by an "m" should refer to a commonly used name for the denomination.

ParentDenom refers to a denomination that gave rise to the one in the current record

ChildDenom refers to a denomination that arose from the one in the current record.