This is more of a general db question pertaining to design of one specific table as opposed to a syntax issue.
In the course of a personal project I am attempting to build a database to capture data for a sports team. When it’s completed I want to do the standard inputting and updating of records and so forth. The question I have is in more in the area of designing a table(s).
If I have a softball team that has veteran players that have played intermittently for the team. If a player plays in non-consecutive seasons and wears more than one uniform number while they play, this poses a problem. For example, I wish to be able to post a player’s data, name, height, weight, etc when a user visits our webpage(not up yet) and clicks on a player. If she has more than one number in the 5 seasons she has played it is a bit odd to post both uniform numbers. But compared to the idea of adding uniform number as part of the primary key, an idea I must say I don’t like at all, I feel it is the best alternative. I am therefore considering a table called players that has the name, position, college team attended if any, and something like uniform_number_start_date1, uniform_number_end_date1, uniform_number1, uniform_number_start_date2, uniform_number_end_date2, uniform_number2, etc for periods they wore their numbers and the numbers themselves. I assume that corporations that have employees work for them and have multiple durations must have a similar setup. Is this a good way to handle this data? Thanks again everyone.
I did not separate the "player" and the "person" but either way, I still see the same problem. If I have one player with 2 or even 3 uniform numbers throughout her career, that means that I have to have an equal number of unique records for each instance unless I am not understanding you. Below is a rough idea of my model. Yes I do realize I need more than one table for the entire thing to work. My question was specific to the uniform number issue and the best way to handle it. Thanks again.
All the stats.......
Uniform_number <============this is the issue