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

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.

thread moved

could you give example rows for your new design please

i can't understand how the "betbetween" column works

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.

betBetween has only 1 value?

how does betBetween = Sam work?

doesn't "between" imply more than one person?

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

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

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.

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