I'm building an application that uses a table containing about 100
fields of data. Approximately half are ints; the other half are
mostly varchar, with a few enums and one text field thrown in. My
question is, is this number of fields poor programming? I could
conceiveably cut back the number of fields by adding some auxiliary
tables. After all, not every field is used in every query. Still,
that would add a lot of complexity to my application and I would like
to avoid it.

Any advice?

On Jan 18, 3:44 pm, firewood...@yahoo.com wrote:
> I'm building an application that uses a table containing about 100
> fields of data. Approximately half are ints; the other half are
> mostly varchar, with a few enums and one text field thrown in. My
> question is, is this number of fields poor programming? I could
> conceiveably cut back the number of fields by adding some auxiliary
> tables. After all, not every field is used in every query. Still,
> that would add a lot of complexity to my application and I would like
> to avoid it.
>
> Any advice?

There's no way to say if it's "poor programming" without knowlege of
what your application is.

If you've gone through the standard normalization analysis and this is
what you came up with, then so be it. I'd be more concerned about
hitting some arbitrary database limit. E.g., Sybase complains if a
single row might become too large to fit in a single 2k block.

On Fri, 18 Jan 2008 16:01:10 -0800 (PST), ThanksButNo
<no.no.thanks@gmail.com> wrote:

>On Jan 18, 3:44 pm, firewood...@yahoo.com wrote:
>> I'm building an application that uses a table containing about 100
>> fields of data. Approximately half are ints; the other half are
>> mostly varchar, with a few enums and one text field thrown in. My
>> question is, is this number of fields poor programming? I could
>> conceiveably cut back the number of fields by adding some auxiliary
>> tables. After all, not every field is used in every query. Still,
>> that would add a lot of complexity to my application and I would like
>> to avoid it.
>>
>> Any advice?
>
>There's no way to say if it's "poor programming" without knowlege of
>what your application is.
>
>If you've gone through the standard normalization analysis and this is
>what you came up with, then so be it. I'd be more concerned about
>hitting some arbitrary database limit. E.g., Sybase complains if a
>single row might become too large to fit in a single 2k block.

I haven't had any complaints from MySQL as yet, and the code seems to
run as fast as I need. It just seemed like an awful lot of fields,
and I wondered if it was unusual.

I haven't been too concerned with normalization, because breaking the
set of fields down into specialized tables would mean I would have to
sacrifice a lot of simplicity in the code. Still, every single SELECT
call to the db winds up delivering a lot of unnecessary data.

This is a website application that is geared to small businesses, so
there is little likelihood that the data sets will ever be gigantic.
I'm really just trying to understand the tradeoff between design
elegance and practicality in writing the code.

firewoodtim@yahoo.com wrote:
> On Fri, 18 Jan 2008 16:01:10 -0800 (PST), ThanksButNo
> <no.no.thanks@gmail.com> wrote:
>
>> On Jan 18, 3:44 pm, firewood...@yahoo.com wrote:
>>> I'm building an application that uses a table containing about 100
>>> fields of data. Approximately half are ints; the other half are
>>> mostly varchar, with a few enums and one text field thrown in. My
>>> question is, is this number of fields poor programming? I could
>>> conceiveably cut back the number of fields by adding some auxiliary
>>> tables. After all, not every field is used in every query. Still,
>>> that would add a lot of complexity to my application and I would like
>>> to avoid it.
>>>
>>> Any advice?
>> There's no way to say if it's "poor programming" without knowlege of
>> what your application is.
>>
>> If you've gone through the standard normalization analysis and this is
>> what you came up with, then so be it. I'd be more concerned about
>> hitting some arbitrary database limit. E.g., Sybase complains if a
>> single row might become too large to fit in a single 2k block.
>
> I haven't had any complaints from MySQL as yet, and the code seems to
> run as fast as I need. It just seemed like an awful lot of fields,
> and I wondered if it was unusual.
>
> I haven't been too concerned with normalization, because breaking the
> set of fields down into specialized tables would mean I would have to
> sacrifice a lot of simplicity in the code. Still, every single SELECT
> call to the db winds up delivering a lot of unnecessary data.
>
> This is a website application that is geared to small businesses, so
> there is little likelihood that the data sets will ever be gigantic.
> I'm really just trying to understand the tradeoff between design
> elegance and practicality in writing the code.

I have learned over the past 25+ years in IT you NEVER say anything like
"There is little likelihood that the data sets will ever be gigantic"
--- because it almost always does....

On Fri, 18 Jan 2008 19:31:32 -0500, firewoodtim@yahoo.com
wrote:

> I haven't been too concerned with normalization, because breaking the
> set of fields down into specialized tables would mean I would have to
> sacrifice a lot of simplicity in the code.

