-1

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

AymDev
  • 6,626
  • 4
  • 29
  • 52
Daniel Martinez
  • 459
  • 3
  • 13
  • 1
    Just to be sure I understand: you want to be able to get a list of players per team regardless of their position ? – AymDev Dec 07 '21 at 13:11
  • Exactly, sometimes same player might be placed in a different position. Sometimes I will need to find teams by players regardless of their position. – Daniel Martinez Dec 07 '21 at 13:15
  • 1
    Don't _store_ `Eric-John-Mark`, instead _compute_ it using `GROUP_CONCAT()`. – Rick James Dec 08 '21 at 22:53
  • ["1NF" has many meanings.](https://stackoverflow.com/a/40640962/3404097) (All involve replacing some table with parameterized structure by some table(s) with a column per parameter.) So do "unnormalized" & "UNF" & "0NF" & for that matter "relation". So you need to tell us your definitions & preferably also textbook name & edition. And how is it you are stuck telling whether you have violated your chosen "1NF"? – philipxy Dec 08 '21 at 23:32

1 Answers1

1

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.

AymDev
  • 6,626
  • 4
  • 29
  • 52