Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57

    Videos, video content

    Hi,

    I have two questions.

    1. Each video has information about its content (they are poker instruction videos, so it's fields like 'game_type', 'stakes', etc.) -- should I use two tables, or one?

    One
    video: id, name, content_game_type, content_stakes, etc. (I prefix with 'content' because a video in itself does not have 'stakes', for example.)

    Two
    video: id, name
    video_content: video_id, game_type, stakes, etc.

    2. These fields (game, game_type, stakes, table_size, tables) all have relatively static value sets, as follows:

    Game: LHE, NLHE, Omaha 8/b, Omaha H/L, Other
    Type: Cash, MTT, SNG, Special
    Stakes: Lower (0.05/0.10 - 0.25/0.50), Middle(1/2 3/6), Higher (5/10+)
    Size: Long handed, Heads up, Short handed
    Tables: 1, 2, 3, 4+

    Should I use MySQL's ENUM type, or create lookup tables for each of these fields?

    Thanks,

    _da.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't know about MySQL's ENUM type so can't comment on its benfits. I would probably use lookup tabes anyway since these are standard and can be ported.


    Whether or not you need one or more tables depends on the nature of the data. Can a video contain information on more than one combination of all these fields. For example, an NLHE MTT video might cover tables of 3 and 4+?

  3. #3
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    Quote Originally Posted by pootle flump
    I don't know about MySQL's ENUM type so can't comment on its benfits. I would probably use lookup tabes anyway since these are standard and can be ported.


    Whether or not you need one or more tables depends on the nature of the data. Can a video contain information on more than one combination of all these fields. For example, an NLHE MTT video might cover tables of 3 and 4+?
    Hi,

    1. True. It just seemed excessive to have so many small tables. Maybe I'm looking at it the wrong way?

    2. Nope, just one set of fields per video. Use one table?

    Thanks,

    _da.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by darkangel
    1. True. It just seemed excessive to have so many small tables. Maybe I'm looking at it the wrong way?
    Tables represent entities. Some entities are information rich (lots of columns) some are information poor. Some can have, in theory, an infintite of instances (a row in the table is an instance of the entity) (order details for example), some have a very small and, broadly, fixed number of instances (your little lookup table here for example). But they are all entities. You generate your entities through a process of normalisation which is driven by dependencies. Whether or not this results in lots of little tables or several huge ones is irrelevent.

    Quote Originally Posted by darkangel
    2. Nope, just one set of fields per video. Use one table?
    Tell you what - can you justify Two?

  5. #5
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    Quote Originally Posted by pootle flump
    Tables represent entities. Some entities are information rich (lots of columns) some are information poor. Some can have, in theory, an infintite of instances (a row in the table is an instance of the entity) (order details for example), some have a very small and, broadly, fixed number of instances (your little lookup table here for example). But they are all entities. You generate your entities through a process of normalisation which is driven by dependencies. Whether or not this results in lots of little tables or several huge ones is irrelevent.
    Thanks, that makes me feel a lot better about it.

    Quote Originally Posted by pootle flump
    Tell you what - can you justify Two?
    It would only be for semantic reasons. i.e. Do these fields represent a separate entity?

    _da.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by darkangel
    It would only be for semantic reasons. i.e. do these fields represent a separate entity?
    And they don't: One-to-one relationship.

    BTW - the stuff about the lookups. There is a counter argument that since these things consist of nothing more than lists of acceptable values (i.e. there are no non-key attributes in there) they do not represent entities in themselves. They are there to support correctness rather than integrity. You could instead just use check constraints in your table DDL.
    The advantage of this - less tables. The disadvantages - adding a new game, for example, requires a DDL change; your app needs to maintain a seperate list for users to select from.

  7. #7
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    Quote Originally Posted by pootle flump
    And they don't: One-to-one relationship.
    Not sure what you mean here -- do you mean between the video entity and each of the 'content' entities? (i.e. games, game_types, stakes, etc.)

    Quote Originally Posted by pootle flump
    BTW - the stuff about the lookups. There is a counter argument that since these things consist of nothing more than lists of acceptable values (i.e. there are no non-key attributes in there) they do not represent entities in themselves. They are there to support correctness rather than integrity. You could instead just use check constraints in your table DDL.
    The advantage of this - less tables. The disadvantages - adding a new game, for example, requires a DDL change; your app needs to maintain a seperate list for users to select from.
    Yeah, it's pretty much the same with ENUMs -- any changes you wish to make require changes to the DDL.

    _da.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by darkangel
    Not sure what you mean here -- do you mean between the video entity and each of the 'content' entities? (i.e. games, game_types, stakes, etc.)
    I was answering your question:
    Do these fields represent a seperate entity? (BTW - it's columns or attributes not fields if you are being fussy - I usually am )
    They don't represent seperate entities so one table.

  9. #9
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    Quote Originally Posted by pootle flump
    I was answering your question:
    Do these fields represent a seperate entity? (BTW - it's columns or attributes not fields if you are being fussy - I usually am )
    They don't represent seperate entities so one table.
    Thanks so much for your help, pootle flump.

    _da.

Posting Permissions

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