friendly Betting website database structure help trying to get to 2 normal form.

[tyweed]

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 issues with users and 2 normal form with groupname and notify having repeated values on different rows so i fixed like so

 

---- USERS----------------------------------------------

EMAIL(key) | password | username |  notify |

----------------------------------------------------------

 

-----groupnames --------

group_id(key) | groupname

-------------------------

 

-------group_email---------------  <======  this is suppose to tie the two to users

groupname | email (key)

-----------------------------

 

 

So if this is correct the tables for users should now be in 2 normal form. Now the hard one betstable

problem is that in 1st normal form betbetween and winner could be the same per row and same goes for

betcreator and winner.  So, i'm kinda stuck.

 

----------------betsTable -----------------------------------------------------

bet description | bet between | amount | winner | betId | betCreator | date of bet

--------------------------------------------------------------------------------

 

i'm trying to seperate the winner from table

 

-----------betId_winner----------------

betId | winner

--------------------------------------

 

 

Am i going about this correctly? This seems like alot of seperating and i'm concerned updating these tables is going to be a pain!

 

 

Please i'm up for all opinions friendly and mean!

 

[mezise]

A few questions:

 

1. Does one user may be assigned to many groupnames?

2. Does `bet between`, `winner` and `betCreator` fields represent users (are supposed to be filled with EMAIL(key))?

[tyweed]

Hey thanks for your input I'm not getting very many responses to this question. To answer your questions:

 

1. I believe it would be best that a user could be assigned to many groupnames.

2. betcreator,betbetween,and winner are representing users. However i'm filling them with the username instead of email. Both will be unique to the tables.

 

So what you think is the revised tables where i seperate betbetween ,winner,and betcreator from betstable necessary? It makes my job a little harder if i remove them each into their own tables. My concern is when they are in the same table you get a table like the following.

 

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

 

you see how with winnner , betcreator, and betbetween you get repetitive data. For example winner betbetween could both be Steve in the same row. And in addition steve could be in multiple columns. Is this a problem? This is why i thought of removing from the table and making their own table.

[mezise]

If I understand correctely your task, I would do this like that:

---- USERS ------------------------------------------------------
user_id | email | password | username | notify |
-----------------------------------------------------------------

---- GROUPS -----------------------------------------------------
group_id | groupname |
-----------------------------------------------------------------

---- GROUPS_USERS -----------------------------------------------
group_id | user_id |
-----------------------------------------------------------------
GROUP_USERS table assigns many groups to many users.

---- BETS ------------ ---------------------------------------------------------------------
bet_id | description | date | amount | between_user_id | winner_user_id | creator_user_id |
--------------------------------------------------------------------------------------------

 

There may be many users.

There may be many groups.

Every user may be assigned to many groups.

There may be many bets.

Every bet may have one between_user, one winner_user and one creator_user.

 

[tyweed]

I have  a quick question for you if you don't mind after implementing this new database design i realized every time i print out the betstable it is printing the betBetween_user_id , winner_user_id, betCreator_user_id

just as your table shows

 

---- BETS ------------ ---------------------------------------------------------------------

bet_id | description | date | amount | between_user_id | winner_user_id | creator_user_id |

--------------------------------------------------------------------------------------------

 

problem is the user_id's are just numbers so the table ends up looking like

 

nameBet betBetween amount winner betId (pk) betCreator dateOfBet

test Open 5 2 1 1  24-Apr-2008

test1 1    4 1 2  1  02-Apr-2008

test 2 14 1  3 2  02-Apr-2008

testy Open 45 1 4 2 02-Apr-2008

 

instead of

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

 

so it would require me to re translate everything from user_id to username.

 

Do you think if i put there usernames in the betstable that would be poor design vs the user_id?

 

 

 

 

[mezise]

General rule designing a database structure is: Anticipate probable changes in the data that may forces you to change the database structure.

If you will use username instead of user_id as a foreign key and if a user will want to change his username you will be forced to update every record where his username was used.

[jd307]

I can see what you are saying that it doesn't give your output something like username Kevin01 as the winning_user_id, instead you get the ID number instead.  But as what was said above, if the user changes their username, you will have to make sure you update the record in the user table and the bets table.

 

As you already know what the user_id is from winning_user_id, then you could incorporate something like:

 

SELECT username
FROM users, bets
WHERE user_id = winning_user_id

 

This can therefore look at the winning_user_id and find the matching user_id in the users table.  In turn, it can pull out the username which is the better way of doing it really rather than storing the username in place of winning_user_id.

 

I hope this helps somewhat.

[tyweed]

jd307 i have a question for you. I would need to do this for winner_id,betcreator_id,and betbetween_id. What if i run through the table once creating a map of the user_id to username. Then i could just check the map to the user_id's returned with php function versus having to convert with sql statements evertime i need the user_id's converted to usernames.

 

array['jon'] =0

array['lisa'] = 1

etc .........

 

 

Is this a bad way to go about it?