Adding a column to an existing table with data

[Raj]

Hi,
I am trying to add some more information to the table which already
has a lot a data (like 2-3000 records). The new information may be
adding 2-3 new columns worth. Now my questions are:
(1)Is it a good idea to add new columns to the existing table? then it
will create these new columns for all old records, will it not result
in wasting a lot of space??
(2)Is it a good idea to create a new table with the new information
and have as a forign key the index value from the first table?
There may be answers to these questions already in some threads but
since I dont have much time I thought I will post it again, if these
are already answered by someone could someone point me there? any help
would be appreciated, thanks,
raj.

[MacDermott]

It is rarely a good idea to add columns to a table -
especially not if this is something that happens repeatedly.

Consider normalizing your table design -
most likely moving some of your existing data into a new table.

For example, suppose you had a table of children and schools -
PK
Child's Name
Child's School
Then children begin changing schools, and you want a historical record.
You might think of a structure like this:
PK
Child's Name
Child's School 1
Child's Dates at School1
Child's School 2
Child's Dates at School2
Of course this will be problematic, because you'll have to keep adding
fields...

Instead, you could have 2 tables
PK
Child's Name
and
Foreign Key - matches PK of first table to identify child
School Name
School Start
School End

This will accommodate as many schools as needed.

HTH
- Turtle

"Raj" <en****@yahoo.c om> wrote in message
news:d3******** *************** ***@posting.goo gle.com...

Hi,
I am trying to add some more information to the table which already
has a lot a data (like 2-3000 records). The new information may be
adding 2-3 new columns worth. Now my questions are:
(1)Is it a good idea to add new columns to the existing table? then it
will create these new columns for all old records, will it not result
in wasting a lot of space??
(2)Is it a good idea to create a new table with the new information
and have as a forign key the index value from the first table?
There may be answers to these questions already in some threads but
since I dont have much time I thought I will post it again, if these
are already answered by someone could someone point me there? any help
would be appreciated, thanks,
raj.

[Raj]

Thanks, in my case I cant do anything with the existing table, apart
from adding a column or two at the most since there are a lot of
people using the database in its original format, even that will be
tough as we may have to update all the users databases once we rollout
the new changes, so I guess the best idea in this case is to add a new
table with all the new data and a foreign-key to link into the main
table...am I right?? Thanks again,
Raj.

"MacDermott " <ma********@nos pam.com> wrote in message news:<VR******* *********@newsr ead1.news.atl.e arthlink.net>.. .

It is rarely a good idea to add columns to a table -
especially not if this is something that happens repeatedly.

Consider normalizing your table design -
most likely moving some of your existing data into a new table.

For example, suppose you had a table of children and schools -
PK
Child's Name
Child's School
Then children begin changing schools, and you want a historical record.
You might think of a structure like this:
PK
Child's Name
Child's School 1
Child's Dates at School1
Child's School 2
Child's Dates at School2
Of course this will be problematic, because you'll have to keep adding
fields...

Instead, you could have 2 tables
PK
Child's Name
and
Foreign Key - matches PK of first table to identify child
School Name
School Start
School End

This will accommodate as many schools as needed.

HTH
- Turtle

[MacDermott]

The only case in which I would recommend your approach is if you know this
application will be replaced soon, and just need something to bridge the
gap.
One way or another, you're going to have to inconvenience your users to make
this change.
Why not just inconvenience them once, and give them a more flexible
structure, so further changes won't be such an inconvenience?

My general rule is that data structure changes should be made as early as
possible.
Trying to work around poorly designed tables has not been worth the
"savings" in my experience.

HTH
- Turtle
"Raj" <en****@yahoo.c om> wrote in message
news:d3******** *************** ***@posting.goo gle.com...

Thanks, in my case I cant do anything with the existing table, apart
from adding a column or two at the most since there are a lot of
people using the database in its original format, even that will be
tough as we may have to update all the users databases once we rollout
the new changes, so I guess the best idea in this case is to add a new
table with all the new data and a foreign-key to link into the main
table...am I right?? Thanks again,
Raj.

"MacDermott " <ma********@nos pam.com> wrote in message

news:<VR******* *********@newsr ead1.news.atl.e arthlink.net>.. .

It is rarely a good idea to add columns to a table -
especially not if this is something that happens repeatedly.

Consider normalizing your table design -
most likely moving some of your existing data into a new table.

For example, suppose you had a table of children and schools -
PK
Child's Name
Child's School
Then children begin changing schools, and you want a historical record.
You might think of a structure like this:
PK
Child's Name
Child's School 1
Child's Dates at School1
Child's School 2
Child's Dates at School2
Of course this will be problematic, because you'll have to keep adding
fields...

Instead, you could have 2 tables
PK
Child's Name
and
Foreign Key - matches PK of first table to identify child
School Name
School Start
School End

This will accommodate as many schools as needed.

HTH
- Turtle