I have seen some material, including on Stack Overflow, which suggests that some redundancy is to be expected in real world databases so I thought I would seek advice on this case.

I have been learning about databases from a book in which one of the example databases seems to have some redundancy in the schema and there is some inconsistency in the data which presumably would not be possible if the redundancy was removed.

I would like to know if I have identified this correctly and if my proposed solution would be sensible both from a theoretical and real world perspective.

The two tables of interest in the database are Teams and Bowlers. These are shown below with a few rows selected from those in the actual database. The schema diagram for the database indicates that CaptainID in Teams is a foreign key referencing BowlerID in Bowlers.

Teams:

Bowlers:

It seems the data must be inconsistent as the bowler with BowlerID = 7 is in the team with TeamID = 2 but is Captain of the team with TeamID = 9. It is unlikely that would be an allowable situation (from my limited knowledge of Bowling).

If I am right about that then I was wondering if a good theoretical and practical solution to this would be to remove the CaptainID column from the Teams table and create a new table called Captains which would just have two foreign keys as columns. These would be TeamID and BowlerID from the Teams and Bowlers tables respectively.

One thing that struck me about the current database design is that it seems necessary to have a different name for the foreign key (CaptainID) than the key in Bowlers (BowlerID) that it references. That is because only some Bowlers are captains so if you used BowlerID instead of CaptainID as a name then it would be difficult to know what it actually meant. By implementing a new table (Captains) one can use the same name for the foreign keys inside it as are used in the tables to which they relate. I was wondering if that was a generally applicable fact in database design which is worth trying to adhere to?

CodePudding user response：

On the names of pk fields; i tend to use Id, an for an Fk playerId or captainPlayerId. There is no technical restriction, as long as they make sense.

For the captainid in teams, this is not fully normalized as it is dependant on the key but not only on the key (boyce cod 3rd normal form violation but going from memory here) that said this seems like a common denormalization step.

The integrity could be enforced using a trigger checking if the field is null or the corresponding player is in the same team