Bulletin board - Database optimisation
- by andrew
This question is a follow on from this
Question
The project and problem
The project I am currently working on is a bulletin board for a large non-profit organisation. The bulletin board will be used to allow inter-office communication within the organisation.
I am building the application and have been having trouble extracting the results that I need from my database because I don't think it is properly normalized and because of limitations in my knowledge of relational database theory and mysql. I would appreciate input into the design of the board in general and in particular, ways that the database structure can be improved to facilitate efficient queries and help me develop this application and future application faster
Business Logic
The bulletin board will be used in the following way
Posting bulletins and responses to bulletins
Employees or 'users' in offices around the country will be able to post messages to the bulletin board.Bulletins must be posted to a location and categorised- i'll call these "bulletins".
Users will be able to post any number of replies to any one bulletin and users will be able to reply to their own bulletin - i'll call these 'replies'.
Rating bulletins and replies
Users will be able to either 'like' or 'dislike' a bulletin or a reply and the total number of likes or dislikes will be shown for each bulletin or reply.
Viewing the bulletin board and responses
Bulletins can be displayed chronologically.
Users can sort bulletins chronologically or chronologically by the latest reply to that bulletin(let me know if you need more explanation)
When a particular bulletin is selected, replies to that bulletin will be displayed chronologically
@PerformanceDBA - edited 10:34 est 28/12/10I have begun implementing the data model. I assume that the 6th data model is the physical model because it contains the associative tables. I am going to post any questions that I have below. I will put up a database dump once I am done. I will then put up a list of all the queries that I need to run on the database and begin writing them. I hope you had a good Christmas. I'm in Canada and there's snow!
Implementation of Physical model