Too many table fields

S

Stokely

I have been working with Access for almost 2 years now and I feel I have an
excellent comprehension of database normalization.

I'm trying to design a system to track and update the depth chart for a
sports team. Specifically 4 lines of 5 positions each, 4 lines of 4 positions
each, and 4 lines of 3 positions each for each team.

The idea is to be able to view all these lines on a single form for any
given team and change players using combo boxes. (Perhaps using tabs for the
different groups of lines). It seems to me this might be one of the few
situations for a table with a lot of fields. A field for each of the
positions.

However, it also seems like it goes against good database structure.

Any ideas?
 
J

John W. Vinson

I have been working with Access for almost 2 years now and I feel I have an
excellent comprehension of database normalization.

I'm trying to design a system to track and update the depth chart for a
sports team. Specifically 4 lines of 5 positions each, 4 lines of 4 positions
each, and 4 lines of 3 positions each for each team.

The idea is to be able to view all these lines on a single form for any
given team and change players using combo boxes. (Perhaps using tabs for the
different groups of lines). It seems to me this might be one of the few
situations for a table with a lot of fields. A field for each of the
positions.

However, it also seems like it goes against good database structure.

Any ideas?
Click to expand...

I'd say it does indeed go against good database structure. Each player has
some specific attributes: a Team, a Line, a Position. It's still a one (team)
to many (player) relationship.

You could certainly have Subforms based on queries, selecting a particular
position; the players in that position could be displayed in sort order by
line. Alternatively, or in addition, you could have subforms based on lines,
showing all the positions for the first team, the second team and so on.

Having 48 bound combo boxes on a form would be a) confusing and b) really hard
to manage!

John W. Vinson [MVP]
 
N

NetworkTrade

and if A. and B. were not bad enough with ~50 fields you could easily bump up
against the 2k record size character max depending on your field lengths.....
 
S

Stokely

So I guess, there would be another look-up table that housed each position
and each line, probably 40+ records that were then linked to each playerID
accordingly. That way a single player could occupy mulitple spots on the same
depth chart, which is a must.

In one table though, if a new team is added each position is already
reserved with a null value. In a relationship database you'd have to have
some type of append query to force the new team to display the unoccupied
roster spots.
 
J

John W. Vinson

So I guess, there would be another look-up table that housed each position
and each line, probably 40+ records that were then linked to each playerID
accordingly. That way a single player could occupy mulitple spots on the same
depth chart, which is a must.

In one table though, if a new team is added each position is already
reserved with a null value. In a relationship database you'd have to have
some type of append query to force the new team to display the unoccupied
roster spots.
Click to expand...

I can't see why an empty "placeholder" would ever be needed. If there is no
placekicker, there simply would be no placekicker record in the table.

John W. Vinson [MVP]
 
T

Tom Wimpernark

the limits in Access is why I moved to SQL Server and Access Data Projects a
long long long time ago
 
N

NetworkTrade

in departmental, small business and non-profits - - I've always been able to
meet their need with Access......I like the product for it's affordability;
often they have it as part of their Office anyways....I have no complaints
against Access , at least in comparison to any other software I've every seen
- they all have some quirks....but size is size and I would only go to SQL
server if the issue is file size and transaction volume......
 
G

George Hepworth

Aaron Kem.pf is posting under a new alias.

Tom Wimpernark said:
the limits in Access is why I moved to SQL Server and Access Data Projects
a long long long time ago
Click to expand...
 
J

John W. Vinson

On Wed, 30 May 2007 09:16:00 -0700, NetworkTrade

"Tom Wimpernark" is another of A.aron K.empf's aliases. This person posts only
to bash MDB files, and anyone who uses them. To him there is only one answer,
whatever the question: ADP bound to SQL/Server.

John W. Vinson [MVP]
 
S

Stokely

The idea is that the user can go to a new "Team" and all of the positions on
the depth chart exist, he/she just needs to select a player to fill in the
position.

If everything is relational then the user has to know all of the positions
because there is no way to see what you are missing without comparing what
you have added with a complete depth chart (either in your head or on a sheet
of paper).

Perhaps with an append query all of the positions could be added instantly
with a default (or null) player ID. That way the user could see all of the
depth chart positions and keep the database relational.

I'll try that, thanks.
 
J

John W. Vinson

The idea is that the user can go to a new "Team" and all of the positions on
the depth chart exist, he/she just needs to select a player to fill in the
position.

If everything is relational then the user has to know all of the positions
because there is no way to see what you are missing without comparing what
you have added with a complete depth chart (either in your head or on a sheet
of paper).

Perhaps with an append query all of the positions could be added instantly
with a default (or null) player ID. That way the user could see all of the
depth chart positions and keep the database relational.
Click to expand...

That would be one possible reason to have such "dummy" records... like the
draft, "A Player to be named later".

John W. Vinson [MVP]
 
Top