Results 1 to 4 of 4

Thread: too many fields

  1. #1
    Join Date
    Nov 2002
    Posts
    2

    too many fields

    How many fields is too many??

    Let's say you had a table with 400 fields and 300 of them were three fields that repeated a hundered times.

    Ex.

    tv_channels
    -----------------------------------------------------------------------
    channel_num PK
    channel_name
    channel_type
    8am_showtitle
    8am_showrating
    8am_showdesc
    8am_showtitle
    8am_showrating
    9am_showdesc
    9am_showtitle
    9am_showrating
    10am_showdesc
    10am_showtitle
    10am_showrating

    Wouldn't it be better to break it down like this.

    tv_channels
    -----------------------------------------------------------------------
    channel_num PK
    channel_name
    channel_type


    showtimes
    ---------------------------------------------
    showtimes_time PK
    showtimes_channel PK
    showtimes_showdesc
    showtimes_showtitle
    showtimes_showrating

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there's a technical term used by data modelling experts to describe that decision -- it's a no-brainer


    rudy

  3. #3
    Join Date
    Nov 2002
    Posts
    2

    here is the real problem

    I know that was a give me, but here is the problem.

    I now want to query this and return a record like what would be returned by querying that horrible example of a table. I'm asking how i would query by the channel and get a recordset with the time of the show in the header of each set of showtimes?

    Originally posted by r937
    there's a technical term used by data modelling experts to describe that decision -- it's a no-brainer


    rudy

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: here is the real problem

    Originally posted by weasal
    I know that was a give me, but here is the problem.

    I now want to query this and return a record like what would be returned by querying that horrible example of a table. I'm asking how i would query by the channel and get a recordset with the time of the show in the header of each set of showtimes?
    This can be done by creating a view, and using a CASE statement, or similar (Oracle has a "DECODE" statement that you could use).

    CREATE VIEW tv_channels_showtimes AS
    SELECT ch.channel_num,
    ch.channel_name,
    ch.channel_type,
    MAX( CASE WHEN ti.showtimes_time = '8am' THEN ti.showtimes_showtitle END ) 8am_showtitle,
    MAX( CASE WHEN ti.showtimes_time = '8am' THEN ti.showtimes_showdesc END ) 8am_showdesc,
    MAX( CASE WHEN ti.showtimes_time = '8am' THEN ti.showtimes_showrating END ) 8am_showrating,

    ... repeat ad nauseam for other times

    FROM tv_channels ch
    , showtimes ti
    WHERE ti.channel_num = ch.channel_num
    GROUP BY ch.channel_num,
    ch.channel_name,
    ch.channel_type;

    Ir's not pretty, but it works.

Posting Permissions

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