many (empty) fields or many entries?

On 14 May 2007 05:29:59 -0700, Lo'oris <looris@gmail.com> wrote:

>oh, stupid NULLs. i guess i shouldn't make assumptions.

There is no problem with assumptions, provided you verify them
carefully !

>two, because, even if they ideally should be only a single table, the
>first one will be used MUCH more than the second one, so having them
>separate should increase speed when accessing the first one, i guess.

Likely, especially if you have only fixed-length types in the stats
table. But it depends also : you might think that 15 stats values are
a lot, but if they all are TINYINTs, they don't take much place, and
if you have a few VARCHAR(50) for the names and description of a
player, the 15 stats are finally not that much (they take as much
space as the name if the name is Albert Einstein).

I don't know exactly what will be in your player_per_match table, but
i guess you will have strings. Do the players choose their own player
name ? Do they change every time ?

By the way, why not a 3 table design : player / match / stats, stats
also playing the role of a many to many link between player and match
? Do you really need that player_per_match table, without the stats in
it ?

On 14 May, 13:05, subtenante <zzsubtenant...@gmail.com> wrote:
> On 14 May 2007 04:58:47 -0700, Lo'oris <loo...@gmail.com> wrote:
>
> >hey nice idea. but. i kinda expected NULLs improved performance over
> >values such as 0, don't they?
> >well, having a slightly worst performance is anyway better than having
> >bugs you can't find, isn't it?
> >i'll consider this, thanks :)
>
> I'm not sure it makes better performance to have NULLs. They add 1 bit
> to every field that can take a NULL value, and they don't make
> searches faster as far as I know.

Quite useful for functions such as COALESCE though.

On 14 May 2007 06:38:16 -0700, Captain Paralytic
<paul_lautman@yahoo.com> wrote:

>Quite useful for functions such as COALESCE though.

I would rather say the reverse : COALESCE is quite useful when you
have NULLs, but it doesn't mean you must use them just because they
have handy functions.

On 14 Mag, 14:54, subtenante <zzsubtenant...@gmail.com> wrote:
>
> There is no problem with assumptions, provided you verify them
> carefully !

yeah :)

> table. But it depends also : you might think that 15 stats values are
[...]
> ? Do you really need that player_per_match table, without the stats in

you made a point!

first table (which i need even before having the stats) uses a lot of
INTs, while second table only uses TINYINTs, so first one is so much
bigger anyway!

i'll stick with single-table approach, then :)

subtenante wrote:
> On 14 May 2007 06:38:16 -0700, Captain Paralytic
> <paul_lautman@yahoo.com> wrote:
>
>> Quite useful for functions such as COALESCE though.
>
> I would rather say the reverse : COALESCE is quite useful when you
> have NULLs, but it doesn't mean you must use them just because they
> have handy functions.

I'm amazed that you would say it that way round. I have only ever had one
application where COALESCE was useful. NULLs however are usefule for many
things as well as many functions (IFNULL(), IF(), ...)

When it comes down to it you choose the attributes of a field based on what
it needs to represent.

But to sugges that just because a field is NULL, COALESCE becomes quite
useful is rather unbelieveble.

>case 1) a table with many fields for storing some data, usually most
>of them will be NULL because they are (and will ever be) unsused
>
>case 2) a table with just 3 fields, but many entries, one for each
>field i'd have used (not null) in case 1.

This is problematical if the types of the fields aren't all the
same.

Consider also: case 3) a bunch of tables with 2 fields, the primary
key, and the value of some column (not null). From case (1), you
have a fixed list of these attributes. So use one table for each.
If a value is NULL, leave out an entry in that table.

This potentially costs disk space for storing repetitions of the
primary key (and indexes), but you don't store anything for NULL
values.

Incidentally, you might consider whether values of NULL or 0 or
something else is more appropriate. NULL might correspond to the
stats of a game which hasn't been played yet; 0 might correspond
to the stats of a game that is completed but without the player,
say, batting in any runs. The difference might be significant if
you want to use avg(rbi.score) to compute overall stats, where
values of 0 (completed games) count in the average and values of
NULL (unplayed-yet games) shouldn't.


>which is better, in general?

Which is better, Pepsi, rat poison, condoms, or beer? It depends on
whether you're thirsty, rat-infested, horny, or sober.

