www.webdeveloper.com
Results 1 to 5 of 5

Thread: Planning my Data Base, need advice

  1. #1
    Join Date
    Dec 2011
    Posts
    1

    Planning my Data Base, need advice

    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 :-)

  2. #2
    Join Date
    Nov 2002
    Posts
    2,632
    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.

  3. #3
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    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?
    Last edited by eval(BadCode); 01-08-2012 at 12:17 AM.

  4. #4
    Join Date
    Nov 2002
    Posts
    2,632
    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.

  5. #5
    Join Date
    Oct 2009
    Posts
    658
    You can start with this structure

    Code:
    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.

    Code:
    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
    Last edited by ssystems; 01-15-2012 at 11:02 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles