Adding columns to a database table programmatically...

[Guest]

Hi,

I've been tasked to write a windows app that allows people to enter
transactions. For each transaction, there can be an unknown number of items,
and as a person enters an item, the program asks if there are more items. If
yes, a new set of input boxes are created dynamically. What I was wondering
is this - not knowing how many items are going to be entered, is there a way
that I can change the layout of a database table depending on the number of
items entered? After the initial creation of the database, with a set number
of item columns, is there a way for me, (ie. the program), to alter/change
the database layout?

Any help is deeply appreciated...

CDB

 

[Nicholas Paldino [.NET/C# MVP]]

CDB,

I don't think that this is a good idea. If you have a person that has
100 items, and one that has one, then you are going to have 999 columns on
the table that are going to go unused on the row where there is only one
item.

That's a lot of wasted space, if you have a single transaction with a
high number of items, and the rest do not.

Instead, why not have an order identifier in the row, and have each row
be a separate item in the order (as long as it has the same key). You will
find that it is easier to program against and process.

 

[zacks]

Hi,

I've been tasked to write a windows app that allows people to enter
transactions. For each transaction, there can be an unknown number of items,
and as a person enters an item, the program asks if there are more items. If
yes, a new set of input boxes are created dynamically. What I was wondering
is this - not knowing how many items are going to be entered, is there a way
that I can change the layout of a database table depending on the number of
items entered? After the initial creation of the database, with a set number
of item columns, is there a way for me, (ie. the program), to alter/change
the database layout?

Any help is deeply appreciated...

CDB

Nicholas made a good comment, but if you still want to programmically
alter a table's definition, it depends on the dabase providor. If
Microsoft SQL Server, all you need to do is issue an ALTER TABLE SQL
command.

 

[Philippe]

Nicholas made a good comment, but if you still want to programmically
alter a table's definition, it depends on the dabase providor. If
Microsoft SQL Server, all you need to do is issue an ALTER TABLE SQL
command.

That *would* be the way to do it, but frankly it would be a disastrous
way to do things from a DBA side of things.. Really..

Basic table with ID column, client/transaction column and item column.
The ID is invisible, and you can get any/all items per transaction nice
and simply.. It also allows for basic querying based on transaction *or*
item, if you're thinking long term.

but yah.. having a table that grows/shrinks columns based on arbitrary
item count is going to make your DBA cry.

P.

 

[Gregg Walker]

CDB,

Here are some other options to consider so (like Nicholas said) you can keep
the DBA happy (maybe ).

1. You could possibly store all items in a large varchar column as a
delimitted string (i.e. comma, etc.).
Of course this assumes that all potentially entered items would fit.

2. Some databases allow for the creation of multi-valued columns in a table.
I don't believe Sql Server
supports this yet. Multi-valued columns allow for 0-n items in a single
column where n is unknown at
design time. These have actually been around for quite a while in
databases like universe and pick.
Some relational vendors have recently added support for them to their
products (i.e. Informix, DB2).

3. Some databases allow for the creation of XML valued columns. Sql Server
2005 supports this column
type. If you wanted to go this route you can stuff whatever XML you
want in the column.

There are other ways of going about it (like Nicholas fine suggestion of
using one row per item in a
separate related table).

And of course you can always just alter the table by adding more columns.

HTH

 

[Guest]

For some reason, I didn't get Nicholas' reply, but, what you wrote really
helps. I don't know why I didn't think of setting up another table to accept
each item in its own row. Also, I like the idea of possibly making the items
comma seperated.

Thank you all for the help. If you could post what Nicholas wrote so I
could see that would be most helpful.

Thanks

CDB

 

[Nicholas Paldino [.NET/C# MVP]]

CDB,

Here is the link to my original response:

http://groups.google.com/group/micr...=Nicholas+Paldino+CDB&rnum=3#f847dd7c7b28fe39