Remove members table in favour of clearer DB provided relationships.

The member table exists to provide a very high level abstraction over proper database relationships - removing the need for join tables, but also complicating queries and requiring too much knowledge of its inner workings.

Currently this is used for:

Users within groups and within organisations (with a capacity).
Packages within organizations/groups.
Groups being members of groups, and/or organisations being members of organisations.
Although removing one table in order to introduce several more seems inefficient, I think the clarity in exactly how objects are related will be worth the extra resources, and will probably:

make it easier to draw our the structure of the database
make it easier, and possibly more efficient, to query.
would allow FK constraints
Suggestions:

Users

Join tables - group_users and organisation_users which both contain the group_id, user_id and a capacity field (for where it is necessary). This will also simplify the process (one day) of separating groups from organisations, or vice versa.

Packages

This should, for organisations, be fixed by https://github.com/ckan/ckan/issues/2789. For groups we should introduce a package_groups join table.

Groups

Change the group table to contain parent_group_id to handle hierarchical groups/organisations. Optionally a path field depending on how whoever implements wanted to implement the hierarchy.

This was originally https://github.com/ckan/ckan/issues/2790

Start by reviewing the proposed member-table replacement and the related context in issues 2789 and 2790. Map the existing relationships for users, packages, groups, and organisations before deciding whether join tables or parent_group_id meet the stated goals. Done means the relationships are explicit and support the listed foreign-key and queryability improvements.

Written by the indexing model from the issue text.