Professional Web Applications Themes

newbie needs a pointer - MySQL

Hi all, Although I am new to MySQL its query capabilities intrigue me. At this moment I am stuck though and I am hoping that someone can give me a pointer on how to solve my problem. Basically I am trying to figure out what prize is attached to a lottery ticket (if any). First of all I tried to do some normalization (bear with me if it is not perfect). I came up with the following tables: The ticket entity with the associated draw id: +-----------+---------+ | ticket_id | draw_id | +-----------+---------+ | 1 | 1206 | | 2 ...

  1. #1

    Default newbie needs a pointer

    Hi all,

    Although I am new to MySQL its query capabilities intrigue me. At this
    moment I am stuck though and I am hoping that someone can give me a
    pointer on how to solve my problem.

    Basically I am trying to figure out what prize is attached to a lottery
    ticket (if any).

    First of all I tried to do some normalization (bear with me if it is not
    perfect). I came up with the following tables:

    The ticket entity with the associated draw id:

    +-----------+---------+
    | ticket_id | draw_id |
    +-----------+---------+
    | 1 | 1206 |
    | 2 | 1207 |
    +-----------+---------+

    The draw entity with the draw date, ball id of the bonus ball and the
    associated prizes:

    +---------+------------+---------+---------+--------------------+
    | draw_id | date | ball_id | match_6 | match_5_plus_bonus |
    +---------+------------+---------+---------+--------------------+
    | 1207 | 2007-07-18 | 23 | 2838118 | 174653 |
    | 1206 | 2007-07-14 | 11 | 5248063 | 201848 |
    | 1205 | 2007-07-07 | 22 | 479142 | 73714 |
    +---------+------------+---------+---------+--------------------+

    continued below...

    +---------+---------+---------+
    | match_5 | match_4 | match_3 |
    +---------+---------+---------+
    | 2352 | 95 | 10 |
    | 3003 | 101 | 10 |
    | 1308 | 57 | 10 |
    +---------+---------+---------+

    The ball entity (maybe a bit ott but I think I need this table):

    +---------+--------+
    | ball_id | number |
    +---------+--------+
    | 1 | 1 |
    | 2 | 2 |
    | 3 | 3 |
    | 4 | 4 |
    | 5 | 5 |
    | 6 | 6 |
    | 7 | 7 |
    | 8 | 8 |
    | 9 | 9 |
    | 10 | 10 |
    | 11 | 11 |
    | 12 | 12 |
    | 13 | 13 |
    | 14 | 14 |
    | 15 | 15 |
    | 16 | 16 |
    | 17 | 17 |
    | 18 | 18 |
    | 19 | 19 |
    | 20 | 20 |
    | 21 | 21 |
    | 22 | 22 |
    | 23 | 23 |
    | 24 | 24 |
    | 25 | 25 |
    | 26 | 26 |
    | 27 | 27 |
    | 28 | 28 |
    | 29 | 29 |
    | 30 | 30 |
    | 31 | 31 |
    | 32 | 32 |
    | 33 | 33 |
    | 34 | 34 |
    | 35 | 35 |
    | 36 | 36 |
    | 37 | 37 |
    | 38 | 38 |
    | 39 | 39 |
    | 40 | 40 |
    | 41 | 41 |
    | 42 | 42 |
    | 43 | 43 |
    | 44 | 44 |
    | 45 | 45 |
    | 46 | 46 |
    | 47 | 47 |
    | 48 | 48 |
    | 49 | 49 |
    +---------+--------+

    The following tables are needed because of the many to many relationships:

    The balls associated with a ticket:

    +----------------+-----------+---------+
    | ticket_ball_id | ticket_id | ball_id |
    +----------------+-----------+---------+
    | 1 | 1 | 3 |
    | 2 | 1 | 13 |
    | 3 | 1 | 22 |
    | 4 | 1 | 32 |
    | 5 | 1 | 44 |
    | 6 | 1 | 46 |
    +----------------+-----------+---------+

    The balls associated with a draw:

    +--------------+---------+---------+
    | draw_ball_id | draw_id | ball_id |
    +--------------+---------+---------+
    | 1 | 1207 | 12 |
    | 2 | 1207 | 31 |
    | 3 | 1207 | 33 |
    | 4 | 1207 | 39 |
    | 5 | 1207 | 41 |
    | 6 | 1207 | 45 |
    | 7 | 1206 | 15 |
    | 8 | 1206 | 27 |
    | 9 | 1206 | 35 |
    | 10 | 1206 | 37 |
    | 11 | 1206 | 38 |
    | 12 | 1206 | 39 |
    | 13 | 1205 | 3 |
    | 14 | 1205 | 9 |
    | 15 | 1205 | 10 |
    | 16 | 1205 | 12 |
    | 17 | 1205 | 46 |
    | 18 | 1205 | 47 |
    +--------------+---------+---------+

    My first step was to figure out how many balls matched for a given
    draw using the following query:

    mysql> SELECT COUNT(ticket_ball.ball_id) AS matches
    -> FROM `ticket_ball`, `draw_ball`
    -> WHERE `ticket_id`=1 and `draw_id`=1205
    -> AND `ticket_ball`.`ball_id`=`draw_ball`.`ball_id`
    -> ;

    This results in:

    +---------+
    | matches |
    +---------+
    | 2 |
    +---------+

    As far as know I can also use a inner join to get this result although
    this method is still unclear to me.

    mysql> SELECT COUNT(ticket_ball.ball_id)
    -> FROM ticket_ball
    -> INNER JOIN draw_ball
    -> ON(ticket_ball.ball_id=draw_ball.ball_id)
    -> WHERE draw_id=1205
    -> ;

    +----------------------------+
    | COUNT(ticket_ball.ball_id) |
    +----------------------------+
    | 2 |
    +----------------------------+

    Although this is a start I cannot figure out how to elaborate this query
    so that it returns the prize won taking into account the bonus ball as
    well.

    So, for instance for draw 1205, if 5 balls match and the bonus ball
    matches it should return 73714. If 5 balls match it should return 1308.

    The CASE expression might come into play but I fail to see how to tie this
    in.

    As stated before, some pointers would be highly appreciated.
    rudderduck Guest

  2. #2

    Default Re: newbie needs a pointer

    > The draw entity with the draw date, ball id of the bonus ball and the 

    in my humble opinion there is too much stuff in one table. let try to split
    that to atomic parts.
    "The ball entity" table - what's that? looks a bit redundant.

    there is a too late for me so I dont give you a solution but I think
    re-arrange data model will be helpful in your situation.

    regards,
    R.


    aaooo54 Guest

  3. #3

    Default Re: newbie needs a pointer

    On Tue, 24 Jul 2007 00:43:40 +0200, aaooo54 wrote:
     
    >
    > in my humble opinion there is too much stuff in one table. let try to split
    > that to atomic parts.
    > "The ball entity" table - what's that? looks a bit redundant.
    >
    > there is a too late for me so I dont give you a solution but I think
    > re-arrange data model will be helpful in your situation.
    >
    > regards,
    > R.[/ref]

    Thanks for your reply, I agree that the "ball entity table" seems
    redundant, I will try and remove that. The reason I added it is because I
    was trying to identify all objects that might come into play.

    With regards to the draw_id table I cannot figure out how to make that
    more atomic than it already is unless I misunderstand the meaning of
    "atomic". I used the following definition for "atomic":

    By atomic we mean that there are no sets of values within a column.

    Which I took from:

    http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

    There are no sets of values in the columns of that table, the draw prizes
    (match_6, match_5_plus_bonus, etc.) may seem odd but are basically just
    ordinary fields. Every draw has those fields, no more, no less.

    Maybe you can help me out where my mistake with regard to the atomic
    part lies.

    Thanks again.
    rudderduck Guest

Similar Threads

  1. NEWBIE needs pointer
    By Ralph in forum PHP Development
    Replies: 4
    Last Post: December 26th, 03:16 PM
  2. Mouse pointer flickers between hand and pointer
    By enutty webforumsuser@macromedia.com in forum Macromedia Flash Sitedesign
    Replies: 15
    Last Post: December 5th, 05:07 PM
  3. Pointer in PHP
    By Denis Crespe in forum PHP Programming
    Replies: 3
    Last Post: October 26th, 07:55 PM

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