- 1. Define database entities (tables)
- 2. Identify attributes for tables
- 3. Define relationships and cardinality between the istances (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:
- - USER (to store data about users, email, password, nickname,...)
- - 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
-----------
id_user_pk (Primary Key)
nick
email
password
user_data_reg (user signup date)
SITE
-----------
id_site_pk (Primary Key)
site_url
site_description
site_data_reg (when a site is added)
totale_share_user (total number of users that share a site)
-----------
id_user_pk (Primary Key)
nick
password
user_data_reg (user signup date)
SITE
-----------
id_site_pk (Primary Key)
site_url
site_description
site_data_reg (when a site is added)
totale_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 beetwen 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).
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 combinations 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
-----------
id_share_pk (Primary Key)
id_user (Foreign Key > USER)
id_site (Foreign Key >SITE)
submitted_by (boolean: flag only if the current user has submitted the site)
-----------
id_share_pk (Primary Key)
id_user (Foreign Key > USER)
id_site (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 and phpMyAdmin.
No comments:
Post a Comment