Adding 100 fields 1

[cabernet]

A client has a need for a new section that wiil be part of its CMS end
It calls for adding about 100+ fields to an existing table
within a DB serving an existing site
that table will not be often utilized
however will it be functional?
can it be done?

 

[tarn]

If your asking is there a limit to how many columns you can have in a table? then I can not see anything that indicates a limit in the documentation, so we can assume that there should not be a problem adding 100+ columns to a table.

Obviously there could be some degredation on query times (compared to how the table looks now) depending on how the queries are structured.


I hope that helps.

Good Luck,
Laurie.

 

[sleipnir214]

There is a difference between if it can be done and if it should be done.

You don't specify what these 100 additional columns or for, but are you sure that it is necessary to include them? Perhaps it would be better to use a related table and store the data "vertically" rather than "horizontally"?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[tarn]

Good answer sleipnir214, you get a star for the obvious.

L.

 

[cabernet]

thanks sleipnir214
I shall look for a related table
which indeed makes sense

 

[csniffer]

Cabernet, Just a bit of an observation on the last comment, you are already in a forum of a database that offers these capabilities.

To err is human, to completely mess up takes a computer.

 

[cabernet]

csniffer
I will have to dev a feeling for lookup table

your signature:
in the same range
I used to sign:
"Errare humanun est et diabolicum persevere"

 

[csniffer]

sorry dont know what u r talking about

To err is human, to completely mess up takes a computer.

 

[cabernet]

I was actually looking for a good definition of lookup table
it boils down to the facts that one can grab for example ID
from a table, another data form another table etc... all done within the same query

 

[sleipnir214]

Assume you have a table which consists of an ID and some values.

Assume you have a related table that holds muliple values that relate to the first table.

The first table will look something like:

create table foo
(
fID int unsigned auto_increment primary key,
some_value varchar(25)
)

The second table will look something like:
create table bar (
{
bID int unsigned auto_increment primary key,
foo_ID int unsigned,
addl_value varchar(10)
)


To fetch a record from foo as well as all related records from bar, something like:

SELECT * from foo, bar WHERE fID = foo_ID AND foo_ID = <somevalue>

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[cabernet]

thanks sleipnir214
looks exactly like what the doctor recomends
will start implementation phase
regards

 

[sleipnir214]

cabernet:
No prob.


tarn:
The reason I caught the &quot;100 fields&quot; thing so quickly is that I once had a web programmer create a database table with fields named &quot;field01&quot;, &quot;field02&quot;, &quot;field03&quot;, etc., out to &quot;field35&quot;.

It was a nightmare to maintain, particularly since this was on M$ SQL Server 6.5, which has a very small limit on how many bytes could be stored in a table row.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[cabernet]

Reporting:
Done all works fine
With 150 var debug sessions are fun!
However the trick at least for correct spelling is to use
Copy and paste of DB cols to set correctly array and query
It guaranties consistency in doing well or in mistake but really helps

Performance are not slowing down
Processing from server to user is: A OK
The slight drawback (very slight) is the needed time to load the long end user CMS management form
Again no problems
Thanks
So 150 var are not an obstacle