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??

Cheers Neil

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Fwd: Database design help

--0016363b88ac9b7170048f315469
Content-Type: text/plain; charset=ISO-8859-1

Looking for some help / comments if possible ?

Cheers
Neil

---------- Forwarded message ----------
From: Neil Tompkins
Date: Tue, Aug 31, 2010 at 8:48 PM
Subject: Database design help
To: mysql@lists.mysql.com


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??

Cheers Neil

--0016363b88ac9b7170048f315469--

Re: Database design help

--00c09f923ddabb5cd6048f319da6
Content-Type: text/plain; charset=ISO-8859-1

Hi there,

I know you would like just a solution, but I want to give you just a little
bit of background.

Think in real life things(entities), think as you would have to do it on
paper.


[1]
You said you have:

managers, teams players and fixtures/results (matches)

these are your tables

plus.... teams!

[2]
then find what relationships you have between tables.

(a) *manager* (have name and other properties) belongs to a team
(b) *player* (have name and other properties) belong to a team
(c) *team* (have name , have 1 manager, and other properties) play matches
(d) *match* have results

translate [have] with properties(columns with values of the table)
translate other actions with relationships (columns with ids of other
tables)

in the case of *match* you would have: idteam1, idteam2, result at least.
in the case of *player* you would have: idplayer, name, idteam at least
in the case of *manager*, if you think a manager can manage more than one
team you will use an idmanager in the team table
otherwise you can use also an idteam in the manager table that allows more
managers to manage the same team.


I dont want (no time sorry!) to write here the data model, but I think this
few lines can trigger the best idea in you.


Claudio






2010/9/1 Tompkins Neil

> Looking for some help / comments if possible ?
>
> Cheers
> Neil
>
> ---------- Forwarded message ----------
> From: Neil Tompkins
> Date: Tue, Aug 31, 2010 at 8:48 PM
> Subject: Database design help
> To: mysql@lists.mysql.com
>
>
> 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??
>
> Cheers Neil
>



--
Claudio

--00c09f923ddabb5cd6048f319da6--

RE: Database design help

Hi Neil,

May be your question is too vague.

You have already identified the 'real world' objects that you want
represented in the database.

Have you identified the specific pieces of information that you want stored
for each object ? After you do that, you can then start to see what the
relationships between the objects are.

And you can then ask people that don't know anything about your application
more specific questions like:

- this is what I have in this and that object : how do I get this and that
to relate to this and that ?

Thanks,
Justin

-----Original Message-----
From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
Sent: 01 September 2010 12:52
To: [MySQL]
Subject: Fwd: Database design help

Looking for some help / comments if possible ?

Cheers
Neil

---------- Forwarded message ----------
From: Neil Tompkins
Date: Tue, Aug 31, 2010 at 8:48 PM
Subject: Database design help
To: mysql@lists.mysql.com


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??

Cheers Neil



__________ Information from ESET NOD32 Antivirus, version of virus signature
database 5414 (20100901) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com



__________ Information from ESET NOD32 Antivirus, version of virus signature
database 5414 (20100901) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Database design help

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@gii.co.jp
Web site: www.the-infoshop.com

>-----Original Message-----
>From: Neil Tompkins [mailto:neil.tompkins@googlemail.com]
>Sent: Tuesday, August 31, 2010 3:48 PM
>To: mysql@lists.mysql.com
>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??
>
>Cheers Neil
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jerry@gii.co.jp





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Database design help

--0015175cdc38fa98cb048f349cce
Content-Type: text/plain; charset=ISO-8859-1

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 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@gii.co.jp
> Web site: www.the-infoshop.com
>
> >-----Original Message-----
> >From: Neil Tompkins [mailto:neil.tompkins@googlemail.com]
> >Sent: Tuesday, August 31, 2010 3:48 PM
> >To: mysql@lists.mysql.com
> >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??
> >
> >Cheers Neil
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe: http://lists.mysql.com/mysql?unsub=jerry@gii.co.jp
>
>
>
>
>

--0015175cdc38fa98cb048f349cce--

Re: Database design help

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 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@gii.co.jp
>> Web site: www.the-infoshop.com
>>
>>> -----Original Message-----
>>> From: Neil Tompkins [mailto:neil.tompkins@googlemail.com]
>>> Sent: Tuesday, August 31, 2010 3:48 PM
>>> To: mysql@lists.mysql.com
>>> 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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org