How to improve a ERD

Question

This question is based on my plan at the thread.

My entity-relationship diagram is

alt text http://files.getdropbox.com/u/175564/sequence-diagram.png

I would like to know the following.

Does my sequence diagram match the following relation diagram?

alt text http://files.getdropbox.com/u/175564/table-problem-3.png

Answer 1

The first diagram looks a bit more like a class diagram.

Answer 2

This is my reply to rexem's answer.

I understand your answer as follows:

alt text http://files.getdropbox.com/u/175564/db-problem-2.png

and the second diagram as

alt text http://files.getdropbox.com/u/175564/db-problem-1.png

I left the xref table for the question-body because I may need to store the changes to my questions too in the future.

Answer 3

Reviewing the first diagram as the Logical model, typically the tables defined in the Logical also exist in the physical - you're missing the QUESTION-TAGS (nee QUESTION-SUBJECTS) table. You have a one-to-one relationship between QUESTION and MONITOR-CHECK, which means there's no value to having MONITOR-CHECK - move the attributes into the QUESTION table.

Viewing the second diagram as the Physical ERD model - there's no benefit to having two tables with a one-to-one relationship. I'm speaking about the QUESTION / QUESTION-BODY, and QUESTION / MODERATOR-CHECK tables.

QUESTION-SUBJECTS is poorly named - if by your note the record represents a "tag", then name the table accordingly. This table would be a code table (having CODE, DESCRIPTION columns) because you want to keep the values consistent for queries to perform well. That said, you need an xref/lookup/corollary table between the QUESTION and QUESTION-TAGS table in order to have a one [question related] to many [question tags].

QUESTION-TAG-XREF

• QUESTION-ID (pk)
• QUESTION-TAG-CODE (pk)