Multiple Tables

• Better use more than one 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,
        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-NULL
        varchar_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

);

-- make it fast to get all extra fields for a particular user
create index users_extra_info_by_user on users_extra_info(user_id);

• User user_id to link the two.