many (empty) fields or many entries?

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.

which is better, in general?

do you maybe need more detailed infos?

On 13 May 2007 18:14:42 -0700, Lo'oris <looris@gmail.com> 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.
>
>which is better, in general?

I think both mean you have a design problem. What about splitting your
table in different ones ?

Sometimes, you need to do ugly things though. Case 1) seems to be more
normalized, but it depends on what your fields are.

>do you maybe need more detailed infos?

The more the better ! Give as much as is safe.

On May 14, 8:41 am, subtenante <zzsubtenant...@gmail.com> wrote:
> On 13 May 2007 18:14:42 -0700, Lo'oris <loo...@gmail.com> 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.
>
> >which is better, in general?
>
> I think both mean you have a design problem. What about splitting your
> table in different ones ?
>
> Sometimes, you need to do ugly things though. Case 1) seems to be more
> normalized, but it depends on what your fields are.
>
> >do you maybe need more detailed infos?
>
> The more the better ! Give as much as is safe.

case 2 sounds like an EAV approach - and, although used successfully
in some environments (see http://en.wikipedia.org/wiki/Entity-...te-Value_model),
it's usually frowned upon in this NG.

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 :)

On 14 May 2007 02:16:31 -0700, strawberry <zac.carey@gmail.com> wrote:

>case 2 sounds like an EAV approach - and, although used successfully
>in some environments (see http://en.wikipedia.org/wiki/Entity-...te-Value_model),
>it's usually frowned upon in this NG.

I had to use it for a part of a project, in the worst case : when the
attributes have almost all sorts of types (integer, floats, dates,
blobs, ...). For cases where you have only numerical attributes for
example, it can work smoothly. But as soon as you have to store
integers as strings, the operations and searches on the objects become
hell.

On 14 May 2007 03:56:24 -0700, Lo'oris <looris@gmail.com> wrote:

>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?

15 fields is not much. Even 30 fields is not that much, and according
to your description, i guess the input will be done "manually"
(somebody has to fill a form somewhere, there is no complete
automation). So the size of your database, I think, is not a big
concern.

EAV, as it is called (didn't know it me neither !), has the
disadvantage to make searches more difficult. If you want to check for
all the matches in which player Albert Einstein scored at least once,
it will be alright. But as soon as you want to have more difficult
operations, it might get tricky (and you will begin doing smart
things... and you'll soon discover than you're not as smart as you
thought in the first place).

I don't have much experience but if my voice counts, let the NULLs be,
they don't do much harm and you will have a far easier time when it
comes to look for the players that have scored between 1 and 3 times
while in position of defender and being entered in the game after the
45th minute of game and never got more than 5 penalties against them.

And if you're sure you never will have this kind of SELECTs, thing
twice ! :}

On Mon, 14 May 2007 19:15:11 +0800, subtenante
<zzsubtenantezz@gmail.com> wrote:

>I don't have much experience but if my voice counts, let the NULLs be

Thinking twice myself, avoid the NULLs and put a default value instead
(0 in general, I guess). NULL imply you are in a three-valued logic
and the results of queries might not always be what they obviously
should be.

On 14 Mag, 13:15, subtenante <zzsubtenant...@gmail.com> wrote:
> to your description, i guess the input will be done "manually" (somebody has to fill a form somewhere,
> there is no complete automation). So the size of your database, I think, is not a big concern.

actually no, it will be completely automated (my mistake, i didn't
specify i meant it was a WEB game ;)

but size is no issue anyway, as of now. this may be an issue later,
but i can't think of everything before having a finished product: size
and other optimizations may come later.

> EAV, as it is called (didn't know it me neither !), has the disadvantage to make searches more difficult.

ooh you're right.

On 15 Mag, 01:14, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Search for "database normalization". It should give you some guidelines
> on what's the best for your case.

i will, thanks for the tip! (i searched "mysql optimization" before,
but found only crap things in the first two pages)

On 14 Mag, 13:46, subtenante <zzsubtenant...@gmail.com> wrote:
> Thinking twice myself, avoid the NULLs and put a default value instead
> (0 in general, I guess). NULL imply you are in a three-valued logic
> and the results of queries might not always be what they obviously
> should be.

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 :)

On 14 May 2007 04:58:47 -0700, Lo'oris <looris@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.

On 14 Mag, 14:05, subtenante <zzsubtenant...@gmail.com> wrote:
> 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.

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

in the and, i think I'll follow the non-EAV approach, with two tables.

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.
I'm guessing again. well, this have to be correct.