This project is much more difficult than we thought at the very beginning. From EDM to CDM to 3NF, the number of entities increases exponentially. In the physical level, we totally have 32 tables; however, the business rules, which should be reflected in table relations (references) are even more complicated.
MadSci is a successful Ask-A-Scientist network, they've developed a sophisticated process to edit, pass, respond, and evaluate questions and answers. Besides of questions, they also deal with data about scientific experiments and other WWW resource. We spent a lot of time on their web site, reviewing page by page, then think, talk, disagree, argue, still disagree, argue again? MadSci has complete user views for each of its function. However, sometimes different user views may repeat similar work, and data redundant is not rare. In order to make our ERDs consistent with users views, we created our own version. One can refer to the Appendix A to see the original user views.
Another challenging work is to carefully figure out how MadSci works, finding all the detailed business rules, and apply them to the ERDs. We have at least 10 different models for our final CDM (the integrated ERDs). Every time we made a change to CDM, we have to also change the logical part and the physical part. The work seems endless. Many thanks to Dr. Shook, who patiently listened to us, discussed with us, and gave us a lot of precious advice. We cannot say our model is perfect now, there are still many steps for MadSci to run on a relational DBMS. However, here we provided one solution, and we'd like to further this project with MadSci to make the whole thing sing!
Many thanks to Lynn Bry and Ricky Sethi, who provide us such a great project, and allowed us access to MadSci moderator's user views, and wrote a lot of helpful emails. We feel much stronger in the data-modeling field, also much closer to the ultimate goal: data mavens. Sincerely hope our model help MadSci improve the performance and become even more successful!