November 27, 2013 at 1:05 pm
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.
November 27, 2013 at 2:14 pm
I'd go with a separate table with the columns ObjectiveId (as a foreign key reference to the current table), OutcomeId, KSA_aspectNo, KSA_AspectValue together with a column referencing the related.
I'm not sure if I'd go for a separate Identity column as the clustered index or if I'd use ObjectiveId, OutcomeId, and KSA_aspectNo as the natural key.
Most probably I'd go with the separate column together with a few constraints... It depends 😉
December 4, 2013 at 2:23 pm
LutzM (11/27/2013)
I'd go with a separate table with the columns ObjectiveId (as a foreign key reference to the current table), OutcomeId, KSA_aspectNo, KSA_AspectValue together with a column referencing the related.I'm not sure if I'd go for a separate Identity column as the clustered index or if I'd use ObjectiveId, OutcomeId, and KSA_aspectNo as the natural key.
Most probably I'd go with the separate column together with a few constraints... It depends 😉
Thanks! That helps alot.
When doing inserts into these tables from my Classic ASP Application, I had thought of inserting the PK in the form of a GUID generated by the application. My challenge was 'how to insert it for use in each table simultaneously?'
Is there a problem with doing it this way, or would using an Autonumber with some sort of trigger to the other tables be possible/preferrable?
Thanks again for any input. 🙂
December 5, 2013 at 11:51 am
If you need to know the auto-generated identity value of the rows that has been added you could use the OUTPUT clause within your first INSERT statement together with the columns that will ligically uniquely identify each row. Then you can "re-use" the identiy value for the following insert statement(s).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply