Final Project: Property Names & Addresses

In my daytime job with the New York City Department of Finance, we collect business and property taxes for the City. One of our biggest problems for Property taxes is to whom to send tax notices, whether it is the owner, managing agent, accountant, lawyer, bank (mortgage servicing organization) or other entity associated with over 1M commercial and residential properties in the five boroughs of New York City. Such "primary recipient" of tax notices for a given property frequently changes.

We currently have in DOF several solutions for these "name and address" problems, none of which works perfectly. One solution might be a new Web-based system for maintaining this extensive data of which this final project could serve as a simple prototype. Due to time constraints - there are only two weeks left in the semester - this project is inquiry-only; data entry and update capabilities would be added later. Due to time constraints this project also does not include login and signup screens similar to what I already did for the class7 and class9 assignments. But this project does include three database tables related by foreign keys, the MVC framework, simple pagination, session cookies, JavaScript ("onclick") and - time permitting - a simple RSS feed based on the RSS feed that I got working for class9.

This project has three screens - wireframes shown below - and three database tables. The three database tables are for Properties, Related Entities (owners etc) and Relationships between properties and their related entities. The Relationships table is an "assocation table" implementing the many-to-many relations between properties and related entities. For examples, a single property can have several owners and a given owner can own multiple properties.

The three screens below are first an initial list of properties from which to select via a JavaScript "onclick" event on the picture for the property. The next screen is a "list" screen of all the entities associated with that property. The entity currently designated as the primary recipient for that property is highlighted in red on this screen, based on a flag setting in the Relationships database table. A set of radio buttons on the left of the list screen allows the user to pick related entities one at a time for viewing on the third screen, which shows name, address and similar infomation for the selected entry. At the top of each screen is the image and description of the property originally selected on the first screen so that the user does not lose track of which property they are viewing.

DB many-to-many: use "association table"

In response to questions about implementing many-to-many relationships in a relational database, I have been working with DB2 mainframe databases for over 18 years. Many-to-many relationships frequently arise, for example students enrolled in a given course each semester. Any one student will have several courses in a semester and each course given in that semester will have several students enrolled in it.

The usual solution for these situations is to have what is generically called an "association table". Each row in the association table will have two foreign keys, one foreign key pointing to student# in a Students table and the other foreign key pointing to course# in a Courses table. The primary key for this Enrollments table would be the composite key of these two foreign keys. Each row in this Enrollments table would have separate columns for attributes (properties) such as seat number and final grade, which are different for each student in each course.

On the other hand, attributes such as room location or cell phone would belong either to the parent Students table or to the parents Courses table. For example, our Intensive Web Development is held in Room 202 of 48 Cooper Square whether anybody enrolls in the course or not. So room# belongs in the Courses table not the Enrollments table. My cell phone# is completely independent of whatever courses I take. So cell phone# belongs in the Students table not the Enrollments table.

Client-Side Summary First Six Weeks

Here are links to each of my client-side assignments during the first six weeks of this semester. Images of each of these applications are shown directly in my previous blogs.

Class 1 Wireframe "Pet Shop"

Class 2 First XHTML/CSS layout

Class 3 "Personal Web Page"

Class 4 "Shipping Address" JavaScript

Class 5/6 "E-Commerce" PHP & Event Observers

Class 6 - Assignment 1

Here is the image of last week's assignment, which was to add PHP and eliminate flickering to the assignment for week 5. I also added new picture elements to the web page.

Here is the link to the files in my folder in onepotcooking.com.