How do you create user editable profiles?

[Ryan]

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.

[smoseley]

It's a complex database architecture with software-controlled permissions. A typical permission database will consist of a schema similar to this:

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.

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.

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.

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.

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

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 !!

[Wired]

D A M N... Transio, add another one of your "10 min posts" to the "potential tutorial" list!