To get primary and foreign keys right in ERD

Answer 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

Answer 2

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.