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

Or a class diagram... Yours seem to be in between, but it's definitely not a sequence diagram. — Andrew Moore, Jul 25 '09 at 20:17
The diagrams read to me as the first one being the Logical model, and the second is the Physical. — OMG Ponies, Jul 25 '09 at 20:22

score 1 · Accepted Answer · answered Jul 25 '09 at 20:37

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)

Please, see my reply to your question above. Do you mean that? — Léo Léopold Hertz 준영, Jul 25 '09 at 21:27
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 — Léo Léopold Hertz 준영, Jul 26 '09 at 21:41

Léo Léopold Hertz 준영 · Answer 2 · 2009-07-25T21:41:37.090

1

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.

edited Jul 25 '09 at 21:41

answered Jul 25 '09 at 21:27

Léo Léopold Hertz 준영

134,464
179
445
697

Both lack the QUESTION-TAGS table. Table names don't change between logical and physical models - QUESTION-HISTORY-XREF and QUESTION-BODY should actually be QUESTION-HISTORY. If you think you need the QUESTION-HISTORY as an audit table, then keep it. I marked you back up to 0 cuz stackoverflow doesn't allow for back & forth conversation. – OMG Ponies Jul 26 '09 at 00:39
`Both lack the QUESTION-TAGS table.` @ Do you mean that to add a new "help-table" between `Question-Tag-xref` and `Question`? – Léo Léopold Hertz 준영 Jul 26 '09 at 03:51
1

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 Jul 26 '09 at 16:55

score 0 · Answer 3 · answered Jul 25 '09 at 20:07

0

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

answered Jul 25 '09 at 20:07

sblair

1,085
2
13
22

How to improve a ERD

3 Answers3

Linked