Plan the Classified Ads Site

This first section of this tutorial helps you create all the files and database tables needed for the application.

Before you start building this application, make sure you have a correctly configured Dreamweaver site, and a working database connection. For more instructions regarding these actions, consult the Getting started help file, which can be found in Help -> InterAKT -> Getting Started.

In the tutorial, you will have to create several files in your site root folder. You can create them at the very beginning, so that you will not waste time with this operation again. To create files and folders in the site's root, use the corresponding options in the File menu of the Files tab.

The file structure will look as in the example below, and you can create it easily by unpacking the zip file corresponding to your server model from \tutorials\Classified Ads\ in your site root:

 

After having created the files for your pages, it is time to set up the database that will hold the information for the application. For this tutorial you will use 6 tables containing data about the ads and users. These tables are:

  1. ads_ads - to store the classified ads.

    · id_ads - the primary key for the ads table. No two ads can have the same ID.
    · idusr_ads - foreign key to the users table, so you'll know which message belongs to which user. Login and access restrictions are stored there.
    · idtyp_ads - a foreign key to the type table. Each classified ad must have an associated type (For-sale, Wanted etc.)
    · idcat_ads - a foreign key to the category table. Stores the category where the classified ad goes in (Cars, Books etc).
    · title_ads - stores the ad subject.
    · content_ads - stores the actual contents of the classified ad.
    · idloc_ads - foreign key to the table that stores the location of the seller or buyer (Denver, Omaha etc.).
    · date_ads - stores the date when the ad was posted.
  2. user_usr - stores information about the site users.

    · id_usr - the primary key for the users table. No two users can have the same ID.
    · username_usr - the name used by the user to authenticate to the website.
    · password_usr - the user's password.

    Note:     In the database created for this tutorial, the passwords are encrypted so to offer the application a higher level of security.

    · active_usr - a boolean flag (1/0) showing if the user account has been activated or not.
    · level_usr - a boolean flag (1/0) which stores the level of access the user is allowed. Level 1 corresponds to administrators and level 0 to common users.
    · randomkey_usr - stores a random alpha-numeric key generated for secure account activation.
    · email_usr - the user's email address.
    · name_usr - the user's real name.
    · regdate_usr - the date when the user registered to the classified ads site.
  3. type_typ - stores the ad types: For sale, Wanted, Exchange, or Don't care.

    · id_typ - the primary key for the type table. No two types can have the same ID.
    · name_typ - stores the actual type of the message.
  4. category_cat - stores the ad categories.

    · id_cat - the primary key for the category table. No two categories can have the same ID.
    · idcat_cat - a self-foreign key to the parent category, if any. For example, for the mobile accessories category, this field would contain the id of the Mobile category (the parent). For main categories, that is categories which do not fall under another parent category, the idcat_cat field value is 0. You can find more details on how self-foreign keys here.
    · name_cat - the category name (Automotive, Electronics etc.).
  5. location_loc - table that stores the location where the ad is available.

    · id_loc - the primary key for the location table. No two locations can have the same ID.
    · name_loc - the location name (Chicago, Paris etc.).
  6. subscription_sub - stores subscription data for the users. Users can subscribe to received new classified ads for their categories of interest.

    · id_sub - the primary key for the subscription table.
    · idusr_sub - a foreign key to the users table. Keeps track of which user has a subscription.
    · idtyp_sub - foreign key to the type table, indicating the type of ads the user wants to receive.
    · idcat_sub - foreign key to the categories table, indicating the category the user subscribed to.
    · idloc_sub - foreign key to the locations table, indicating the locations where the users is interested in receiving ads from.

Below is a layout of the tables using InterAKT's visual query editor, MX Query Builder:
 

 

Note: The database diagram in the image above was built with MX Query Builder (also referred as QuB) to better illustrate the database structure. You do not need to build it in order to complete this tutorial.

 

You can find the scripts needed to create an identical table structure inside the downloaded package, in the \tutorials\Classified Ads\db\ folder, as an sql or mdb file, depending on the database server you intend to use. Import them in your database server management software (e.g. PHPMyAdmin, Microsoft Access etc).

The scripts already contain data so that you can easily view the results. In order to access all parts of the application, you can use the default user accounts contained in the sql scripts. The accounts for the classified ads application are:

These user accounts will be used for authenticating to the web application, to allow you view and post ads. In the database, the passwords are encrypted, as mentioned above.

 

Before proceeding with the tutorial, open the main index page and create a new connection named connAds and configure it to connect to your newly created database:

 

If you use ColdFusion, you will need to define a valid data source using the ColdFusion Administrator interface. For more details, check out the Getting Started book, accessible from the Help menu > InterAKT -> Getting Started.

In the next chapter, you'll learn how to post and list classified ads.