Topic: Could you help me in designing my database?

I plan on using MySql and it's going to be a football (American) related Web site. It will be like a message board/blog with user posts on whatever player they want categorized by player name and by week.

The users will have to register before being allowed to comment.

Here's a rough mental break down of it:

User table:
username
password
email
Primary Key (auto number)

Sport table: (in case I expand to baseball and other sports)
Primary Key (auto number)
NFL

Conference table:
NFC
AFC
Primary key (auto number)


Team table
All 32 teams W
City
Other info
Primary Key
Conference with conference primary key

Positions
QB
WR
RB
etc.
Primary keys for each.

Player Table
Primary Key (auto number)
Name
position (with primary key from position)
team (with primary key from position)
conference (with primary key from position)
etc.

Here's the point I'm having problem with. I want to log the players info for each week and let users make comments based on that for each week. What's the best way to accomplish this as far as database design goes?

I was thinking of a table for each week that would log the primary key for the player to the comments, but should I make a table for each player per week or one table for the entire league per week?

Any help is greatly appreciated and if I can be of assistance please let me know and it's late right now so I might not have made much sense!

Thanks,
Don.

Re: Could you help me in designing my database?

Creating a new table for each week, at any scope, would be a nightmare.  Scope out a dozen user stories, prioritize them, and start working.  Big-up-front-design is for the birds.  Invariably, whatever you came up with will be wrong.

BrewControl.com - Brewery and Brewpub management powered by RoR

Re: Could you help me in designing my database?

bkrahmer wrote:

Creating a new table for each week, at any scope, would be a nightmare.  Scope out a dozen user stories, prioritize them, and start working.  Big-up-front-design is for the birds.  Invariably, whatever you came up with will be wrong.

Thanks for the reply!

I've only developed with ASP.Net, Java and some PHP so this "Agile" development is pretty new and exciting!

I'm a newb so please expand on "Scope out a dozen user stories" My thought is that I need to have the players listed so that a user can select them and comment underneath just for that specific player. Are you saying just make my player database (similar to a product database on a conventional site) and then go with it and see how if fleshes out from there?

Thanks again,
Don.

Re: Could you help me in designing my database?

A 'user story' describes one single feature.  Your first user story might be 'Allow a user to create an account on the site'.  The next story might be 'Allow administrative accounts', followed by 'Allow admin accounts to create/update/delete teams', etc.  Create each table as you need it.  I'd say as a hunch that your database design that you mentioned is pretty close.  I would leave out the sport table.  Don't design in speculative features.  It adds complexity that may never be used.

BrewControl.com - Brewery and Brewpub management powered by RoR

Re: Could you help me in designing my database?

bkrahmer wrote:

A 'user story' describes one single feature.  Your first user story might be 'Allow a user to create an account on the site'.  The next story might be 'Allow administrative accounts', followed by 'Allow admin accounts to create/update/delete teams', etc.  Create each table as you need it.  I'd say as a hunch that your database design that you mentioned is pretty close.  I would leave out the sport table.  Don't design in speculative features.  It adds complexity that may never be used.

Thank you again for the clarity! I appreciate it, being new to this it's such a different method of working that sometimes I lose site of the trees because of the forest!

Don.

Re: Could you help me in designing my database?

OK, I have almost completed the Agile Rails development book so I'm about to start my own app.

Here's what I have to start with and please correct, guide, scold, mock me!

I start with a player DB:

Player:
ID (primary key autonumber)
FirstName
LastName
Position
Team

Week:
ID (primary Key autonumber)
WeekNumber
Stats
PlayerID (foreign key from Players table ID)

Comments:
ID (primary Key autonumber)
UserID
Comment

Players will have many weeks which will have many comments, and the reverse has one going back from comments to weeks to players.

So I figure I will start with the player DB and a form to create new entries, when I get a few in I will then move on to the Week creation and finally the Comments.

Eventually I would like to add admin only for players and weeks and user for the comments but I think that first I should just try to create this structure.

Any help or comments or links or anything will be of great help!

Thanks again!

Re: Could you help me in designing my database?

To enrich your application, here is another use story:

Tim works at the shopping mall. He is usually a quite guy but has a strong opinion of his views. He hates reading other peoples' interpretation of events and prefers his opinion. He is a sports fanatic and together with his colleagues watch all big time matches, in which their favorite players participate

Tim  needs a forum to make comment about Joe(plays for Mariners), he did not give his best at the last match.

To start our agile process we start with:
A method for Joe to make a comment about the player.
A way to associate this post/comment with Tim.

Do you have buddies? Get one guy to play Tim, show him a sketch on paper of how he can make a post and have it visible to the whole big world.how the post would look like. keep it simple. Ask him to detail what else he would like to do, redraw it and show it to him again.

Make the necessary table and columns, a controller and an associated view, display it, show it to your 'Tim', he may be or not satisfied....This marks the start of a real world practical application

Rails allows you to change the design fast and easy, thanks its agile and you are agile!

Re: Could you help me in designing my database?

I started the site this weekend. The first thing I did was make it possible to enter players in a database with their information. I'm now trying to make it so that with the click of a button I can pass the player info to the weeks so that I can create entries in that database. So far so good!

I will probably post in a different thread for any further problems, unless they are database related. I will try and put a link here for anyone that may find this thread.

Don.

Re: Could you help me in designing my database?

When I design a database I think in terms of nouns and verbs. Nouns are tables, verbs are methods on those nouns. A Team plays a game against an opposing team. Tables => team, game, opposing_team. Methods => play which would allow you to create a game, pick two teams, etc... I know it helps me (subjective) to think in English first and then db it up.

Re: Could you help me in designing my database?

dononyx@yahoo.com wrote:

When I design a database I think in terms of nouns and verbs. Nouns are tables, verbs are methods on those nouns. A Team plays a game against an opposing team. Tables => team, game, opposing_team. Methods => play which would allow you to create a game, pick two teams, etc... I know it helps me (subjective) to think in English first and then db it up.

Thank you for the tips! That makes sense to me, especially with Ruby!

I have posted other questions about it here: http://railsforum.com/viewtopic.php?id=6602

So far so good with all the help that I received from everyone here!

Thanks again everybody!
Don.