• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 151
  • Last Modified:

create new columns in a table

I have a table called tblMonthlyReport2

I want to add the following 20 columns with DataType Date:

MR6Date(0)
MR6Date(1)
MR6Date(2)
.......
MR6Date(19)

How do I do this?
0
al4629740
Asked:
al4629740
  • 4
  • 3
  • 2
  • +2
1 Solution
 
jorgedeoliveiraborgesCommented:
I suggest you to create a child table and store the values.
As far as I know, in SQL SERVER, we cant create a multivalued column.

0
 
al4629740Author Commented:
Each of these are a separate column....

MR6Date(0)
MR6Date(1)
MR6Date(2)
.......
MR6Date(19)
0
 
lluddenCommented:
ALTER TABLE dbo.tblMonthlyReport2 ADD
      [MR6Date(0)] date NULL,
      [MR6Date(1)] date NULL,
etc

This is a pretty poor design for most things.  You are better off adding a new table with a date column and an ID field referencing back to the primary table.

You don't generally want columns of repeating data.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
al4629740Author Commented:
What is the reason why having so many columns is a problem?
0
 
lluddenCommented:
In general, it is bad form because it makes a complicated query.  If you want to find if a date is used for a row, you have to examine each row:

SELECT * FROM MyRecords WHERE MR6Date1 = @Date OR MR6Date2= @Date or MR6Date3 = @date
etc

It also limits you to the max number of records you have created columns for.  If you move the data to another table, you can do the query:
SELECT * FROM MyRecords WHERE
MyRecordID IN (SELECT MyRecordID FROM RecordDates WHERE MR6Date = @Date)

This works for any number of rows.

0
 
Anthony PerkinsCommented:
>>What is the reason why having so many columns is a problem? <<
May I suggest you read up on normalization?  The question may be clearer after you understand the concept.
0
 
dwkorCommented:
While I agreed that in a lot of cases extra table would help, I'd be very careful suggesting this without asking for additional details about the system. First, we don't know if author needs to filter by either of those columns. Second, and more importantly, 20 extra joins could introduce the huge overhead in case if client code cannot be changed and query needs to provide all the dates in 1 row.

So design with 20 columns could be acceptable (and work better) in some cases. Another approach is to put dates to (indexed) XML column.

So bottom line - we need to know more before criticizing the design.

 
0
 
jorgedeoliveiraborgesCommented:
>>What is the reason why having so many columns is a problem? <<

...
The concept of a "repeating group" is, however, understood in different ways by different theorists. As a consequence, there is no universal agreement as to which features would disqualify a table from being in 1NF. Most notably, 1NF as defined by some authors (for example, Ramez Elmasri and Shamkant B. Navathe,[3] following the precedent established by Edgar F. Codd) excludes relation-valued attributes (tables within tables); whereas 1NF as defined by other authors (for example, Chris Date) permits them....

http://en.wikipedia.org/wiki/First_normal_form
0
 
jorgedeoliveiraborgesCommented:
Please, read this: (Thanks, anyway).

Design pattern: multivalued attributes (hobbies)
http://www.tomjewett.com/dbdesign/dbdesign.php?page=hobbies.php
0
 
jorgedeoliveiraborgesCommented:
>> This is a pretty poor design for most things.  << #a37021220

The DBMS your are using does not complies with multivalued attributes.
0
 
dwkorCommented:
We're not attending university classes here but dealing with the production systems. There are quite a few cases when 20 columns design author suggested make more sense from performance standpoint in compare with the separate table.

It's amazing that people are giving advices how to (re)design the system without asking single question about the system in general
0
 
al4629740Author Commented:
This is helpful and thank you for your discussion
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now