Planning my Data Base, need advice

[Mandu]

Hi everyone;

I searched around and haven't seen quite the advice I'm looking for.

I am creating a site that will have multiple users from multiple organizations accessing and creating data base info. Any advice on how to structure this?

-It should not be possible (or at least easy) for someone to register as belonging to an organization that they do not belong to.
-many resources will be similar between organizations.
-I need to create queries on activity that all organizations are doing.

I am developing with WAMP. Any and all advice is appreciated, I'm a development noob :-)

[spufi]

Generally speaking, create a table for the users to register their information on. The DBA or someone with the privileges needs to be the one that sets users to whichever organization that the user belongs to. You then create as many organization tables as needed. Once a person logs in, they only get the table(s) that they are allowed to see.

[eval(BadCode)]

good answer about authentication spufi not to be mean, but I see a future of trouble for the poster who insists on security, but can't explain exactly the authentication and authorisation schemes. If you have the DBAdmin add them 1 by 1, then you will have less trouble with authentication.

Authorisation.... spufi says discretionary access controls; I say Chinese Wall... I get the impression you'll end up making a User<->Resource matrix

Who do you trust with what?

[spufi]

Depends on how big the company is and how many people are being added. If you are dealing with something government related, you are talking about maybe thousands of workers. If it's a new system where everybody has to be added, that can be a lot for one person to deal with. If you are talking about normal everyday adding in a couple of new employees, the DBA better be able to handle that work load. The way I would do it would handle the heavy work load as well even if it hardly ever needed.

[ssystems]

You can start with this structure

CREATE TABLE [ApplicationUsers](
	[ApplicationUserId] [UNIQUEIDENTIFIER] NOT NULL,
	[FirstName] [NVARCHAR] (128) NOT NULL,
	etc
	[AuthenticationId] [UNIQUEIDENTIFIER] NOT NULL
)

CREATE TABLE [Authentications](
	[AuthenticationId] [UNIQUEIDENTIFIER] NOT NULL,
	[UserName] [NVARCHAR] (128) NOT NULL,
	[Password] [NVARCHAR] (256) NOT NULL,
	etc
)

CREATE TABLE [Schema].[Organizations](
	[OrganizationId] [UNIQUEIDENTIFIER] NOT NULL,
	[OrganizationName] [NVARCHAR] (128) NOT NULL,
	etc
)


CREATE TABLE [Schema].[Resources](
	[ResourceId] [UNIQUEIDENTIFIER] NOT NULL,
	[ResourceName] [NVARCHAR] (128) NOT NULL,
	etc
)


CREATE TABLE [Schema].[UserResourcePermissions](
	[UserResourcePermissionId] [UNIQUEIDENTIFIER] NOT NULL,
	[AuthenticationId] [NVARCHAR] NOT NULL,
	[CanWrite] [BIT] NOT NULL,
	[CanRead] [BIT] NOT NULL,
	[CanView] [BIT] NOT NULL,
	etc
)

Depends on your implementation and how your objects handle resources sometimes I find it faster if bitwise operations are utilized. e.g.

CREATE TABLE [Schema].[UserResourcePermissions](
	[UserResourcePermissionId] [UNIQUEIDENTIFIER] NOT NULL,
	[AuthenticationId] [NVARCHAR] NOT NULL,
	[Permissions] [BIGINT] NOT NULL
)

Where 1 = canwrite, 2 = canread, 4 = canview, 8 = can delete. When they got all the permissions the value of the permissions would be 1111xb or 15.

Just adjust queries to match MySQL syntax