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?