The Nightstar Zoo

Nightstar IRC Network - irc.nightstar.net
It is currently Fri Dec 15, 2017 5:16 am

All times are UTC - 6 hours [ DST ]




Post new topic Reply to topic  [ 3 posts ] 
Author Message
PostPosted: Sat Jul 03, 2004 4:12 pm 
Offline
Nightstar Graveyard Daemon
User avatar

Joined: Mon Jun 03, 2002 8:30 pm
Posts: 1071
Location: Wouldn't you rather observe my Velocity?
Okay, I've been doing a lot of thinking about The Database Problem. Here are some of my findings.

First, it's not an OO vs. DB thing that's the problem. It's a nonstupid design vs. DB thing. Think about my original problem statement with the database: "I am considering moving to an OO framework if it makes sense, because the existing procedural code is awful." We couldn't see an obviously intelligent way to do it in OO, but that's because there's not a readily intelligent way to encapsulate database access at all.

This thread is for intelligent database design. Give it your best shot--Procedural solutions are just as welcome as OO ones. If you want to talk about your own problem domain, go for it, but if you want a problem domain to sink your teeth into, feel free to use mine. Here it is YET AGAIN for those of you who can't be bothered to keep track of every change made to every post in every thread in this forum (remember that the names have been changed to protect my NDA):

Quote:
StarTrack corporation owns a secure broadband network and provides VPN access to hotels. They do this through two channels: Zoom and InfoBahn. Zoom provides free network access. InfoBahn is a fee-based service. StarTrack wants to survey their customers to improve their service by finding and eliminating problems as well as identifying potential new areas of business (such as adding a document printing service or external e-mail hosting).

StarTrack has produced a survey for each company. They are identical except for two extra questions in the InfoBahn service intended to track the success of their brand (since it is a fee-based service). StarTrack wants to be able to make small changes to the survey (adding a few questions, taking a few away) without losing the ability to track old question or historical changes to common questions over time. (E.g. they recenttly removed the questions about document printing and added a question aboun VPN branding. They want to retain the printing data for historical purposes even though new surveys do not address this question anymore. There is a question about customer satisfaction that is present in both the old and the new survey. They want to pull the survey responses from the old survey and the new and have a continuous historical representation of that data.)

StarTrack has several reports that they pull every week. The three most important ones are
  1. A report showing the total number of responses for a given date range, then a list of every question in the survey and every answer to each question with percentages of respondents. (E.g. question 1 is "Are you traveling on Business or Leisure today", and the responses might be "Business: 92.3%, Leisure: 6.1%, Business and Leisure: 1.2%, Trade Show or Conference: 0.4%")
  2. A report showing every comment left by customers. There is a "suggestion box" at the end of the survey. About 15% of Zoom customers leave comments, while nearly 40% of InfoBahn customers respond. This report gets all the responses that have comments from a date range and displays them sorted by either hotel and date.
  3. Zoom vs. InfoBahn and wired vs. wireless. From a date range, compare the customer satisfaction of Zoom vs. InfoBahn and Wired vs. Wireless.

StarTrack requests custom reports about twice a week, and about once a month a custom report will be generated that is useful enough that they ask it to be included in the list of regular reports.

StarTrack also regularly requests a pair of custom reports of a specific type. They go to present their data to the investors or board of directors of a hotel chain such as Marriott or Ritz Cartlon and want to present data on how their service is affecting the hotel itself. (StarTrack could lose the contract to provide broadband access if enough customers complain that they would choose a different hotel in hopes of finding better network access.) About once every two weeks StarTrack will provide a list of properties--it is not yet clear how much these lists change, so far they've given us three different lists for three different property chains--and we provide a historical report of customer satisfaction, along with a report pulling all of the comments of just the dissatisfied customers in those properties. Typically this report shows one column lumping everything over the past 90 days, but occasionally they want a report showing one column per week for the past 6 weeks.

Current project notes: StarTrack purchases its webhosting from a network consulting company in Salt Lake City. Let's call it Greg's Consulting company. When StarTrack wants a new report, they call Greg, and Greg calls me. I write the code, bill Greg for the time, and StarTrack may or may not know I even exist at a business level. (At a personal level, they know, because one of their engineers used to work with me at E&S.)

This project is programmed using PHP and MySQL. The main report that shows the list of all questions & answers is in report.php, which is accessed by visiting getreport.php in a browser and filling out the start and end date for the report and which channels (sometimes they only want Zoom or just InfoBahn) should be reported. The type of report generated is hardcoded into report.php.

I am guilty of creating the "Cut and Paste Programming" antipattern on this project. When Greg calls me to write a new report for StarTrack, I copy getreport.php and report.php to a new filename, such as get_satisfaction_report.php and satisfaction_report.php. Then I tweak the selection criteria in the get_* file, and rewrite about 80% of the *_report.php file. I access mysql directly using the PHP mysql functions, like mysql_query() and mysql_fetch_object(). Some recent bugs have really put the hurt on me for doing it this way--sure enough I needed to make a change to one file that required matching changes in EVERY other report in the system. There are currently 9 different reports.


I'm going to open this up for discussion by you monkeys and ask you for your thinking on this problem. Since I'm being directly (and financially) rewarded for this effort, I'll make this offer: if you suggest a scheme that I end up using, I'll make it worth your while: Call it a contest, and I'll send the winner a shiny new copy of "Data Access Patterns: Database Interactions in Object-Oriented Applications". I bought my copy last night, and it has made me realize that the design problems I'm having aren't OO problems, but encapsulation and abstraction problems.

If Pi wins I'll offer him something less OO. :-) Perhaps "Win32 Programming" by Rector & Newcomer--a book that in my opinion completely outshines Charles Petzold's famous Windows book. It also has the property that all the code is written entirely in C. I kept thinking of Pi's dialog box problem by saying "why doesn't he just call CreateDialog()... oh, because his message handlers are in a CWindow object."

Tell you what, I'll give the winner their choice. You're up against steep competition, though... did I mention I bought my copy of Data Access Patterns last night?

I will post ideas and discussion-starters about database design here as I have them, and I will post my solution here after I have implemented it. If I use a solution from DAP that you post here first, I'll give you the credit. To get credit, you must discuss how your solution would work--no reading the book's chapter headings on Amazon and posting them here without discussion! :P


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jul 07, 2004 5:33 pm 
Offline
Nightstar Graveyard Daemon
User avatar

Joined: Mon Jun 03, 2002 8:30 pm
Posts: 1071
Location: Wouldn't you rather observe my Velocity?
You guys all suck. AGAIN. :P


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jul 07, 2004 6:12 pm 
Chalain wrote:
You guys all suck. AGAIN. :P

Sorry, dude. Not taking the bait this time.

I've got enough programming problems of my own here that I am getting paid to solve. Besides, I hate databases.


Top
  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 

All times are UTC - 6 hours [ DST ]


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
Powered by phpBB® Forum Software © phpBB Group