Lo'oris wrote:
> On 14 Mag, 09:41, subtenante <zzsubtenant...@gmail.com> wrote:
>>> do you maybe need more detailed infos?
>> The more the better ! Give as much as is safe.
>
> ok.
>
> i already have first table. it contains an entry for each player
> playing in a match (we're talking about a team game). it has some
> fields (more or less 6), and all of them are used.
>
> now i have to add stats (many. such as 15, i think, i didn't count
> them yet) about what happened in the match. such as how many points a
> player scored, etc.
> If i add these fields into the table i already have, **most** of them
> will be NULL because simply that player didn't do everything.
> I am concerned about having so many unused fields, maybe they slow
> everything down, or take too many space?
>
> So, i either add those fields in that table, or (didn't thought about
> that) i create a new table with the empty fields (so not affecting the
> performance of the first table when i need only his fields), or i
> create a new table with EAV approach (actually it would not have empty
> fields, but it would be redundant anyway, listing many times the same
> player ids and attribute name).
>
> On 14 Mag, 11:16, strawberry <zac.ca...@gmail.com> wrote:
>> case 2 sounds like an EAV approach - and, although used successfully
>> in some environments (seehttp://en.wikipedia.org/wiki/Entity-Attribute-Value_model),
>> it's usually frowned upon in this NG.
>
> yes it's definitly EAV, i didn't knew the term but i knew the
> concept :)
>

Search for "database normalization". It should give you some guidelines
on what's the best for your case.


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

On Mon, 14 May 2007 22:02:24 +0100, "Paul Lautman"
<paul.lautman@btinternet.com> wrote:

>I'm amazed that you would say it that way round. I have only ever had one
>application where COALESCE was useful. NULLs however are usefule for many
>things as well as many functions (IFNULL(), IF(), ...)

Yes, that's why i say COALESCE is useful only when you have NULLs !
You wouldn't think to put NULLs in your field definition only to be
able to use COALESCE.

> But to sugges that just because a field is NULL, COALESCE becomes quite
> useful is rather unbelieveble.

That wasn't my intention ! What i wanted to suggest is that COALESCE
is never useful on NOT NULL fields, and you might not make a field
possibly NULL just because of COALESCE.

> On 14 May 2007 06:38:16 -0700, Captain Paralytic
> <paul_lautman@yahoo.com> wrote:
>
>> Quite useful for functions such as COALESCE though.

Reading your sentence, i understood it as "NULLs are useful for
COALESCE function", but in my mind it is the other way : COALESCE (and
IFNULL, and all the others crafted for fields being possibly NULL) are
only a help when you need to use NULLs, but not an argument on why you
should or should not define a field as NULL or NOT NULL.

I think we agree on the principal anyways. Sorry if i misunderstood
your first message.

On May 14, 10:54 pm, gordonb.xi...@burditt.org (Gordon Burditt) wrote:
> >case 1) a table with many fields for storing some data, usually most
> >of them will be NULL because they are (and will ever be) unsused
>
> >case 2) a table with just 3 fields, but many entries, one for each
> >field i'd have used (not null) in case 1.
>
> This is problematical if the types of the fields aren't all the
> same.
>
> Consider also: case 3) a bunch of tables with 2 fields, the primary
> key, and the value of some column (not null). From case (1), you
> have a fixed list of these attributes. So use one table for each.
> If a value is NULL, leave out an entry in that table.
>
> This potentially costs disk space for storing repetitions of the
> primary key (and indexes), but you don't store anything for NULL
> values.
>
> Incidentally, you might consider whether values of NULL or 0 or
> something else is more appropriate. NULL might correspond to the
> stats of a game which hasn't been played yet; 0 might correspond
> to the stats of a game that is completed but without the player,
> say, batting in any runs. The difference might be significant if
> you want to use avg(rbi.score) to compute overall stats, where
> values of 0 (completed games) count in the average and values of
> NULL (unplayed-yet games) shouldn't.
>
> >which is better, in general?
>
> Which is better, Pepsi, rat poison, condoms, or beer? It depends on
> whether you're thirsty, rat-infested, horny, or sober.


Actually, as beer seems to remove the both the capacity and need to
either engage with or care about any of the above, I'd have to go with
beer.

On 15 May 2007 01:41:26 -0700, strawberry <zac.carey@gmail.com> wrote:

>Actually, as beer seems to remove the both the capacity and need to
>either engage with or care about any of the above, I'd have to go with
>beer.

Well, rat poison does, too... Even permanently.