I'm working on a database table in SQL Server 2008, which is the backend for a Classic ASP application. I inherited this database table, which holds 'outcomes' for each course.
Each row represents an 'objective,' and then the 'outcomes' are in the columns - the column values indicate whether or not the objective meets that particular outcome.
Here is an example of the current db table's columns:
ID | Objective | Outcome1 | Outcome2 | ... Outcome11
Recently, the need was expressed to have a new set of descriptors added to each row. Called "KSA"s for short, it stands for "Knowledge, Skills, and Attitudes". Basically a KSA set "explodes" the description for each Outcome into fine detail.
Within Knowledge, there are a set of identifiers particular to the first outcome. The second outcome has a different set of identifiers, and so on. "Skills" and "Attitudes" each have their own identifiers that also differ from outcome to outcome.
To help summarize, there are an unknown quantity of objectives for each course - most usually 10-20. The reason they are unknown is that they are input into the application by faculty members, and the quantity depends upon the particular requirements of their course.
For each objective (row), there are 11 outcome columns already existing.
The new KSA aspect will contain:
...... a variable number of identifiers for "Knowledge" that relates to outcome 1.
and... a variable number of identifiers for "Knowledge" that relates to outcome 2.
and so on...thru outcome 11
and... a variable number of identifiers for "Skills" that relates to outcome 1...
and... a variable number of identifiers for "Skills" that relates to outcome 2...
and so on...thru outcome 11
and... the same for "Attitudes"
and so on...thru outcome 11
I inherited the table in which a separate column was provided for each of the 11 outcomes. We will be keeping this aspect and adding more detail with the addition of the KSAs. With the addition of the KSAs, though, I am wondering about the best way to go about adding the data structure. If I was to provide a separate column for each KSA, I would wind up with around 140 new columns, and it just seems that there should be a better way to hold the data. Additionally, many of the KSA columns could contain a null.
Also, I had thought of having all of the KSA data in one column, as a delimited string, but when the user performed a search, it seemed that there would be a performance problem, in that quite a few operations would need to be performed on the string each time.
Here is an example of the data in the KSA, in 'outline' form, which illustrates how each outcome is broken down into finer detail with the KSA descriptors:
Outcome: Calculations-The graduate will be able to perform calculations accurately.
Knowledge:
1. specific calculations
2. specific factors that influence calculations.
Skills:
1. employing consistently accurate mathematical ability in all calculations.
2. applying principles of calculations and specific factors to determine the appropriate use of therapy.
3. completing calculations needed to prepare specific results.
Attitudes:
1. stating the importance of accurate calculations.
2. recognizing the impact of calculation errors on response and risk
What might be the best way to add the KSA element? I had thought of using a separate table, due to the way the data for each outcome is split into multiple descriptors, but wasn't sure how to go about that, logically.
Thanks for any help.
