I'm looking at creating a web application that seems to be pretty complex to conceptualize for me. I'm having a hard time determining the best route to go in regards to how the db will be designed. So, I figured I would give my requirements here and some ideas I've tossed around and maybe somebody with more experience will be able to see if I'm headed in the right direction.

There requirements are as follows:
1.) The application will allow organizations to track their members, events, etc that are specific to their organization and NOT public. A user in the system can belong to 0 or more organizations with one organization being their default upon login. Most users will probably only have one organization but should have the potential to belong to others upon invite.

2.) Organizations cannot share data with other organizations.

3.) To login, a user only need provide their username and password. Providing access to their organization information will be handled by the application. I don't want the user to have to specify another credential or something upon login to differentiate. Switching the default organization will be a preferences option.

4.) A superuser should be able to manage all users and organizations.

The best design that I've been able to come up with is to have everything related to an organization ID and filter all queries around this. Is that the only or best solution there is?

Will there be scalability or maintenance headaches with this design or is there a better way to go? I've considered creating an application that will only handle one organization and then install as many instances of that as needed but that would be a huge pain for maintenance and upgrading.

Any ideas?

Few more questions:

• Can any body register to use your site or do they need an invite?
• Who gives an invite to join an organisation - is it the super user or is there a special type of admin within each organisation?
• How does a new user have a default organisation - if they are new then they can't be invited to join an org?
• The process of registering new users is quite standard and it's easy to get hold of code (I'd suggest PHP and MySQL) to do all this.
• How many users and organisations are you intending this for?
• What data can an organisation not share?

Here's some ideas for tables to start you off:

• User: id, email, user_name, pwd, super_user_YN, default_org_id, when_joined
• Organisation: id, name, ...
• UserOrg: user_id, org_id, relation_type
• RelationType: relation_type (ie invited, belongs to,administrator)
• Log: user_id, time, msg
• OrgData: ???

That's a horrible way of doing it! you should definitely put everything in one database.