CODEWITHSUNDEEP
sqldatabasenormalization
Barrosy
Barrosy

Reputation: 1457

One table reference to two different tables with one foreign key, possible?

I am not sure if this is just a lame stupid question, but then again, I just need to know how exactly database design works.

So I have this database with tables called customers, currentsurveyresults and overallsurveyresults, which show survey results the customers fill in.

dbo.customers
----------
customerID
customerName
surveyID

dbo.currentsurveyresults
------------------------
surveyID
questionanswer1
questionanswer2

dbo.overallsurveyresults
------------------------
surveyID
questionanswer1
questionanswer2.a
questionanswer2.b

To get a little bit of why I named/called the tables like this:

The overallsurveryresults table should show a more detailed table (the customer will fill in another survey after a certain date so details can be more specific later on) compared to the currentsurveyresults table (this one will be filled in earlier on).

If I understand the primary key/foreign key principle correctly, can I assign the following?:

customers.customerID (PK)
customers.surveyID (FK)
currentsurveyresults.surveyID(PK)
overallsurveyresults.surveyID(PK)

So that I can always grab the information I need from both result tables whenever I search on customers? (which means one foreign key references to two primary keys)

So the question actually is: Can a foreign key reference to multiple primary keys? If not, should I add another surveyID column which specifies each table specificly?

Upvotes: 0

Views: 266

Answers (1)

Dawood Awan
Dawood Awan

Reputation: 7328

Your relation between Customer and Survey is wrong (According to what you are trying to do).

You said

The customer will make a survey of their opinion at that moment and later on they will fill in a survey with similar questions and more advanced question on that survey of their opinion at that later moment

You want the Customer to take more than one Survey.

Create a Survey Results table and save all results in that Table. So you know which Customer took which Survey.

CUSTOMERS                        SURVEYS               Questions
---------------             |--------------------|-----------------
CustomerId (int) PK         | SurveyId (int) PK  | QuestionId (int) PK
Name (nvarchar(500))        | Name (int)         | Quesion (nvarchar(500)


SURVEY QUESTIONS (Map N-Questions against N-Survey) (Re-use Questions different Surveys)
----------------
SurveyId (FK)
QuestionId (FK)


SURVEY RESULTS  
--------------
CustomerId  (id of Customer who took Survey)
SurveyId (Id of survey taken)
SurveyDateTime (Date Survey taken)
QuestionId (FK to Question table)
Answer     (Customer Answer)

If you don't want to re-use the Question in Different surveys you can add it to the Question Table:

Questions
----------
QuestionId
Question
SurveyId (FK)

In this method previous survey will not get overwritten.

So I think a customer takes either multiple different surveys or the same (however the latter might overwrite)

If you want to overwrite you only have to update the SURVEY RESULTS Table

Upvotes: 1

Related Questions