Re: baseball league db design

Karel Miklav <karel_at_inetis.spppambait.com> wrote in message news:<Ao8Qb.1912$%x4.322559_at_news.siol.net>...

> Try this:
>
> participant (id, name, address, ...)
> coach (id, team_id, ...)
> player (id, team_id, coach_id, ...)
> manager (id, team_id, ...)
>
> All common data is stored in the 'participant' table, then there is a
> specialized table for each role. When you store the data for a role, you
> update 'participant' and another corresponding table, and they are of
> course linked by the same id. Sounds reasonable?

Sounds perfectly reasonable. So 'id' above becomes a common primary key across all roles and participants, and although I might autoincrement my Participant id, I shouldn't do that with the various roles since they share the same id which I'll pull from Participant.

One other thing...many of the participants share the same address. For example, parents, siblings in the same league, etc. Does it seem reasonable to store addresses separately in an Address table? Then each participant would have a foreign key to an Address.

Thanks for your help!

--
John