Home > front end >  SQL First Normal Form (1NF) vs Serialisation
SQL First Normal Form (1NF) vs Serialisation

Time:12-08

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:

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.

  • Related