MRDigiBoard

********

OutlineSe

ction

1

• Gain competitive edge

• Provide excellent customer service

• Promote profitable business activities

• Provide affordable living

• Provide sophisticated living

• Ensure quality management

• Efficient maintenance & repair

Secti

on 2

Secti

on 3

• Improve the flow of information

• Reduce work order processing time

• Increase maintenance employee accountability

• Store critical business data

• Accurate reporting

• Improve tenant relations

• Manage & repair apartments effectively

• Improve record keeping

• Easy GUI to navigate with

• Use reports to make effective business decisions

• Enhanced communication between employees with bilingual comments

Objectives SummaryO

rgan

izatio

n

• Gain competitive edge

• Provide excellent customer service

• Promote profitable business activities

• Provide affordable living

• Provide sophisticated living

• Ensure quality management

• Efficient maintenance & repair

Syst

em

Use

r

• Improve the flow of information

• Reduce work order processing time

• Increase maintenance employee accountability

• Store critical business data

• Accurate reporting

• Improve tenant relations

• Manage & repair apartments effectively

• Improve record keeping

• Easy GUI to navigate with

• Use reports to make effective business decisions

• Enhanced communication between employees with bilingual comments

Financial Impact

Problem - Information on ‘make ready’ board is often outdated and unreliable.

Result - Work order performed more than once or not at all.

Impact - Supply waste - $30 a month Loss of one tenant - $450 a month

Current System - Impact

Problem - Lack of central data storage Result - Walking back to office to clarify work Impact - Average profit loss of 3% annually

25 minutes added to time or an extra $25 a week

Current System - Impact

Whiteboard is manual = Increase work order processing time Impact:

10 minutes added to processing time or $10 a week

25 minutes added to processing time or $25 a week

Current System - Impact

Lack of accountability = Unable to evaluate employee on work performance Impact:

Slowest work order time = 4 weeks or $200

Proposed System - Options

Automate work orders to system specifications

Web-based database with user friendly front-end

Database hosting and maintenance offloaded to hosting company ($19.95 per month)

Software

•Apache 2.x (Free Limited Support)•PHP 5.x (Free Limited Support)•SQL Server 2005 (Free Limited Support)•Visual Basic 2008 (Free Limited Support)•Google Code (Free Limited Support)

Hardware

•Cisco Catalyst Series Router ($350.00)

Feasibility Analysis

Technical• The proposed system will utilize readily available, off-the-shelf

software such as Microsoft Windows Server 2008 and Microsoft SQL Server 2005 Workgroup Edition.

Economic• The proposed system can be implemented with minimal

additional overhead and costs

Operational• New system will be intuitive and user-friendly, fficiency, by

helping staff members who do not speak English.

Cost of Ownership

Hardware and Software Summary Cost

M.R. Digiboard Development and Testing $33,100.00

UH Student Discount (-$33,100.00)Database Maintenance

$19.95/mo x 12 months x 4 years $ 957.00

Software Purchases $ 739.00Hardware Purchase

Cisco Catalyst Series Router $ 350.00Server Hosting

$19.95/mo x 12 months x 4 years $ 957.00User and IT Staff Training $ 900.00Total Cost of Ownership (4 years, student discount) $4,700.00

WEIRDOS Design Process

Development Tools

Developed using industry standard software Microsoft Visual Basic 2008 – Programming and

debugging user interface Microsoft SQL Server 2005 – Programming and

debugging database Adobe Creative Suite 4 – Create and edit graphics for

user interface and project documents … and more.

Project Timeline

Jan 2009WEIRDOS form

partnership withCase Nube

Apr 2009Project proposal

officially presented

Feb 2009‘M.R. Digiboard’ is

born

Feb-Apr 2009In-depth analysis of

business process begins

Early Nov. 2009GUI finalized, final

testing begins

Mid Nov. 2009M.R. Digiboard presented and

officially released

Aug-Sep 2009Database planning and

architecture begins

October 2009Database structure finalized, frontend

work started

Initially 15 tables• Now 53 tables• 6 tables assigned to each team member

Difficulties/Errors• Unmatched data• Data Dictionary matched with primary table

Naming Conventions• Attributes• Lower Case

Table• Upper case for entities• Underscore to separate words

Implementation Process

Entity Relationship Diagram

COMPLAINT

PK complaint_ID

FK unit_ID

FK compliant_type_ID

COMPLIANT_TENANT

PK complaint_tenant_ID

FK tenant_ID

FK complaint_ID

UNIT

PK unit_ID

FK building_ID

FK floorplan_ID

FK color_ID

FK flooring_ID

Lists all entities, attributes and relationships

Abstract and conceptual representation of database

Generated with Access, Visio and ConceptDraw Pro

Early ERD – September 2009

• 14 Total Entities (Tables)

• Multiple many-to-many relationships

• Unnormalized Form (UNF)

Final ERD – October 2009

• Entities increased to 50• Many-to-many

relationships broken down

• Normalized to 3NF

ASSIGNMENT

PK assignment_ID

FK employee_ID

ERD: Employee

Problem Unnecessary information

Solution Remove entity

EMPLOYEE

PK employee_ID

EMERGENCY_CONTACT_EMPLOYEE

PK emergency_contact_employee_ID

FK emergency_contact_ID

FK employee_ID

ERD: Emergency Contact

Problem Missing Associative Tables

Solution Add Table that handles the M:N relationship

EMERGENCY_CONTACT

PK emergency_contact_ID

FK relationship_ID

EMERGENCY_CONTACT_TENANT

PK emergency_contact_tenant_ID

FK emergency_contact_ID

FK tenant_ID

TENANT

PK tenant_ID

EMPLOYEE

PK employee_ID

ERD: Property

EMPLOYEE

PK employee_ID

FK property_ID

PROPERTY

PK property_ID

employee_IDFK

Issue – Sending foreign key to both entities

Solution – Remove foreign key from one side of entity

PROBLEM

PK problem_ID

FK problem_type_ID

ERD: Problem Table

PROBLEM_TYPE_LOOKUP

PK problem_type_ID

PROLEM_LIST_LOOKUP

PK problem_list_ID

Issue – Incorrect relationshipsSolution – Adjust constraints

Completed Solution

Fully Digital ‘Whiteboard’Work order forms with ample space for comments Work order reports that indicate statusAssignment report for each userMake-ready reports for each userPet information on work order report

User Interface Benefits

Simple, intuitive designEffortless navigation with minimal user training required.

Dual-Scale FunctionalityQuick and simple reports for employees, in-depth edit and lookup forms for managers.

Flexible DesignDeveloped with future expansion in mind.

Maintenance and Backups

Effortless BackupsDaily, server-wide backups performed by hosting provider automatically – easy.

Off-Site Backup StorageBackups shipped offsite weekly

Instant RedundancyRAID-1 disk mirroring to safeguard against individual hard disk failures.

Lessons Learned

Think critically early on – An ounce of prevention is worth a pound of cure.

Work smart, not hard – Consolidate knowledge and save time.

The small mistakes can cost just as much time as the big ones.

Questions?