Is there a limit to the number of columns a table can have ?

On 2011-02-28 06:12, richard wrote:
See for example:

http://dev.mysql.com/doc/refman/5.0/...unt-limit.html



/Lennart

On Sun, 27 Feb 2011 22:12:04 -0700, richard wrote:

found the answer at mysql.com.

In article <1i5bcl39yj1m$.dlg (AT) evanplatt (DOT) sux>, richard <member (AT) newsguy (DOT) com> wrote:
Asking this question indicates a (probably) ill-thought-out table design. Why
do you believe that you *need* to have so many columns in one table that you
might encounter some limit?

Doug Miller wrote:
It may have been pure curiousity.

And I was interested in the answer anyway.

One can always dream up some example where the need could exist..

I am sure you have more than 4096 unique things about you that
could go into some national security database..

Select id, name, from poulation where
.....current_fingernail_color='Amazonian Purple"..

According to some physicists, the world is a database that is just
large enough to describe itself accurately.

how large your SQL table is depends on how much of it you are attempting
to model :-)

The Natural Philosopher:

[max. 4096 columns]
Sure. But if you meet someone on a MySql forum or newsgroup, who asks
about the maximum number of columns, would you more likely:
a) expect him to actually have a Real World need for so many columns
or
b) suspect that something might be wrong about his model
or
c) believe that he is merely posting his dreams

?


--
Erick

Erick T. Barkhuis wrote:
might be simple curiosity and a desire to learn..

I've run into table limits, and simple BLOB limits*, and memory limits,
before .It was interesting to note that blobs apart, there is a size
limit on a row as well, both in terms of fields and in terms of total
data size.

So whilst noting that any real world application is unlikely to exceed
those limits, its still nice to know where they are.


*couldn't understand why I was only getting half a picture..:-)

On Mon, 28 Feb 2011 11:58:50 GMT, Doug Miller wrote:

Let's say I wanted a table with 365 columns. One for each day of the year.
Or maybe I want a table where column 1 is the state name, then the
remaining columns is for each city and county. If the column limit was only
two hundred, I'd have to reinvent my table wouldn't I?

On 2/28/2011 11:44 AM, richard wrote:
Nope, because your database design is bad.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

richard wrote:
OK.


No. because you have a one to many relationship, you would definitely
want three tables. What you have there is a classic tree sturcure/

Root is Country - say USA
next is a table of states each with a unique ID and name that may have a
field to indicate which country they are in.

then comes a series of counties, whose name may not be unique, but each
one has a unique ID and a 'state' field that indicates which state they
belong to....and so on down to town, street, and house number and
anything else inside the actual house you might want to detail..like
people. A person - one or many, may live in a house, on a street in a
town, none of which needs to have more than a link to indicate the
relationship. Certainly you wouldn't want to have a field in a database
of all US citizens telling us repeatedly which state they were in since
if you know the street you know the town, if you know the town, you know
the county if you know the county, you know the state..


No, you wouldn't invent that one in the first place!