Simple messaging system -database configuration

Contents
We will create the database tables and user to access the database from the application
You will find at bottom the

1. scripts for creating database
2. materials to read further
3. homework for you

As an older database programmer, I have started with database configuration.

The selected database engine is Sql Server 2008 R2 Express – the free version that can be downloaded from here : http://www.microsoft.com/express
To create tables , just start Sql Server Management Studio, point to your database, right click “Tables” node and press “New Table”
I have created the following tables in the diagram:

Several comments:

1. The smsg_User table can ( and will !) be replaced by the owner of the site with his table of users . More, this will be configurable by the owner. That’s why I choose IDUser being varchar(150) – maybe the user id will be GUID ?
2. The messages are stored in the smsg_Message
3. The message archive table(smsg_Message_Archive) is simply to make the search faster on smsg_Message table. We will put here the messages older than (1 month? 2 months?)
4. The smsg_MessageThread contains the possibility for the user to reply more than one time to a message.
5. IDMessage is bigint . We can also put guid to have unlimited number of messages -but 9,223,372,036,854,775,807 messages ( * 2 -we will see it in action) will be enough for a small site.
6. You can download the script from here:
   http://msprogrammer.serviciipeweb.ro/wp-content/uploads/MVC-4_94F/smsgV1.zip

Also you should not rely on Windows Identity to access the database. Why ? Because , usually , on Web hosting you have only some user name and password for database – not Active Directory user.

We will create an user that can access the Database and have full rights. We will manage further the rights for everyone.

Open SQL Server Management Studio and go to Security.

Please right click on “Logins ” in “Microsoft SQL Server Management Studio”

Step 2: Please left click on “New Login… “

Step 3: Please introduce “smsg” in “Login – New”

Step 4: Please left click on “SQL Server authentication ” in “Login – New”

Step 5: Please left click on “Password ” in “Login – New”

Step 6: Please put “smsg” in “Login – New”

Step 7: Please “smsg” in “Login – New” for confirm password

Step 8: Please left click in “Login – New”

Step 9: Please left click on “Enforce password policy ” in “Login – New”

Step 10: Please left click on “Database ” in “Login – New”

Step 11: Please keyboard input in “Login – New” […]

Step 12: Add rights : Please left click on “SMsgS “

Step 13: Please left click on “User Mapping ” in “Login – New”

Step 15: Please mouse drag end on “Current view pane ” in “Login – New”

Step 16: Please left click on “Smsg” database

Step 17: smsg user will be in the dbo schema

Step 18: Please put “dbo” as schema

Step 19: Please select “db_datareader “

Step 20: Please select on “db_datawriter ” in “Login – New”

Step 21: Please select “db_owner ” in “Login – New”

Step 22: Please left click on “OK ” in “Login – New”

Now we will verify on database:

Step 23: Please left click on “Object Explorer Hierarchy ” in “Microsoft SQL Server Management Studio”

Step 24: Please left click on “Object Explorer Hierarchy ” in “Microsoft SQL Server Management Studio”

Step 25: Please left click on “smsg ” in “Microsoft SQL Server Management Studio”

Summary
We have created database tables . We also generate a script for you that can be found here:
http://msprogrammer.serviciipeweb.ro/wp-content/uploads/MVC-4_94F/smsgV1.zip
But the security login ( user : smsg , pwd: smsg) you do have to do yourself.
You can also download the database backup from http://messagemvc.codeplex.com/releases/view/74250

To read:
3 Normal Form from Database Normalization, http://en.wikipedia.org/wiki/Database_normalization

Homework:
What if we want to send messages to a further date( let’s say, after on day 1 on the next month) ? What database changes do you envision ?

Simple messaging system