SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Avoiding Large Number of db Columns in SQL Server - Database Redesign


Avoiding Large Number of db Columns in SQL Server - Database Redesign

Author
Message
cajun_sql
cajun_sql
SSC Eights!
SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)

Group: General Forum Members
Points: 930 Visits: 400
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.
LutzM
LutzM
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10343 Visits: 13559
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 ;-)



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
cajun_sql
cajun_sql
SSC Eights!
SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)

Group: General Forum Members
Points: 930 Visits: 400
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. :-)
LutzM
LutzM
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10343 Visits: 13559
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).



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search