Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 3 of 3
  1. #1
    Senior Member
    Join Date
    Apr 2003
    Location
    Hamden Ct
    Posts
    103
    Member #
    1121
    Such as the ones that message boards use? How do they make it so the user can edit just their profile and no one elses? I know this isn't a very clear question, but if anyone can try to answer it would be greatly appreciated.

  2.  

  3. #2
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    It's a complex database architecture with software-controlled permissions. A typical permission database will consist of a schema similar to this:
    Code:
    CREATE TABLE roles (
        id int NOT NULL AUTO_INCREMENT,
        name varchar(255) NOT NULL,
        PRIMARY KEY (id)
    );
    
    INSERT INTO roles (name) VALUES ('Administrator');
    INSERT INTO roles (name) VALUES ('Moderator');
    INSERT INTO roles (name) VALUES ('Member');
    The above table stores the roles (i.e. user groups) that can be assigned to users in the database.
    Code:
    CREATE TABLE permissions (
        id int NOT NULL AUTO_INCREMENT,
        name varchar(255) NOT NULL,
        PRIMARY KEY (id)
    );
    
    INSERT INTO permissions (name) VALUES ('Administer Users');
    INSERT INTO permissions (name) VALUES ('Moderate Posts');
    INSERT INTO permissions (name) VALUES ('Create/Edit Profile');
    The above table contains the permissions that exist in the system.
    Code:
    CREATE TABLE role_permissions (
        role_id int REFERENCES roles (id),
        permission_id int REFERENCES permissions (id),
        PRIMARY KEY (role_id, permission_id)
    );
    
    INSERT INTO role_permissions (role_d, permission_id) VALUES (1, 1);
    INSERT INTO role_permissions (role_d, permission_id) VALUES (1, 2);
    INSERT INTO role_permissions (role_d, permission_id) VALUES (1, 3);
    INSERT INTO role_permissions (role_d, permission_id) VALUES (2, 2);
    INSERT INTO role_permissions (role_d, permission_id) VALUES (2, 3);
    INSERT INTO role_permissions (role_d, permission_id) VALUES (3, 3);
    The above combination key table maps permissions to roles.
    Code:
    CREATE TABLE users (
        id int NOT NULL AUTO_INCREMENT,
        username varchar(255) NOT NULL,
        password varchar(255) NOT NULL,
        first_name varchar(255) NULL,
        last_name varchar(255) NULL,
        city varchar(255) NULL,
        state varchar(255) NULL,
        dob datetime(255) NULL,
        ssn varchar(255) NULL,
        aim varchar(255) NULL,
        yim varchar(255) NULL,
        msn varchar(255) NULL,
        icq varchar(255) NULL,
        PRIMARY KEY (id)
    );
    
    INSERT INTO users (username, password) VALUES ('transio', 'you_wish_you_knew');
    And thus we have created our users.
    Code:
    CREATE TABLE properties (
        id int NOT NULL AUTO_INCREMENT,
        name varchar(255) NOT NULL,
        PRIMARY KEY (id)
    );
    
    INSERT INTO properties (name) VALUES ('Favorite Color');
    INSERT INTO properties (name) VALUES ('Lucky Number');
    The above table contains the properties that can be defined for users
    Code:
    CREATE TABLE user_properties (
        user_id int REFERENCES user (id),
        property_id int REFERENCES properties (id),
        value varchar(255) NOT NULL,
        PRIMARY KEY (user_id, property_id)
    );
    
    INSERT INTO user_properties (user_id, property_id, value) VALUES (1, 1, 'Orange');
    INSERT INTO user_properties (user_id, property_id, value) VALUES (1, 2, '13');
    And this table defines the values for properties for users... where applicable

    You can see that through the combination key table "role_permissions", we are assigning permissions to a particular role in the system.

    The "users" table contains the linear information about the user (such as SSN and DoB... immutable info that cannot have multiple values).

    The "user_properties" table contains additional information about users in normalized format. This allows for easy addition of properties to the system in the future without addition of tables or columns to the database. It's always good to plan ahead when designing a database.

    I hope this helps you out a bit.

    BTW... to anyone who tries to create this database... I haven't tested it, and made it in about 10 minutes... so please forgive any errors !!

  4. #3
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,657
    Member #
    1234
    Liked
    138 times
    D A M N... Transio, add another one of your "10 min posts" to the "potential tutorial" list!
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 04:40 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com