Results 1 to 9 of 9
  1. #1
    Join Date
    May 2008
    Posts
    7

    Thumbs up Revising my database structure please have a look give me your input before I use it.

    Ok, so I have designed a website so that my buddies and I can keep track of all our friendly bets online. I'm trying to learn relational database structures in the process. So, here was my first design.

    2 tables

    ---- USERS----------------------------------------------
    EMAIL(key) | password | username | groupname | notify |
    ----------------------------------------------------------
    groupname is so only users with the same groupname are displayed on the table views
    notify - value of yes or no whether they want email notifications

    ----------------betsTable---------------------------------------------------
    bet description | bet between | amount | winner | betId (key)| betCreator | date of bet
    --------------------------------------------------------------------------------

    So, after doing my homework I found there are some repetition issues with this design. I have gone through a few books and tutorials and have revised my tables. Please can all you database experts give me opinoins on if this design is better.

    BetsTable
    ------------------------------------------------------------------
    betId (key) | nameBet | amount | dateOfBet
    -------------------------------------------------------------------

    winner_betId
    -----------------------------
    betId(pk) | winner
    ---------------------------

    betBetween_betId
    ---------------------------------
    betId(pk) | betBetween
    ---------------------------------

    betCreator_betId
    --------------------------------
    betId(pk) | betCreator
    --------------------------------


    users
    ------------------------------------------------------------------------------------------
    user_id(pk) | email (unique) | username (unique) | password (unique) | notify enum(yes,no)
    ------------------------------------------------------------------------------------------

    groupnames
    -------------------------------
    id(pk) | groupname (unique)
    -------------------------------


    user_id_groupnames
    ------------------------------
    user_id(pk) | groupname(unique)
    -------------------------------


    old design of betstable
    -----------------------------------------------------------------------------------------
    nameBet betBetween amount winner betId (pk) betCreator dateOfBet
    test Open 5 steve 1 ken 24-Apr-2008
    test1 ken 4 ken 2 steve 02-Apr-2008
    test 2 ken 4 steve 3 steve 02-Apr-2008
    testy Open 45 ken 4 steve 02-Apr-2008
    ffffff steve 23456 steve 5 ken 05-Apr-2008
    -----------------------------------------------------------------------------------------

    old design of users
    --------------------------------------------------------------------------------------

    email username password groupname notify
    test@yahoo.com ty molli group1 no
    test2@yahoo.com jony361 ytrtyuu8 group2 no
    test3@yahoo.com tyhgf vvvvv group1 yes
    --------------------------------------------------------------------------------------

  2. #2
    Join Date
    May 2008
    Posts
    7
    I apologize after further researh I should have put this in "Database Concepts & Design " I don't know how to move it myself so i'll just hope a moderator moves it. In the meantime i'm still open for any and all help. This will be in mysql syntax eventually.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thread moved

    could you give example rows for your new design please

    i can't understand how the "betbetween" column works
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2008
    Posts
    7
    betBetween is essentially this:

    John and Sam have a bet on who will win the nba game between lakers vs warriors.

    table would look like the following:

    old design of betstable
    ----------------------------------------------------------------------------
    nameBet betBetween amount winner betId (pk) betCreator dateOfBet
    lakers vs warriors Sam $45 none 5 Jon
    11/24/2008
    ---------------------------------------------------


    new design: with many more tables

    betstable
    ----------------------------------------------------------
    betId (key) nameBet amount dateOfBet
    5 lakers vs warriors $45 11/24/2008
    ---------------------------------------------------------
    winner_betId
    -----------------------------
    betId(pk) | winner
    5 none
    ---------------------------

    betBetween_betId
    ---------------------------------
    betId(pk) | betBetween
    5 Sam
    ---------------------------------

    betCreator_betId
    --------------------------------
    betId(pk) | betCreator
    5 Jon
    --------------------------------


    Was the old table fine or was it neessary to add all those extra tables.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    betBetween has only 1 value?

    how does betBetween = Sam work?

    doesn't "between" imply more than one person?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2008
    Posts
    7
    yes, betbetween really should be named betOpponent but it is just the other person you are betting with. So, you have a betCreator who made up the bet and a betBetween person who accepts the bet. So, what you think? Is splitting up the tables like I have done in the new table setup better? Because the older table design seems much easier to use select on to match values. I'm very new to database design and I really want to get it right in the beginning

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't see how splitting the table into betCreator and betOpponent/betBetween is any better (no pun intended)

    however, it is better in one way: if you ever change your system so that there can be three-way, four-way, or more-way bets

    then the old system is very clumsy indeed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    May 2008
    Posts
    7
    what about the repetition in the columns of winner,betbetween, and betCreator in the rows on the older version table design. As an example look at this:

    nameBet betBetween amount winner betId (pk) betCreator dateOfBet
    test Open 5 steve 1 ken 24-Apr-2008
    test1 ken 4 ken 2 steve 02-Apr-2008
    test 2 ken 4 steve 3 steve 02-Apr-2008
    testy Open 45 ken 4 steve 02-Apr-2008
    --------------------------------------------------

    see how steve and ken are repeated frequently over many rows and in other instances the winner repeats with either betCreator or betBetween (depending on who wins the bet) within the same row. Does this not fail the 1st and 2nd normal forms tests. This is why I was going to separate them out into there own tables. I read that this is necessary to avoid insert,delete anamolies. It sure makes my life easier to keep them all in one table like the first version but I want to do it right the first time.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tyweed
    Does this not fail the 1st and 2nd normal forms tests.
    no, it doesn't

    1NF means single values in each column, so you're okay there

    2NF means each column is fully dependent on the PK, and since the PK is a single column and not a composite PK, you're okay there too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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