Relationship Issues

jumbaugh

New member
Local time
Today, 12:37
Joined
Aug 1, 2012
Messages
3
I've been working on this database for a while and I'm starting to "confuse" myself. What it is is a database to have a registry of pastors for a conference of church's. I will have about 200 pastors and I want to link them to a church. The issue I'm having is how to change the church a pastor is linked to. In other words a pastor can be moved from church to church and I don't want to lose the information we already have for that church. Currently I have limited fields int he church table until I get it working properly.
 

Attachments

You are faced with a many-to-many relationship because not only can a church have many pastors, because these pastors move from church to church, a pastor can have pastored many chruches.

I would create a PastorChurchLink table. You would have a primary key field (autonumber type), a number field as a foreign key to the Chruches table, another number field as a foreign key to the Pastors table. I would also add Started date and Ended date fields. This would allow you to find and display the church and the pastor where the Ended date field is null but it would also allow you to retain the history of each pastor's tenure at any church.
 
Thanks so much for the response. I'll try your suggestions these next couple of days. I guess one question that comes up before I even start playing with the database again is how I would do the forms. I should be able to put it all on my main form with a combo box to select the church, right? The main form is from the pastors table.
 
One thing to keep in mind is that you will need to not only select the church but supply a start date so it may not be as simple as putting a combo box on your main form. It is a little difficult to advise you as to exactly what you will need to do to manage the information because I don't know exactly how you have your form setup. I would most likely use a list box to display the list of churches where the pastor has pastored and then use an Add command button to open a popup form to allow the user to select a chruch and provide a starting date.

You would also be able to have the list box sorted in descending order so you would have the most recent pastorate on top. (There would not be an ending date in that record.) The user could then select that record and you could have an Edit command button that would then open the popup form with the ending date field displayed so this data could be provided.

You can certainly do it as you choose but these were just my initial thoughts.
 
All great ideas. I was just informed that we are going to use the database more for pastors and not to track the church information. So I have plenty of time to implement the relationship and "tweak" the church side of the tables.

Thanks so much for your input. If I have any more questions I'll come looking for you.

Thank you
 

Users who are viewing this thread

Back
Top Bottom