Database Table Design Restructuring for three related tables

Question

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?

usually the is only one id so id =event_id. and you if one project can have many risks you need a bridge table — nbk, Aug 24 '21 at 22:15
@ Raphael, Id is the unique key for that table, because I non destructively add records with actions taken (Create, Update, Delete) in additional columns to record historical context and action performed by a particular user — Vahe, Aug 24 '21 at 22:17
@nbk, I deleted my bridge entity comment but I had an idea towards that approach — Vahe, Aug 24 '21 at 22:17
@nbk, please clarify, one risk has (multiple) at most 5 events, should I be using id instead of project_id, risk_id, event_id.. (I hope this is not going overboard) to look up an event — Vahe, Aug 24 '21 at 22:21
Declaring a FK tells the DBMS that subrow values somewhere appear elsewhere as UNIQUE. If that's not so, why do you want to declare a FK? (Rhetorical.) You aren't explaining what you want or why or how you are stuck. Your "understanding" & goal/problem are not clearly described & seem misconceived. Follow a published information modeling & DB design reference/textbook & ask 1 specific researched non-duplicate question re how you are 1st stuck/unsure. — philipxy, Aug 24 '21 at 22:48

score 1 · Answer 1 · answered Aug 24 '21 at 22:17

1

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

answered Aug 24 '21 at 22:17

Raphael PICCOLO

2,095
1
12
18

That is what I was thinking and as such, my design relies upon a double arrow linkage, columns needing to be in the constraint, that was my first and major vague point – Vahe Aug 24 '21 at 22:18

score 1 · Answer 2 · answered Aug 24 '21 at 22:22

1

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) |
+---------------+-------------+

answered Aug 24 '21 at 22:22

Bill Karwin

538,548
86
673
828

As I stepped back I considered that I might in the domain of violating the transitive dependency requirement. When is it necessary that I not use a non essential column like id in my case? Maybe I should ask should I use a key when using historical records like my 3 tables in this example. – Vahe Aug 24 '21 at 22:40
In my case does the anomaly, which I did not consider, mean I am mutating the values of the event in a way that violates entity integrity? My id is a unique auto incrementing value that I have a pattern of adding, in my case ProjectID is repeated because I have a column in all three tables called IsHistory to store the record of changes to the tables. ID is unique always for each table (each table is essentially a current value holder and historical log that is) – Vahe Aug 24 '21 at 22:46
1

I would probably choose to store the history in a separate table, so the Project table only stores the current state. If you store multiple rows in Project with the same ProjectId value, then you can't declare a unique key on ProjectId. This is important because a foreign key should reference only one unique row. – Bill Karwin Aug 24 '21 at 23:23
My RiskID is used for means to display a friendly sequential id to the user for every project, say risk 1-5 goes to project 1, risk 1-5 goes to project 2, but the main id is still unique, if needed at all – Vahe Aug 25 '21 at 01:59
Then it sounds like `RiskId` will have duplicates. Well, you could reference the auto-increment `Id` instead. But a foreign key should reference a primary key or unique key. – Bill Karwin Aug 25 '21 at 02:56
If I use a ranking feature (incrementing variable) for a derived column, id in my application query for both risk, and event, specifically, and capture the incrementing values of each risk (sorted by its candidate key (projid, riskid)), and event that are sorted in order of its candidate key (projid, riskid, eventid)), I can get user friendly ids. Events will have a compound key (ProjectID, RiskID, EventID) since each of the keys of the composite's candidate keys (ProjectID, RiskID, EventID) are foreign keys themselves. Risk will have a compound key (RiskID, ProjectID). ProjectID = PK. – Vahe Aug 25 '21 at 10:16
Ok I guess you will do that. Why did you ask this question on Stack Overflow? – Bill Karwin Aug 25 '21 at 16:20
To be honest, I had not properly defined table structure and needed help, I suppose I can or should transfer the question to Database or another branch of stack exchange which I suppose is another method of getting help. I am not the greatest in database concepts and am using this exercise as an example to incorporate that knowledge into my abilities overall. – Vahe Aug 25 '21 at 18:52

score 1 · Accepted Answer · answered Aug 24 '21 at 22:59

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

Thank you for the helpful suggestion, I will need to limit my question to database design, my largest uncertainty, mainly for this thread, but normalizing my existing tables is another issue which was covered. The key (Id of each table) was there prior to my decision to add historical context to each table. Thank you! — Vahe, Aug 24 '21 at 23:05

Database Table Design Restructuring for three related tables

3 Answers3