MySQL data modeling: Multiple user types / Activity / Following

Question

I have a project in which there is a site with multiple user types. And I am having trouble wrapping my head around the best practice for this.

I want to be able to get activity from (nodes) you follow regardless of node type. Pretend the node types are:

User: Organization:

An organization will be an entity that can act as a user. Write comments, send messages. But an organization is made up of users who can edit the orgs information. Organizations can also follow, and be followed.

• A) Should Users and Organizations be separate tables?
• B) Generally speaking how should this be stored.
• C) If they are in fact 2 tables, how does a join work when getting activity from those you follow?

Answer 1

Sounds like in your case organization is also an user. This is very similar to our database which looks like this,

1. You have a table with every users and organizations. We call them principals. The organization and user are treated the same in this table. This is where you store your data like activities. You can add a column for the type (org or user).

2. You would have another table for the relations. It just needs two columns, one column is organization and the other is users belong to this organization. Say an organization has 100 users, you would have 100 entries in this table for each user.