List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:September 1 2010 9:49pm
Subject:Re: Database design help
View as plain text  
On 9/1/2010 11:47 AM, Tompkins Neil wrote:
> I do have a tabled which contains both the managers_id and teams_id for the
> current teams managed.  I think by adding the managers_id alongside the
> fixture_result table will then allow me to find which points the manager
> has accumulated alongside which fixtures and teams.
> 
> Cheers
> Neil
> 
> 
> On Wed, Sep 1, 2010 at 4:43 PM, Jerry Schwartz <jerry@stripped> wrote:
> 
>> I strongly suggest that you make a separate table for the manager <-> team
>> relationship, so you can keep a history. Put a date-stamp in there. This
>> might
>> come in handy as you get further into your design.
>>
>> I ran into this problem when one of our sales reps moved from one office to
>> another, and took their sales history with them! That was a mess to
>> unscramble.
>>
>> Regards,
>>
>> Jerry Schwartz
>> Global Information Incorporated
>> 195 Farmington Ave.
>> Farmington, CT 06032
>>
>> 860.674.8796 / FAX: 860.674.8341
>> E-mail: jerry@stripped
>> Web site: www.the-infoshop.com
>>
>>> -----Original Message-----
>>> From: Neil Tompkins [mailto:neil.tompkins@stripped]
>>> Sent: Tuesday, August 31, 2010 3:48 PM
>>> To: mysql@stripped
>>> Subject: Database design help
>>>
>>> Hi
>>>
>>> I've a soccer application consisting of managers, teams players and
>>> fixtures/results. Basically each manager will get points for each game
>>> which will depend on the result.
>>>
>>> What would be the best table design bearing in mind that a manager can
>>> move to a different club.
>>>
>>> My thought was to have a field in the fixtures/results table for the
>>> manager points but i think that I will also need a users field so that
>>> I can remember which points belong to which manager.
>>>
>>> Is this the correct approach??
>>>
I think you are definitely on the right track. Each score does not 
belong to just a manager or to a team but to a manger/team combination. 
Should the manager switch teams, those results need to remain associated 
to both entities not just the manager.

here's a possible record shape:

manager_id, team_id, game_id, ... summary details about the game ...

This way each result is associated with the correct combination of 
entities (a manager and a team) and not just one or the other.

-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
Thread
Database design helpNeil Tompkins31 Aug
  • Fwd: Database design helpTompkins Neil1 Sep
    • Re: Database design helpClaudio Nanni1 Sep
    • RE: Database design helpwebmaster1 Sep
  • RE: Database design helpJerry Schwartz1 Sep
    • Re: Database design helpTompkins Neil1 Sep
      • Re: Database design helpMySQL)1 Sep