CODEWITHSUNDEEP
database-designentity-relationshipdatabase-normalization
Vahe
Vahe

Reputation: 1841

Database Table Design Restructuring for three related tables

I have three tables that need to be linked like so:

Event(RiskID) -> Risk(RiskID)
Risk(ProjectID) -> Project(ProjectID) 
Event(ProjectID) -> Project(ProjectID)

Using these tables:

Project Table

+------------+-----------+
|   ID       | ProjectID |            
+------------+-----------+

And the bottom two tables that I am trying to link via a foreign key constraint

Risk

+------------+-----------+------------+
|  ID        | ProjectID | RiskID     |
+------------+-----------+------------+

Event (aka, risk mitigation event, many events per one risk, and many risk per one project)

+------------+-----------+------------+-------+
|  ID        | ProjectID | EventID    | RiskID|
+------------+-----------+------------+-------+

From my current understanding of Relational Databases like MySQL, to create a foreign key I should be using one Primary Key in the linked table to the table that needs to reference values, and for enforcing referential integrity. How do I accomplish this constraint if all three tables have duplicate values, namely for the attribute set {ProjectID,EventID} which are as such because all my tables have a history specific column information to track changes?

This sort of approach made me have some doubts about how to create the foreign key if necessary in this case. How do I use the ID inside my foreign key but still use the {ProjectID, EventID} {ProjectID, RiskID} together...etc...

If my terminology deviates from what is understood I can revise or clarify. Hope my question (on this topic of foreign keys) has a common solution?

Upvotes: 0

Views: 132

Answers (3)

nbk
nbk

Reputation: 49373

I would add several bridge tables so that every projet kann have many risks and multipole event

I am not ver clear why you have another primary unique key, but ok evrybiody can do what they like

Every column should be only in one

project

Proj_IG(PK) | Project_ID(KEY)

event_project

Project_ID(FK) | Envent_ID(FK

event

Evnt_ID | Envent_ID....._

If the event project and risks are interliked, you could make a bridge table with three colums, so could a project could ave also mutile event and risks but tas tehy are all conected the bridge table yan represent that

risk_project

RIsk_ID Projekt_ID

risk

    R_ID | Risk_ID

project

Proj_IG(PK) | Project_ID(KEY)

event_risk project

Project_ID(FK) | Envent_ID(FK) | Risk_ID(FK)

event

Evnt_ID | Envent_ID....._

risk

    R_ID | Risk_ID

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562260

Event.ProjectID is redundant and a violation of 3rd normal form. Since a given Risk references only one Project, you could create an anomaly in the Event table if you reference a Project but then also a Risk that references a different Project. It becomes ambiguous which project the Event truly belongs to.

It looks like you have a superfluous Id in each table. What's the difference between Project.Id and Project.ProjectId? Which one should Risk.ProjectId reference? Is there a unique constraint on Project.ProjectId?

Typically you would make just one unique key per table if possible, and it would be the primary key.

So you would ultimately have something like this:

Event(RiskID) -> Risk(RiskID)
Risk(ProjectID) -> Project(ProjectID)

Project

+----------------+
| ProjectID (PK) |            
+----------------+

Risk

+--------------+----------------+
|  RiskID (PK) | ProjectID (FK) |
+--------------+----------------+

Event

+---------------+-------------+
|  EventID (PK) | RiskID (FK) |
+---------------+-------------+

Upvotes: 1

Raphael PICCOLO
Raphael PICCOLO

Reputation: 2175

foreign key doesnt need to point to a primary key

If you really want to create a foreign key to a non-primary key, it MUST be a column that has a unique constraint on it.

this thread is a bit more explicit Foreign Key to non-primary key

Upvotes: 1

Related Questions