skyace888 Posted September 9, 2006 Share Posted September 9, 2006 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 Quote Link to comment https://forums.phpfreaks.com/topic/20224-mysql-database-schema/ Share on other sites More sharing options...
AndyB Posted September 9, 2006 Share Posted September 9, 2006 http://www.halfadot.com/temp/db_schema.gifThat'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) Quote Link to comment https://forums.phpfreaks.com/topic/20224-mysql-database-schema/#findComment-88949 Share on other sites More sharing options...
skyace888 Posted September 9, 2006 Author Share Posted September 9, 2006 Ok. I took care of making the image smaller. I didn't realize it was that large. Thanks for pointing that out, AndyB. Quote Link to comment https://forums.phpfreaks.com/topic/20224-mysql-database-schema/#findComment-88953 Share on other sites More sharing options...
fenway Posted September 9, 2006 Share Posted September 9, 2006 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? Quote Link to comment https://forums.phpfreaks.com/topic/20224-mysql-database-schema/#findComment-88972 Share on other sites More sharing options...
skyace888 Posted September 9, 2006 Author Share Posted September 9, 2006 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 Quote Link to comment https://forums.phpfreaks.com/topic/20224-mysql-database-schema/#findComment-89048 Share on other sites More sharing options...
fenway Posted September 9, 2006 Share Posted September 9, 2006 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. Quote Link to comment https://forums.phpfreaks.com/topic/20224-mysql-database-schema/#findComment-89089 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.