Database design question - recurring data, best practices

[colezpapa3]

I have a database that tracks clients. Most of the information that is stored deals with a series of quesstions about the client. Now, every 3 years we review the client and ask the same questions again. We don't want to lose the answers to the orginal questions, but we want to store the new information. I can make the fields memo enabling them to hold larger amounts of text...but as for design ideas what would be a better way to go? Essestially every three years, they need to go thru the questions again.

 

[fneily]

You didn't show your existing table structure. Also, does every client get the same questions? I'll assume so.
You have a many-to-many relationship. One client can answer many questions, one question can be asked to many clients. So, three tables:
tblClient with ClientID and other client info fields
tblQuestions with QuestionID and Description
and a so-called junction table -
tblClientAnswers with ClientAnsID, ClientID, QuestionID, Date (primary key is ClientAnsID. ClientID and QuestionID are common fields in this table)
So every three years, you'd just add more records to the junction table. Then you can run any analysis off of this table.

You may want to read:
Fundamentals of Relational Database Design

http://r937.com/relational.html

 

[colezpapa3]

Hi

Yes, that design would work. Thanks