Lets say I have a football team table, where player names are tied to positions:

teamId: 1
goalkeeper: 'Marc'
position1: 'John'
position2: 'Erik'
...

But now I also want one attribute that represents the players NOT taking into account positions. I would create a new attribute teamString serialising all players sorted alphabetically (this will ensure that different Teams with same players will have the same teamString attribute):

teamString: Eric-John-Mark[...]

Now I can filter Teams with same players even if they play in different positions.

Will this new attribute teamString be against 1NF principle?

Thanks in advance

CodePudding user response：

Your teamString attribute would be a violation of the 1NF as your teamString property would contain multiple values. And it would reference values existing in non-key attributes of the same entity (maybe a 3NF violation but I'm not sure about it).

The issue here is that you treated specific positions in a team as attributes of the team and not as a relation.

I'd make a relation between team and player:

team (1, n) - (0, n) player

A team may have one to many players. A player may play for zero to many teams.

As both max cardinalities are to n you'd get a Many To Many relation which implies a join table with foreign keys from both sides (the team id player id). In this table you can add a column for the position type.

This means you should get rid of the position columns (goalkeeper, position1, ...) in the team table.

The position table could look like:

Then the application could be responsible for checking that a team has only a limited number of players for a specific position. But for modeling you should stick to the 0, 1 & "n" values that you use in cardinalities.