Matt Posted December 3, 2012 Share Posted December 3, 2012 Hello everyone, HartleySan and I are working on a web project for a university and my question is about the approach to take for the database design. Basically, the site is designed to help students find tutors for various classes. What happens is that a tutor fills out a profile with basic personal details, as well as price and availability. Students who register on the site can view the tutor's profiles, and when they find one they like, they can click on a "poke" button to send the tutor a request that they are interested in his/her services. Both students and tutors have a control panel where they can view requests they've made/received. After a student has requested a tutor, he/she will see the student added to a table in their control panel where they can either "accept" or "deny" the request. If they accept it, then the student is given a status of "pending" in both the student's and tutor's table. From there, the student can make the final "accept" to finish the transaction and then get the tutors personal contact information. I know that probably sounds confusing the way I described it, but it's really easy. The problem is how to model these 3 different transactions (student request of teacher, teacher acceptance of student, final accept by student) in database tables. There are several possible solutions, but all of them have positive and negative points: 1) Make one table with the student_id, tutor_id, date_requested, date_pending, and date_accepted. This would be the easiest and most secure, but the problem is that it's not normalized at all (not even 1NF). There would be empty columns when transactions aren't completed. 2) Make 2 tables: One with student_id, tutor_id, date_requested, and date_pending and another with student_id, tutor_id, and date_accepted. I like this one the most as it keeps the incomplete transaction in a separate table from the completed one (i.e. student finally accepts the tutor and becomes his customer) and is also more secure. 3) Make 3 tables (one for each step of the process). When a transaction occurs and a student/tutor go from one table to the next, they will not be deleted from the previous table, so that there is a record of all transactions. This would be the normalized approach, but the queries involved could get complicated. I know this post is getting long, so I'll end it here. If anyone has any input or advice as to how they would approach this, it would be greatly appreciated. Thanks in advance, Matt Link to comment Share on other sites More sharing options...
Larry Posted December 4, 2012 Share Posted December 4, 2012 I think option 1 is the most logical. I don't see any problems with it from a normalization standpoint because you'll be able to use NULL values as important information (i.e., steps not completed). Really a no-brainer to me. 2 Link to comment Share on other sites More sharing options...
Matt Posted December 6, 2012 Author Share Posted December 6, 2012 Larry, Thank you for the quick and decisive reply! I always trust your intuition on this sort of thing and I do agree that option 1 is the easiest to implement, query, and maintain (although I might not go as far as to say that it's "normalized", as multiple NULL values are being used to store data). That feedback is always helpful! Thanks again! Matt Link to comment Share on other sites More sharing options...
Recommended Posts