Database design

I'm working on a database for a web app and I'm stuck. What is better in
the long run and what are the pros/cons of each design

Version 1

4 tables: Organization, Season, League and Team

Season is related to Organization, League is related to Season and Team
is related to League.

Organization Table
org_id (primary)
org_name

Season Table
season_id (primary)
org_id
season_name

League Table
league_id (primary)
season_id
league_name

Team Table
team_id (primary)
league_id
team_name

OR

Version 2

2 tables: Organization and Team

Team is related to Organization

Organization Table
org_id (primary)
org_name

Team Table
team_id (primary)
org_id
season
league
team_name

Thanks

Brett
bawork@sprynet.com

Re: Database design

"BaWork" wrote in message
news:ujL71THmFHA.1044@tk2msftngp13.phx.gbl...
> I'm working on a database for a web app and I'm stuck. What is better in
> the long run and what are the pros/cons of each design
>
> Version 1
>
> 4 tables: Organization, Season, League and Team
>
> Season is related to Organization, League is related to Season and Team
> is related to League.
>
> Organization Table
> org_id (primary)
> org_name
>
> Season Table
> season_id (primary)
> org_id
> season_name
>
> League Table
> league_id (primary)
> season_id
> league_name
>
> Team Table
> team_id (primary)
> league_id
> team_name
>
> OR
>
> Version 2
>
> 2 tables: Organization and Team
>
> Team is related to Organization
>
> Organization Table
> org_id (primary)
> org_name
>
> Team Table
> team_id (primary)
> org_id
> season
> league
> team_name
>
> Thanks
>
> Brett
> bawork@sprynet.com

How many Organizations, Leagues, Seasons and Teams?

How static/dynamic is the information?

How will it be used?

Re: Database design

Your questions:

How many Organizations, Leagues, Seasons and Teams? Big - 10X10X10X10

How static/dynamic is the information? After initial entry, these items
are static

How will it be used? Sports reporting for a high school

McKirahan wrote:
> "BaWork" wrote in message
> news:ujL71THmFHA.1044@tk2msftngp13.phx.gbl...
>
>>I'm working on a database for a web app and I'm stuck. What is better in
>>the long run and what are the pros/cons of each design
>>
>>Version 1
>>
>>4 tables: Organization, Season, League and Team
>>
>>Season is related to Organization, League is related to Season and Team
>>is related to League.
>>
>>Organization Table
>>org_id (primary)
>>org_name
>>
>>Season Table
>>season_id (primary)
>>org_id
>>season_name
>>
>>League Table
>>league_id (primary)
>>season_id
>>league_name
>>
>>Team Table
>>team_id (primary)
>>league_id
>>team_name
>>
>>OR
>>
>>Version 2
>>
>>2 tables: Organization and Team
>>
>>Team is related to Organization
>>
>>Organization Table
>>org_id (primary)
>>org_name
>>
>>Team Table
>>team_id (primary)
>>org_id
>>season
>>league
>>team_name
>>
>>Thanks
>>
>>Brett
>>bawork@sprynet.com
>
>
> How many Organizations, Leagues, Seasons and Teams?
>
> How static/dynamic is the information?
>
> How will it be used?
>
>
>

Re: Database design

If you've done any database design at all you know the 'correct' database
design is the first one. By having separate tables for each element you get
a lot of benefits that the second design doesn't provide. A few things off
the top of my head.

- Easy to create dropdowns dynamically for Season and League - eliminates a
lot of typos, eliminates redundant data
- Easy to add information about Seasons and Leagues - adding additional
redundant fields to Team records would be bad
- Easier to select entries by Season and League because you will use numbers
(generated by a combo box usually) instead of searching for strings


"BaWork" wrote in message
news:ujL71THmFHA.1044@tk2msftngp13.phx.gbl...
> I'm working on a database for a web app and I'm stuck. What is better in
> the long run and what are the pros/cons of each design
>
> Version 1
>
> 4 tables: Organization, Season, League and Team
>
> Season is related to Organization, League is related to Season and Team
> is related to League.
>
> Organization Table
> org_id (primary)
> org_name
>
> Season Table
> season_id (primary)
> org_id
> season_name
>
> League Table
> league_id (primary)
> season_id
> league_name
>
> Team Table
> team_id (primary)
> league_id
> team_name
>
> OR
>
> Version 2
>
> 2 tables: Organization and Team
>
> Team is related to Organization
>
> Organization Table
> org_id (primary)
> org_name
>
> Team Table
> team_id (primary)
> org_id
> season
> league
> team_name
>
> Thanks
>
> Brett
> bawork@sprynet.com