We have a web application wherein nonprofit organizations can host their own auctions, donation campaigns, manage event ticketing, etc. We also have services for for-profit businesses, but let's ignore them for simplification. And for nonprofits, I'll focus on donation campaigns.
In order to place a donation, a donor must have a user account with us (username, password, etc). Credit card tokens, addresses, and donations are associated with a user ID.
We're going to expand our dashboard to provide CRM-like capabilities, including allowing organization admins to add arbitrary people. Maybe those people have made offline donations, maybe they haven't. These are not users within our system, although they may become users eventually (and we'll build a de-duplication tool so admins can handle those cases).
We're also going to want to be able to add some basic features to the "CRM", such as associating addresses with people (we already do this for users), adding arbitrary tags and internal notes, etc.
My question: how best to store "people entered into the CRM" vs. "actual users of our application"?
Immediately I think of creating a
people table and updating code across our existing products to interface with a
person_id, but it feels like there's some redundancy there. Example:
USERS id | username | email | password | first_name | last_name | (etc) PEOPLE id | organization_id | first_name | last_name | (etc) DONATIONS id | organization_id | user_id | person_id | placed_offline | amount | (etc) ADDRESSES id | user_id | person_id | country | city | (etc)
Another idea would be to change our
users table so that it only contains authentication-related information, and store all other data in
people. We'd have to change a bunch of queries around the site, but it's doable. Something like this:
USERS id | username | password | salt | registered_on | (etc) PEOPLE id | organization_id | email | first_name | last_name USER_PEOPLE user_id | person_id DONATIONS id | organization_id | person_id | placed_offline | amount | stripe_charge_id | (etc) ADDRESSES id | person_id | country | city | (etc) INTERNAL_PERSON_NOTES id | organization_id | person_id | note | posted_by | (etc) PERSON_TAGS id | person_id | tag_id | (etc) TAGS id | organization_id | name | (etc)
Note that people need to be organization-specific, since we wouldn't want one organization's custom-added people appearing for another organization. For
people records tied to a
user I'd set an
organization_id of 0.
I like the second idea, but I think it has some kinks that need working out, likely some that I'm unaware of. I'm wondering what an actual DBA would think of doing in this situation / if there's a best practice.