Click here to Skip to main content
14,639,650 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have a table which looks something like this:

ID | NAME | HOMEID | HOMENAME | TEAMID | TEAMNAME | YEARJOINED
1 | PETE | SP |   SPAIN  |     1  |   TEAM1  |  2018
2 | PAUL | FR |   FRANCE |     1  |   TEAM1  |  2018
3 | MARY | GR |  GREECE  |    2   |  TEAM2   | 2019
1 | PETE | SP |   SPAIN  |     2  |   TEAM2  |  2019
2 | PAUL | FR |  FRANCE  |    2   |  TEAM2   | 2020


Each person can join a maximum of one team per year.


I have normalised the table down to (what I think is) the BCNF:

{ID, NAME}
{HOMEID, HOMENAME}
{TEAMID, TEAMNAME}
{ID, TEAMID, YEARJOINED}
{ID, HOMEID, TEAMID}


However, I'm really confused as to how to normalise this so that there are no repeated value in the primary key for the last two tables. As far as I can work out, the way YEARJOINED is dependent on ID and TEAMID, tables will always have repeated values?

What I have tried:

I've tried splitting the tables into TEAM1 and TEAM2, i.e.:

TEAM1{ID, TEAMID, YEARJOINED}
TEAM2{ID, TEAMID, YEARJOINED}


given that each person is only able to join one team per year. However, this makes it difficult to do the SQL queries on the tables later!
Posted
Updated 28-Nov-19 3:07am
v3
Comments
MadMyche 27-Nov-19 11:47am
   
Can you provide some more context to this; would make it easier to work with if there was a bigger picture

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Assuming that a player's HOME won't change over his lifetime, then your tables could be:
{HOMEID, HOME}
{TEAMID, TEAM}
{ID, NAME, HOMEID }
{ID, YEARJOINED, TEAMID}

For the last table, you have a constraint that (ID, YEARJOINED) is unique, so you could either add that as a table constraint, or use (ID, YEARJOINED) as a compund primary key.
   
Comments
Heather14637354 28-Nov-19 7:59am
   
Please excuse my ignorance, but wouldn’t this still result in duplicate values in the primary key, because you would still need to list each person multiple times if they’ve joined more than one team?
k5054 28-Nov-19 11:31am
   
You said that "each person is only able to join one team per year". So that means that you can use (ID, YEARJOINED) as a primary key, and a primary key is, by definition, unique for a given table.
If I've misunderstood, and a player can join more than one team each year, then you will have to add a generated PK for the table (e.g an "identity" (MS SQL) or "auto_increment" (MySQL), or maybe extend the YEARJOINED to DATEJOINED (YYY-MM-DD) or DATETIMEJOINED (YY-MM-DD HH:MM:SS)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100