Jump to content

Archived

This topic is now archived and is closed to further replies.

skyace888

MySQL Database Schema

Recommended Posts

I'm currently taking a directed project course at my university and need a little assistance setting up the design of a database.  I'm working on a PHP/MySQL program for coaches to track the statistics of a basketball team.  I'm using DBDesigner4 to design the database.  My current model is at [url=http://education.alcodesigns.net/projects/coursework/db_schema9-9-06.gif]http://education.alcodesigns.net/projects/coursework/db_schema9-9-06.gif[/url].  My foreign keys that I have so far:

PLAYERS(TeamNumber) is a foreign key to TEAM(Number)
MEMBERS(TeamNumber) is a foreign key to TEAM(Number)
TIMEOUT_LOG(GameID) is a foreign key to GAME_INFO(GameID)

The sample file with explanations is at [url=http://education.alcodesigns.net/projects/coursework/samplefile.doc]http://education.alcodesigns.net/projects/coursework/samplefile.doc[/url].  I'm not sure how I can efficiently log all the offensive and defensive plays based on the information in the file.  Each play has an outcome that must be recorded as well.  Any suggestions are greatly appreciated!

Thanks,
Al

Share this post


Link to post
Share on other sites
http://www.halfadot.com/temp/db_schema.gif

That's a 32kb version of the [b]2Mb[/b] bmp file you posted.  Can you duplicate it to your own server and edit the link in your post ... it'll save everyone who visits a lot of time :)

If you need a free image editor/converter use IrfanView from www.irfanview.com (windoze only)

Share this post


Link to post
Share on other sites
Ok.  I took care of making the image smaller.  I didn't realize it was that large.  Thanks for pointing that out, AndyB.

Share this post


Link to post
Share on other sites
Not that I'm a fan of doing someone else's homework, but could you describe, in brief, what you're trying to accomplish without reference to other materials?

Share this post


Link to post
Share on other sites
Hi fenway!  I don't expect anyone to do my homework for me, rather I came here hoping someone could provide some suggestions and lead me in the right direction.  Anyways, I think I have the "Players", "Team", and "Program" regions in my database map setup correctly.  Those were pretty straightforward.  I'm having difficulty figuring out how to design the "Game" region.  According to the Word sample file, I need to record all the offensive and defensive plays in the game.  I'm not sure if I should make this in one table, called "PLAY_LOG", or have an offensive and defensive log separate.  In addition, every play has an outcome and some of these outcomes need additional fields for player numbers.  I think I have the "GAME_INFO", "TIME_OUT", and "TIMEOUT_LOG" setup correctly.  The program is supposed to be able to allow for queries such as "What was the success rate of play x when players a, b, c, d, and e were on the court?"  I have worked with databases that track orders and products in the past, where there are many examples, however this one is proving more challenging.  Maybe I am not interpreting the sample data correctly...

Thanks,
Allen

Share this post


Link to post
Share on other sites
That's the right approach... you should have a play_log table, as you describe, though I would keep all the info together, just flag the play as offensive/defensive.  As far as the "outcomes", if you can come up with some general fields that describe all the plays, then put them in that table, otherwise, break it out.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.