How the Project was Built

Component Types

All the components in the database fall into one of several broad categories denoted as the component type such as CPU, motherboard, video adapter, etc.. The lookup term field is utilized to construct a Google "define" query. For example, "video adapter" becomes a URL link video adapter wherever this category is denoted in specs for a component.

Components

The heart of the database is the database of components. In this particular implementation, I strove not for a comprehensive database, but for a illustrative selective set of components that lead to questions and answers and likely trouble-spots for new comers in building their first computer.

Component Resources

As I searched for components to include in the database, I frequently ran across useful links in each component category. This table captures these links and associates them by component types.

Component Images

Rather than downloading and storing all the images on my website, I chose to reference images hosted on other websites, providing links to the websites in the process so as to provide proper credit to the image's origins.

Component Technologies

This table lists all the salient technologies such as PCI, SCSI, AGP, IDE, etc..

Component To Technologies

This contains entries for each component and all technologies a particular component supports. To determine if one component is compatable with another, a query is performed to look up all the technologies of each component that is being paired and if a common technology exists for both components, then the component are assumed to be compatable. If a match is indeed found, then a secound query is run against the component incompatabilities table to determine that there are no explicit after-market incompatabilities.

Questions

The Questions table defines each question and featured component in the component matchup exercise. The question itself will largely be text that describes the featured component in some detail much as you would find on the online shopping sites. Depending on the choose_correct_yn flag, the learner is then asked to choose either the compatable or incompatable component from the list presented.

Answers

The Answers table houses the answers to the various questions along with reason text that is displayed to the learner when he/she chooses a particular answer. If the choice is incorrect, the compatability issues are discussed and learner will be sent back to the featured component's question to try again. If the choice is correct, the criteria that made the component the correct choice are pointed out in the reason text that's presented.

Component Conflicts

The component conflicts table provides a way to detect incompatability between two components during the build computer simulation exercise. Two components are listed along with reason for the conflict.

Database Schema

create table component_types (
    component_type_id  integer not null primary key,
    description varchar(50),
    lookup_term varchar(50)
);

create table component_resources (
    resource_id        integer not null primary key,
    component_type_id  integer not null references component_types(component_type_id),
    resource_title     varchar(100),
    url                varchar(100)
);

create table components (
    component_id       integer not null primary key,
    component_type_id  integer not null references component_types(component_type_id),
    component_name     varchar(100),
    manufacturer       varchar(100),
    description        varchar(500),
    specs              varchar(2048)
);

create table component_images (
    image_id           integer not null primary key,
    component_id       integer not null references components(component_id),
    image_caption      varchar(100),
    is_primary         boolean default 'f',
    url                varchar(255),
    page_url           varchar(300)
);

create table component_techs (
    tech_id       integer not null primary key,
    tech_name     varchar(50),
    description   varchar(2048)
);

create table comp_questions (
  question_id    	integer not null primary key,
  question       	varchar(2048),
  component_id   	integer not null references components(component_id),
  choose_correct_yn	boolean
  );

create table comp_answers (
  answer_id		integer not null primary key,
  question_id		integer not null references comp_questions(question_id),
  component_id		integer not null references components(component_id),
  correct_yn		boolean,
  reason		varchar(2048)
  );
  
create table comp_matchup_sessions (
  sessionid	integer not null primary key,
  start_timestamp	timestamp default now(),
  end_timestamp		timestamp,
  current_question	integer,
  last_answer           integer
  );

create table simulation_steps (
   step_id             integer not null primary key,
   component_type_id   integer not null references component_types(component_type_id),
   step_order          integer not null,
   description         varchar(4096),
   direction           varchar(256)
   );

create table simulation_sessions (
  sessionid	        integer not null primary key,
  start_timestamp	timestamp default now(),
  end_timestamp		timestamp,
  current_step    	integer,
  last_answer           integer
  );

create table simulation_choices (
   session_id           integer not null references simulation_sessions(sessionid),
   step_id              integer not null references simulation_steps(step_id),
   component_id         integer not null references components(component_id)
   );

create table comp_conflicts (
   comp1   integer not null,
   comp2   integer not null,
   reason  varchar(512)
);