Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Avoiding Large Number of db Columns in SQL Server - Database Redesign Expand / Collapse
Author
Message
Posted Wednesday, November 27, 2013 1:05 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 12:12 PM
Points: 907, Visits: 263
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.
Post #1518193
Posted Wednesday, November 27, 2013 2:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, September 7, 2014 11:27 PM
Points: 7,164, Visits: 13,257
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
Post #1518212
Posted Wednesday, December 4, 2013 2:23 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 12:12 PM
Points: 907, Visits: 263
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.
Post #1519788
Posted Thursday, December 5, 2013 11:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, September 7, 2014 11:27 PM
Points: 7,164, Visits: 13,257
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
Post #1520271
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse