The first diagram looks a bit more like a class diagram.
sblair
2009-07-25 20:07:01
+1
A:
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)
OMG Ponies
2009-07-25 20:37:43
Please, see my reply to your question above. Do you mean that?
Masi
2009-07-25 21:27:42
Thank you for your answer!
Masi
2009-07-25 21:45:38
Please, see my comment to your comment below.
Masi
2009-07-26 03:56:53
I opened the following thread based on your answers at http://stackoverflow.com/questions/1185626/why-does-many-to-many-data-structure-require-two-additional-tables
Masi
2009-07-26 21:41:18
A:
Masi
2009-07-25 21:27:19
OMG Ponies
2009-07-26 00:39:57
`Both lack the QUESTION-TAGS table.` @ Do you mean that to add a new "help-table" between `Question-Tag-xref` and `Question`?
Masi
2009-07-26 03:51:36
No. I don't recognize the help-table as representing an actual table, just a note on the model. You need to add QUESTION-TAGS as a table to both models, attached to the QUESTION-TAG-XREF table.
OMG Ponies
2009-07-26 16:55:26