CiviCRM Community Forums (archive)

I couldn't figure where to put a data design question. Hope this is the right place!

I have created a contacts database for my department at the University of British Columbia. Originally I imported the Employer for UBC employees as "UBC". I'm seeing now that that won't work since it's way too broad. Yes, these people would say their employer was UBC but what I care about is that the specific area they work in.

Specifically I want to add relationships like Jane Gee is the Undergraduate Advisor of Geophysics.

Their employer info can be broken down to four fields.

Employer: UBC
Faculty: eg Arts (null for 50% of UBC employee records)
Section/Department: eg Sociology (null for 66% of UBC employee records)
Unit: eg Institute of Social Justics (null for 95% of UBC employee records)

I was planning to create an Individual custom field set "Organisation details" with Faculty, Section/Department and Unit as custom fields. People not employed by UBC (about half of all records) can still have data for those fields.


My current thinking about how to accurately record their specific employer is to update the Employer record for UBC employees to be a concatenation of all or some of Employer & Faculty & Section/Department & Unit. For example some people will just have Employer & Section/Department. Others will have Employer & Faculty.

I don't like this solution. One because it duplicates information. Two because the decision as to which fields to concatenate is somewhat arbitrary. Three because I don't have complete information for all those fields for all UBC employees.

Has anyone dealt with this issue before or has some insight on how I should design my data?

I feel like I'm missing something. There must be a better way!

Thanks for any suggestions.
 

Thanks for the prompt reply. I understood about 1/2 of it. Had to look up the rest.

To confirm, you are suggesting I create new contact subtypes?
e.g
Individual > Staff
Organisation > Faculty
Organisation > Department
Organisation > Unit

Then create a custom field set called "Employer Details" and have a contact reference field for Faculty, Department and Unit.

Is that right?

I don't know how to write custom extensions so I'll just leave that part of the suggestion for now. (Though I do understand what you're suggesting it does.)

I don't need to track movement, I just need to know where they are right now. But I could potentially use the detailed employer info on event badges and other things I haven't thought of yet. 

Is creating multiple employer relationships an alternative to creating new subtypes as above?

Regarding this point: "I'd put the most specific "employer" if not empty (eg. unit if set, otherwise section, otherwise faculty)"
I need to see it all in context, but I think the relationships that I really care about is at the highest level (UBC) and the most specific level (unit ->section->faculty as you suggest).

I'm not really feeling clear on a way forward but I'm going to play for a bit with your subtype suggestion and see how that goes.

Thanks

Thanks for your assistance.