Typical Database model domain for User

18 June 2014

SQL scripts

A typical User domain model must have a clean separation between each table and what they're supposed to store according to each table's entity responsibility.

It is not really good to have the main user table with a lot of fields, keep on it mainly the fields which are most accessed.

this sql script was tested under PostgreSQL DB and also OracleXE DB with minor syntax changes. In this scenario the User primary key is also the email, and has to be unique across all the app. User password is stored encrypted in the database (MD5 or some other encryption algorithm).

User

 CREATE TABLE  TB_USER
 (
      ID_USER varchar(64) NOT NULL CONSTRAINT USER_PK PRIMARY KEY,
      PASSWORD varchar(128) NOT NULL,
      ACTIVE CHAR(1)
 );



CREATE TABLE  TB_USER_GROUP
 (
     ID_GROUP VARCHAR(32) PRIMARY KEY NOT NULL,
     DESCRIPTION varchar(128)
 );



CREATE TABLE  TB_USER_GROUP_USER (
       ID_USER varchar(64) NOT NULL,
       ID_GROUP  varchar(64) NOT NULL,
       CONSTRAINT USER_GROUP_USER_PK PRIMARY KEY(ID_USER, ID_GROUP),
       CONSTRAINT USER_GROUP_USER_USER_FK FOREIGN KEY (ID_USER) REFERENCES TB_USER(ID_USER),
       CONSTRAINT USER_GROUP_USER_GROUP_FK FOREIGN KEY(ID_GROUP) REFERENCES TB_USER_GROUP(ID_GROUP) ON DELETE CASCADE
);



 CREATE TABLE  TB_USER_PROFILE (
     ID_USER_PROFILE INTEGER NOT NULL,
     ID_USER varchar(64) NOT NULL ,
     USER_COOKIE varchar(128),
     LOCALE_CODE CHAR(6),
     FIRST_ACCESS DATE,
     LAST_ACCESS DATE,
     EMAIL_CONFIRMATION_FLAG CHAR(1),
     EMAIL_CONFIRMATION_STRING varchar(64),
     SEND_MESSAGE_FLAG CHAR(1),
     RECEIVE_MESSAGE_FLAG CHAR(1),
    CONSTRAINT USER_PROFILE_PK PRIMARY KEY(ID_USER_PROFILE),
    CONSTRAINT USER_PROFILE_USER_FK FOREIGN KEY (ID_USER) REFERENCES TB_USER(ID_USER)
);



CREATE TABLE  TB_USER_PREFERENCE (
     ID_USER_PREFERENCE INTEGER NOT NULL,
     ID_USER varchar(64) NOT NULL,
     CURRENCY varchar(3) NOT NULL,
     PREFERRED_LOCALE CHAR(6),
     RECEIVE_NEWSLETTER_FLAG CHAR(1),
     REMEMBER_ME CHAR(1),
    CONSTRAINT USER_PREFERENCE_PK PRIMARY KEY(ID_USER_PREFERENCE),
    CONSTRAINT USER_PREF_USER_FK FOREIGN KEY (ID_USER) REFERENCES TB_USER(ID_USER)
);



 CREATE TABLE  TB_LOG
 (
    ID_LOG INTEGER NOT NULL,
    EMAIL varchar(64) NOT NULL,
    VALUE varchar(64) NOT NULL,
    NAME varchar(48) NOT NULL,
    ACTION_TYPE varchar(64) NOT NULL,
    CONSTRAINT TB_LOG_PK PRIMARY KEY (ID_LOG)
 );

Description of Tables:

  • TB_USER - main table for User.

  • TB_USER_GROUP - Defines User Group for authorization and access security purposes.

  • TB_USER_GROUP_USER - Many to Many relationship between User and UserGroup.

  • TB_USER_PROFILE - UserProfile saves metrics about the User usage like Last access, if user is able to send or receive messages, etc.

  • TB_USER_PREFERENCE - Stores preferences for the user across the site like: Currency, preferred language, remember me feature, etc.

  • TB_LOG - Log table for saving log records from a specific user action like changing sensitive data, also it's not coupled to any other table.

comments powered by Disqus