Member Avatar for regan.a

Hi Everyone,

I volunteered to design a database for a church and would like some feedback on the design. This will be a MySQL database used by PHP to fetch information. The database is for tracking projects at different sites and assigning voluteers to those projects (very simple description). If you need any more information please let me know, I am happy to provide. I just don't want to write a book in my first post ;)

Here is an image of the database. I can provide SQL if that is better. Thanks!

http://img114.imageshack.us/img114/5771/sddatabaseig2.png

  • 2 Contributors
  • 1 Reply
  • 71 Views
  • 15 Hours Discussion Span
  • Latest Post Latest Post by Memento
Member Avatar for Memento

That schema shows more planning and thought then many corporate systems I have inherited.

  • Merge sd_leaders, sd_volunteers, sd_contacts, and sd_pastors together.
    • If needed, you can add one or more columns to this table to designate some special status, however,
    • You can determine their role(s) by whether sd_church_has_sd_pastors, sd_contacts_has_sd_worksite, sd_volunteers_has_sd_project, and/or sd_leaders_has_sd_project link to them
  • Possibly add another table between sd_church and sd_volunteers so that the member can volunteer on projects not directly managed by his church (this is similar to what you did with the pastors). I think this would come in handy if a volunteer is referred by one church to another church's project.
  • Adding start and end dates to the tables that maintain your people relationships (sd_church_has_sd_pastors, sd_contacts_has_sd_worksite, sd_volunteers_has_sd_project, and sd_leaders_has_sd_project) because people and pastors switch churches, leaders step down or get replaced, and contacts change. To maintain historical integrity, you cannot just change the relationship, you must end date it and then add a new one.
  • Allow room for the full address on sd_worksite, sd_leaders, sd_volunteers, sd_contacts, and sd_pastors. I have seen systems where they store address on another table so that you can keep a history. This can also be used to store multiple addresses for someone.
  • On sd_worksite, perhaps include room for notes (like driving directions)

Good luck!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.