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
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:
| team_id | player_id | type |
|---|---|---|
| 1 | 12 | goal_keeper |
| 1 | 15 | position1 |
| 2 | 12 | position_2 |
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.