How do I normalise this so there are no repeated values in the primary key.

Question

0.00/5 (No votes)

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 27-Nov-19 3:51am

Heather14637354

Updated 28-Nov-19 3:07am

v3

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

k5054 · Answer 1 · 2019-11-27T05:59:00

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.

Posted 27-Nov-19 5:59am

k5054

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)