Professional Web Applications Themes

many (empty) fields or many entries? - MySQL

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

  1. #1

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

    Lo'oris Guest

  2. #2

    Default Re: many (empty) fields or many entries?

    On 13 May 2007 18:14:42 -0700, Lo'oris <com> wrote:
     

    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.
     

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

  3. #3

    Default Re: many (empty) fields or many entries?

    On May 14, 8:41 am, subtenante <com> wrote: 


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

    >
    > The more the better ! Give as much as is safe.[/ref]

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

    strawberry Guest

  4. #4

    Default Re: many (empty) fields or many entries?

    On 14 Mag, 09:41, subtenante <com> wrote: 
    >
    > The more the better ! Give as much as is safe.[/ref]

    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 <com> wrote: 

    yes it's definitly EAV, i didn't knew the term but i knew the
    concept :)

    Lo'oris Guest

  5. #5

    Default Re: many (empty) fields or many entries?

    On 14 May 2007 02:16:31 -0700, strawberry <com> wrote:
     

    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.
    subtenante Guest

  6. #6

    Default Re: many (empty) fields or many entries?

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

    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 ! :}
    subtenante Guest

  7. #7

    Default Re: many (empty) fields or many entries?

    On Mon, 14 May 2007 19:15:11 +0800, subtenante
    <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.
    subtenante Guest

  8. #8

    Default Re: many (empty) fields or many entries?

    On 14 Mag, 13:15, subtenante <com> wrote: 

    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.
     

    ooh you're right.

    On 15 Mag, 01:14, Jerry Stuckle <net> wrote: 

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

    Lo'oris Guest

  9. #9

    Default Re: many (empty) fields or many entries?

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

  10. #10

    Default Re: many (empty) fields or many entries?

    On 14 Mag, 14:05, subtenante <com> wrote: 

    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.

    Lo'oris Guest

  11. #11

    Default Re: many (empty) fields or many entries?

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

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

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

  12. #12

    Default Re: many (empty) fields or many entries?

    On 14 May, 13:05, subtenante <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.[/ref]

    Quite useful for functions such as COALESCE though.

    Captain Guest

  13. #13

    Default Re: many (empty) fields or many entries?

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

    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.
    subtenante Guest

  14. #14

    Default Re: many (empty) fields or many entries?

    On 14 Mag, 14:54, subtenante <com> wrote: 

    yeah :)
     
    [...] 

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

    Lo'oris Guest

  15. #15

    Default Re: many (empty) fields or many entries?

    subtenante wrote: 
    >
    > 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.[/ref]

    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.


    Paul Guest

  16. #16

    Default Re: many (empty) fields or many entries?

    >case 1) a table with many fields for storing some data, usually most 

    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, Pepsi, rat poison, condoms, or beer? It depends on
    whether you're thirsty, rat-infested, horny, or sober.


    Gordon Guest

  17. #17

    Default Re: many (empty) fields or many entries?

    Lo'oris wrote: 
    >> The more the better ! Give as much as is safe.[/ref]
    >
    > 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 <com> wrote: 
    >
    > yes it's definitly EAV, i didn't knew the term but i knew the
    > concept :)
    >[/ref]

    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.
    net
    ==================
    Jerry Guest

  18. #18

    Default Re: many (empty) fields or many entries?

    On Mon, 14 May 2007 22:02:24 +0100, "Paul Lautman"
    <com> wrote:
     

    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.
     

    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.
     [/ref]

    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.
    subtenante Guest

  19. #19

    Default Re: many (empty) fields or many entries?

    On May 14, 10:54 pm, org (Gordon Burditt) wrote: 

    >
    > 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, Pepsi, rat poison, condoms, or beer? It depends on
    > whether you're thirsty, rat-infested, horny, or sober.[/ref]


    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.

    strawberry Guest

  20. #20

    Default Re: many (empty) fields or many entries?

    On 15 May 2007 01:41:26 -0700, strawberry <com> wrote:
     

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

Similar Threads

  1. Empty Fields
    By HMOKeefe in forum Coldfusion - Getting Started
    Replies: 12
    Last Post: October 14th, 03:43 PM
  2. empty fields in form
    By jdearie in forum Macromedia ColdFusion
    Replies: 4
    Last Post: August 15th, 10:15 PM
  3. Previous Entries Appear for Text Fields
    By DRDuquette in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: June 5th, 01:15 AM
  4. Replies: 2
    Last Post: August 12th, 02:52 PM
  5. Preventing duplicate entries in text fields
    By Jim in forum Microsoft Access
    Replies: 2
    Last Post: September 9th, 02:40 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139