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 user_id or person_id, but it feels like there's some redundancy there. Example:

id | username | email | password | first_name | last_name | (etc)

id | organization_id | first_name | last_name | (etc)

id | organization_id | user_id | person_id | placed_offline | amount | (etc)

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:

id | username | password | salt | registered_on | (etc)

id | organization_id | email | first_name | last_name

user_id | person_id

id | organization_id | person_id | placed_offline | amount | stripe_charge_id | (etc)

id | person_id | country | city | (etc)

id | organization_id | person_id | note | posted_by | (etc)

id | person_id | tag_id | (etc)

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.