0

I read my notes and I am now unsure about the keys.

My logical diagram

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

This table should be in line with the following physical ERD

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

I have only one foreign key in the table Question.

I would like to know

  • should the question-id and question-tag in the table Question-tag-xref, AND question-id and answer in the table Answers be also foreign keys?
Léo Léopold Hertz 준영
  • 134,464
  • 179
  • 445
  • 697

2 Answers2

1

Looking at both ERDs, here are the foreign keys I would want to put in place:

First ERD: Question-Tag-xref(question-id) should refer to Question(question-id). Answers(question-id) should refer to Question(question-id). Question(user-id) should refer to User(user-id).

Second ERD: Question-Tag-xref(question-id) should refer to Question(question-id). Answers(question-id) should refer to Question(question-id). Question(user-id) should refer to user-info(user-id).

I get only one foreign key in the Question table too.

In answer to your question: The column question-id in Question-tag-xref should have a foreign key reference to Question(question-id). The column question-id in the answers table should have a foreign key reference to Question(question-id)

K

Karl T.
  • 492
  • 3
  • 3
  • Please, see my edit of my question. I updated the pictures to show you how I understand your answer. – Léo Léopold Hertz 준영 Jul 26 '09 at 16:32
  • Logical and Physical ERD models represent the same model - there's only one ERD to comment on here. Logical ERDs don't contain data type information, but do indicate if a table attribute is optional (able to be null or not). Attributes in a Logical model get converted into table columns in the Physical, but it isn't always a 1 to 1 relationship. – OMG Ponies Jul 26 '09 at 17:15
  • @rexem: Could you give an example where I should add `null` or `not`? – Léo Léopold Hertz 준영 Jul 26 '09 at 17:43
1

Masi - Logical models don't usually contain data type (string, int, etc) information, but do indicate if the attribute (becomes a column in the Physical model) is optional (able to be null or not).

That said, yes - the QUESTION-ID and QUESTION-TAG columns would be foreign keys in the QUESTION-TAG-XREF table. Here's a physical view of QUESTION-TAG-XREF:

QUESTION-TAGS-XREF

  • QUESTION-ID (pk, fk relationship with QUESTIONS table)
  • QUESTION-TAG-CODE (pk, fk relationship with QUESTION-TAG table)

Both columns are the pk to ensure you can't have duplicate tags for a given question.

You are correct that QUESTION-ID would be a foreign key in the ANSWERS table, in reference to the QUESTION table.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502