You may regret that later. Normalization not only renders
tables with fewer columns, but also a good protection
against inconsistency. Your code could get another kind of
complexity to protect against that.
--
( Kees
)
c[_] Power corrupts, but intermittent power corrupts absolutely (Jeff Bell, asr) (#90)

firewoodtim@yahoo.com wrote:
> On Sat, 19 Jan 2008 02:18:16 +0100, "Rik Wasmus"
> <luiheidsgoeroe@hotmail.com> wrote:
>
> "snip"
>>
>>Eehm. Why does it deliver unnecessary data? You control the queries,
>>how come the queries don't ask for just the data they need?
>>
>
> Well, I read somewhere that 'SELECT *' was a lot faster than naming a
> bunch of fields that are specific for the exact task for the moment.

Wherever it was you read that, make sure you don't go there again!

On Sat, 19 Jan 2008 16:46:18 -0000, "Paul Lautman"
<paul.lautman@btinternet.com> wrote:

>firewoodtim@yahoo.com wrote:
>> On Sat, 19 Jan 2008 02:18:16 +0100, "Rik Wasmus"
>> <luiheidsgoeroe@hotmail.com> wrote:
>>
>> "snip"
>>>
>>>Eehm. Why does it deliver unnecessary data? You control the queries,
>>>how come the queries don't ask for just the data they need?
>>>
>>
>> Well, I read somewhere that 'SELECT *' was a lot faster than naming a
>> bunch of fields that are specific for the exact task for the moment.
>
>Wherever it was you read that, make sure you don't go there again!
>
Interesting. Is there some reference for how queries for MySQL are
best structured?

On Sat, 19 Jan 2008 16:38:34 -0500, firewoodtim@yahoo.com
wrote:

>On Sat, 19 Jan 2008 16:46:18 -0000, "Paul Lautman"
><paul.lautman@btinternet.com> wrote:
>
>>firewoodtim@yahoo.com wrote:
>>> On Sat, 19 Jan 2008 02:18:16 +0100, "Rik Wasmus"
>>> <luiheidsgoeroe@hotmail.com> wrote:
>>>
>>> "snip"
>>>>
>>>>Eehm. Why does it deliver unnecessary data? You control the queries,
>>>>how come the queries don't ask for just the data they need?
>>>>
>>>
>>> Well, I read somewhere that 'SELECT *' was a lot faster than naming a
>>> bunch of fields that are specific for the exact task for the moment.
>>
>>Wherever it was you read that, make sure you don't go there again!
>>
>Interesting. Is there some reference for how queries for MySQL are
>best structured?

http://dev.mysql.com/doc/refman/5.0/en/query-speed.html
(took me 2 minutes to find, and I consider myself slow)
--
( Kees
)
c[_] Seen on a sign in a mechanic's garage:
Labor $10.00/hr.
If you watch $15.00/hr.
If you help $25.00/hr. (#524)

On Jan 19, 8:46 am, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> firewood...@yahoo.com wrote:
> > On Sat, 19 Jan 2008 02:18:16 +0100, "Rik Wasmus"
> > <luiheidsgoe...@hotmail.com> wrote:
>
> > "snip"
>
> >>Eehm. Why does it deliver unnecessary data? You control the queries,
> >>how come the queries don't ask for just the data they need?
>
> > Well, I read somewhere that 'SELECT *' was a lot faster than naming a
> > bunch of fields that are specific for the exact task for the moment.
>
> Wherever it was you read that, make sure you don't go there again!

Maybe it meant that 'SELECT *' is faster *to*code* - - - ??

I mean, look, it must take HOURS to type out each of a hundred
columns, then go back and fix typos and what-not --

Now, take; 'SELECT *'

BOOM, you're done! Time for a beer break!

;-)

ThanksButNo wrote:
> On Jan 19, 8:46 am, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>> firewood...@yahoo.com wrote:
>>> On Sat, 19 Jan 2008 02:18:16 +0100, "Rik Wasmus"
>>> <luiheidsgoe...@hotmail.com> wrote:
>>> "snip"
>>>> Eehm. Why does it deliver unnecessary data? You control the queries,
>>>> how come the queries don't ask for just the data they need?
>>> Well, I read somewhere that 'SELECT *' was a lot faster than naming a
>>> bunch of fields that are specific for the exact task for the moment.
>> Wherever it was you read that, make sure you don't go there again!
>
> Maybe it meant that 'SELECT *' is faster *to*code* - - - ??
>
> I mean, look, it must take HOURS to type out each of a hundred
> columns, then go back and fix typos and what-not --
>
> Now, take; 'SELECT *'
>
> BOOM, you're done! Time for a beer break!
>
> ;-)
>

BOOM. Your program just blew up because someone added a 10MB blob to
each row in the table! You're fired!

Lazy coding is seldom *good* coding.

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