user registration and management chapter 5 the user database should record as much information as...

User Registration and ManagementCHAPTER 5

The user database should record as much information as possible that might help Person A assess Person B's credibility.

Due to need for face to face meetings, it will be helpful to record their place of residence and postal code.

Suppose that the system specifications start off with very simple requirements for the user database, one that can be handled by the following single table:

create table users (

user_id integer primary key,

first_names varchar(50),

last_name varchar(50) not null,

email varchar(100) not null unique,

-- we encrypt passwords using operating system crypt function

password varchar(30) not null,

registration_date timestamp(0)

Fat versus Skinny: the Ideas

MOHAMMAD BORUJERDI

1

INTERNET ENGINEERING


After a few more months ... create table users (

user_id integer primary key,first_names varchar(50),last_name varchar(50) not null,email varchar(100) not null unique,password varchar(30) not null,-- user's personal homepage elsewhere on the Interneturl varchar(200),registration_date timestamp(0)-- an optional photo; if Oracle Intermedia Image is installed-- use the image datatype instead of BLOBportrait blob,-- with a 4 GB maximum, we're all set for Life of Johnsonbiography clob,birthdate date,-- current politically correct column name would be "gender" but data models often outlive linguistic fashion so we

stick with more established usagesex char(1) check (sex in ('m','f')),country_code char(2) references country_codes(iso),postal_code varchar(80),home_phone varchar(100),work_phone varchar(100),mobile_phone varchar(100),pager varchar(100),fax varchar(100),aim_screen_name varchar(50),icq_number varchar(50)

);

MOHAMMAD BORUJERDI

2



The table just keeps getting fatter. As the table gets fatter, more and more columns are likely to be NULL for any given user. We can apply “relevant data group breakup idea” to user registration:

create table users (user_id integer primary key,first_names varchar(50),last_name varchar(50) not null,email varchar(100) not null unique,password varchar(30) not null,registration_date timestamp(0)

); create table users_extra_info (

user_info_id integer primary key,user_id not null references users,field_name varchar(100) not null,field_type varchar(100) not null,-- one of the three columns below will be non-NULLvarchar_ value varchar(4000),blob_ value blob,date_ value timestamp(0),check ( not (varchar_value is null and

blob_ value is null and date_value is null))

-- in a real system, you'd probably have additional columns-- to store when each row was inserted and by whom

);

MOHAMMAD BORUJERDI

3



?

USE NORMAL FORMS TO REALIZE SKINNY TABLES

MOHAMMAD BORUJERDI

4



To make it fast to get all extra fields for a particular user CREATE INDEXES

create index users_extra_info_by_user on users_extra_info(user_id);

To enforce integrity constraints such as uniqueness for many-to-one relations :

create unique index users_extra_info_user_id_field_idx on users_extra_info (user_id, field_name);

Fat versus Skinny: the Decision

is an engineering judgment call. You can certainly build a working system using either approach.

Because NULL columns take up so little space in the database, you shouldn't decide between skinny and fat based on presumed data storage efficiency.

Skinny is good when you are storing wildly disparate data on each user, such that you'd expect more than 75 percent of columns to be NULL in a fat data model. Skinny can result in strange-looking SQL

queries and data dictionary opacity.

If using commercial RDBMS and wish to make queries really fast, build bitmap indices.

MOHAMMAD BORUJERDI

5



A good general rule is that representing a many-to-one relation requires two tables: Things A and Things B, where many Bs can be associated with one A. Another general rule is that representing a many-to-many relation requires three tables: Things A, Things B, and a mapping table to associate arbitrary numbers of As with arbitrary numbers of Bs.

create table user_group_map (

user_id not null references users;

user_group_id not null references user_groups;

unique(user_id, user_group_id)

);

This is in First Normal Form, in which there are no multivalued columns.

Typical data model will include a USERS table , a USER_GROUPS table, and a

USER_GROUP_MAP table.

MOHAMMAD BORUJERDI

6


One of the most powerful constructs in an online community is a user group. you'll want to be able to refer to groups of users from other objects in your database.


Derivable Data : join and other operations

To answer the question "Is Norman Horowitz part of the Tanganyikan Ciclid interest group and therefore entitled to their private page" we must execute a query like the following:

select count(*) from user_group_mapwhere user_id = (select user_id

from users where first_names = 'Norman' and last_name = 'Horowitz')

and user_group_id = (select user_group_id from user_groups

where group_name = 'Tanganyikans')

To answer the question "To which groups does Norman Horowitz belong" we must JOIN the following tables: users, user_groups, user_group_map:

select user_groups.group_namefrom users, user_groups, user_group_mapwhere users.first_names = 'Norman' and users.last_name = 'Horowitz'and users.user_id = user_group_map.user_idand user_groups.user_group_id = user_group_map.user_group_id;

MOHAMMAD BORUJERDI

7



Use a view if you really need to simplify queries

Note, however, that views are purely syntactic. If a query is running slowly when fed directly to the

RDBMS, it won't run any faster simply by having been renamed into a view.

data model complexity can always be tamed with views which let you deal with an adequate subset of the big database.

MOHAMMAD BORUJERDI

8


So, Overall :Clean up ugly queries with views. Clean up ugly performance problems with indices.

If you're facing Yahoo! or Amazon levels of usage, look into unloading the RDBMS altogether with application-level caching.


Access Control and Approval

We can model user registration as a finite-state machine, and code our policies such as in figure 5.1.

Limit access to only those registrants who've verified receipt of an email message at the address that they supplied upon registering. Reject registration from users whose only email address is at an anonymous provider. A community may need to change its policies as the membership grows.

MOHAMMAD BORUJERDI

9


Not a user

Need email verification ; Need admin approval

Need email verification Need admin approval

Authorized

Banned Deleted

Rejected via any pre-authorization state


Multi-Page Design and Flow

A second general principle is Have users pick the object first and then the verb.

These principles are especially easy to apply to user administration pages, for example. The administrator looks at a list of users and clicks on one to select it. The server produces a new page with a list of possible actions to apply to that user.

The first general principle of multi-page design is : Don't break the browser's Back button.

Users should be able to go forward and back at any time in their session with a site. For example :

choose a book enter shipping address enter credit card number confirm thank-you

MOHAMMAD BORUJERDI

10


STUDY GET AND POST AND THEIR DIFFERENCES

user registration and management chapter 5 the user database should record as much information as...

Documents