Delicious Database Design: relationships
Wall Script
Wall Script
Tuesday, November 18, 2008

Delicious Database Design: relationships

This lesson explains how to project a typical relationships-entities model for a database to be used in our web projects. My approach is:

1. Define database entities (tables)
2. Identify attributes for tables
3. Define relationships and cardinality between the instances (records) of tables.

Step 1: define database entities
The first step when you project a database is to identify all entities (tables). For example if we want to project a simplified del.icio.us-like web site, our database will have these entities:

1. - USER (to store data about users, email, password, nickname,...)
2. - SITE (to store data about the sites added by the users)


These are only the main entities required from our project but, take a mind, that we will add other tables to store data about relationships between istances (records) of these tables in case of cardinality (M:M), many to many (see Step 3).

Step 2: define attributes
The next step is to define attributes for the tables USER and SITE. In this semplified example we will have something like this:

USER
-----------
user_id_pk (Primary Key)
user_name
email
password
user_data (user signup date)


SITE
-----------
site_id_pk (Primary Key)
url
description
share_user (total number of users that share a site)


Step 3: define database relationships
Our simple application del.icio.us-like works in this way: an user add a site that can be shared by other users. The relationship's cardinality between USER table and SITE table is:


USER > SITE (M:M) - Many to Many (an user can add many sites).
SITE > USER (M:M) - Many to Many (a site can be shared by many users).


In this case ( cardinality M:M) we have to add a new table (SHARE) that contains all possible combination between all instances of USER table and SITE table . In this new table, SHARE, to identify an user that share a site added by another user or by itself, we will add two Foreign Key:


SHARE
-----------
share_id_pk (Primary Key)
user_id (Foreign Key > USER)
site_id (Foreign Key >SITE)
submitted_by (boolean: flag only if the current user has submitted the site)




Implement your database using SQL
Now, our database is ready to be implement with a DBMS (for example using MySQL). The next lesson will explains how to implement this database using SQL language.
Next Lessons:Create Tables and Relationships with SQL
web notification

4 comments:

  1. Hi,

    This is nice article but it is very very basic one and any novice user will be doing this thing only.

    I am not sure if your purpose of 'delicious' database design is solved here?

    Can you rather think of presenting a design of database/framework generic enough such that any one can design any kind of application with custom entities of his own choice quickly, along with UI related code (JSPs etc)?

    This way when I am writing any new application I shall not have to design/think of low level things like tables in database!

    ReplyDelete

mailxengine Youtueb channel
Make in India
X