I have a solution with Staff and Volunteers, which previously I stored as one in the Member Table. As I started to think about this solution down the road, the recognition that Staff and Volunteers are completely different modules -- ie. have an entirely different workflow, layout, and are accessed by totally different users, the only place they interact are the various products/credentials that exist -- and I will be adding more member types / modules in the near future, I decided to split this members table up:
so that all contact info resides in the person, while all info pertaining to their membership is stored in their respective table. Essentially these are 1 to 1 relationships. 95% of people will either be a staff or volunteer, not both. the 5% instance could happen where a volunteer becomes a staff . I would set volunteer record to inactive and staff to active. I've gone back and forth on the reasons for doing this but, i suppose i settled on:
1. keeping my tables narrow and performance high -- I could easily surpass 150 fields once i start adding in the other modules if all is in one table.
2. data separation -- keeping most of my edits and future additions in a modular format, I wonder if this would be helpful to isolate the various edits and changes so they are not happening in the same shared table, i can imagine this would be great for developer organization otherwise every field would be suffixed with vol_ or stf_,
3. inherent security -- only volunteers are shown when layout based on volunteer, vs person where i would be filtering every time.... though i just realized i could filter through ERD using a self-relationship, right?
My biggest annoyance with splitting the tables seems to be maintaining an indexed copy of the full name and email in each table [VOLUNTEER, STAFF], as these fields will be used 95% of the time searching for a volunteer or staff, and FM cant index from related tables. Fine with a lookup, but its stuff like this that makes me question my design.
So I'm wondering a few things:
1. Is this idea well thought out, would it be better to keep everything in one big table vs several 1 to 1's?
2. In theory and food for thought, which Table should i place the foreign keys? or rather, are there instances where you would places foreign keys in the parent table? -- does VOLUNTEER and STAFF get id_person or does PERSON get id_volunteer and id_staff. all records are created through the VOLUNTEER / STAFF layout not the PERSON. realistically a PERSON layout doesnt exist, at least for normal use. I would normally put foreign keys in the child table, but with this design i get a little confused, with the layout and most all operations are being run from VOLUNTEER or STAFF.