DBMS day-3

Dbms storage engines and transactions - audio blog

Today i would tell you about how our database schema got shaped at the hands of an experienced person and other basic operations in dbms ! Read previous post of mine before reading this post for better understanding.

Relations between tables are important:

• It's very important to understand how tables are related. In previous post i have explained about the database schema me and my partner created. We didn’t look into relations deeply. That created some problems.
• The messages table is related with user table. A user can create many messages and a message can be sent or viewed by many users.
• So the relation is many to many here.
• When we have many to many relationship we need to have some intermediate table for proper database design.
• The concept of sending messages to groups has been removed to make things simple.

New database schema designed by guidance:

Table name: messages
Attributes: msg_id, sender_id, msg_text, datetime, send_type, deleted_status

Table name: user_messages
Attributes: msg_id, receiver_id, delete_status

the table user_messages acts as an intermediate table between messages and user.

send_type - public/private. Public messages can be viewed by everyone.  
Now we have two simple tables instead of three. This seems a good database schema design.

Note: if many to many create intermediate table

The other modules of the application we are designing were also designed based on the relationship among tables.

Some basic operations and concepts in database:

create table messages (
msg_id int auto_increment primary key,
msg_text text,
created_time datetime not null,
send_type enum('public','private') default 'private',
delete_status bool default false );

alter table messages add sender_id int references user(user_id) after msg_id;

create table user_messages (
msg_id references messages(msg_id),
user_id references user(user_id),
delete_status bool default false,
primary key(msg_id,user_id)
);
  
From the above sql statements,
you can get an idea about create table,alter table, primary key,foreign key, auto_increment, text, not null, default etc..
Explaining about each syntax is not necessary. There are many good sites for reading about those concepts.


Thanks for reading this post of mine. See you in next blog entry !


Trained @ Sourcebits