In this tutorial we will build an online bookmark system. This will be used to create a database for storing all our URL's and their descriptions. There are many commercial and non-commercial applications that offer almost the exact functionality that we will create in our application; the difference is that we have full control over our application, allowing us to tweak it to suit our needs.
So what do we want this application to do?
- It must enable users to login and out of the system. This is very important if you are using this system online or on a shared computer.
- Users must be able to manage their own bookmarks. This includes storage, changes, retrieval and deletions.
- The system should offer user recommendations of other sites.
- It must display news headlines.
- Users must be able to personalize their corner of the site.
When we translate these requirements programmatically, we have the following:
- The program will authenticate every user. The method of authentication will include a username and password, which will be checked against the stored passwords. The passwords will be encrypted when stored for extra security.
- A verification code line has been included on the login form for those who want to use the application online. This will prevent automatic login by a robot. We will also take a detailed look at different ways in which this method can be implemented.
- To recommend other sites for users to visit, we will use the bookmarks that are stored in the database by other users. If, for example, more than one user stores the same bookmark, then we will use it as a recommendation for another user.
- As far as personalizing the application is concerned, the user will be able to upload an image, which will be displayed when they login. In addition, they will also be able to implement their own color scheme.
|bid||Primary key assigns unique number for every bookmark|
|uid||Links the bookmark to a user in the users table|
|desc||Description of the URL|
|uid||Assigns unique number to user|
|uname||Stores the user name|
|upass||Stores the password|
|Stores the email address of the user|
|level||Saves the access level of the user|
|bgc||Stores the color scheme of the user|
|img||Stores the image of the user|
We will store the user id as a foreign key to represent the user in the bookmark table. Then, each time a particular user stores a bookmark, their
userID is going to be stored in the bookmarks table as well. This will make it easy to retrieve their bookmarks when we run queries. I've also included some sample data which we will be using throughout this series. To set up the main database, you will need to create a database called 'personalization' and then run the SQL code below. Simply copy and paste it into your MySQL client:
At a later stage we will need to make some changes to the current tables, but for now the database is complete.