How to determine a foreign key

[Pierre_M]

I’m building a Project management DB, learning as I go along.
Is it better for tblProject to point to ClientID, TaskID, SupplierID, or is it better for tblClient, tblTask and tblSupplier to point to ProjectID? Why?
Thanks

[Villeroy]

Let me guess:

• Each project has one client.
• Each project has many tasks.
• Each project has many suppliers.

That gives 4 lists of clients, tasks, projects and suppliers plus 2 mapping tables for the many-to-many relations:

CREATE TABLE CLIENTS(
    N VARCHAR(30) NOT NULL,
    ID INT IDENTITY
);
CREATE TABLE PROJECTS(
    CID INT NOT NULL,
    N VARCHAR(30) NOT NULL,
    ID INT IDENTITY,
    FOREIGN KEY (CID) REFERENCES CLIENTS (ID)
);
CREATE TABLE SUPPLIERS(
    N VARCHAR(30) NOT NULL,
    ID INT IDENTITY
);
CREATE TABLE TASKS(
    N VARCHAR(30) NOT NULL,
    ID INT IDENTITY
);
CREATE TABLE PROJECTS_SUPPLIERS(
    PID INT, 
    SID INT,
    PRIMARY KEY (PID,SID),
    FOREIGN KEY (PID) REFERENCES PROJECTS(ID),
    FOREIGN KEY (SID) REFERENCES SUPPLIERS(ID)
);
CREATE TABLE PROJECTS_TASKS(
    PID INT, 
    TID INT,
    PRIMARY KEY (PID,TID),
    FOREIGN KEY (PID) REFERENCES PROJECTS(ID),
    FOREIGN KEY (TID) REFERENCES TASKS(ID)
);

[Villeroy]

For a quick draft, I run the above code in Tools>SQL, click View>Refresh tables, add a listbox query for each list and start creating forms.

Projects form with 2 subforms:
ask129347.odb (14.2 KB)

[Pierre_M]

That’s a great start, thanks! Just to clarify, each project is separated in “Divisions”, each with their own order dates, delivery dates, etc., but all the divisions share the same suppliers. How can I have different Tasks for each Division?
Basically, I need the “Task” List Box to change according to the selected “Client” for each project (renamed to “Division”). I’ve tried to modify the Relationships, but I can’t quite get it to do what I want.

[keme1]

Formally

AFAIK, old school “normalizing” and “normal forms” (NF) is still the gospel of relational database integrity. What I write below is based on that assumption. Note that there is a learning threshold.

Third normal form or Boyce-Codd normal form (often referenced as 3NF/BCNF) is a reasonable target level.

A fairly formal/abstract/theoretical description of the path to 3NF/BCNF can be found here and in the links noted there as prerequisite knowledge. There are many other resources out there, with different mixes of practical vs. theoretical approach.

In practice

When normalizing, you “decompose” your data structure into sub-tables for every new logical level of hierarchy/dependency. The “dependent” usually has a foreign key which links to the primary key of the “parent”. Simple, “flat structure” data tables may already satisfy BCNF. In that case no decomposing is required.


Some examples:

• If a project is always for one customer, and any customer may have multiple projects, the customer is “parent” and the project is “dependent”. This is also called “1-n relationship”. The project table has one field which points to the customer primary key.

1-n relationships are the most common type. There are other types.

• If you need to track a project’s use of resources, typically a project can use several resources and a resource can be used by several projects. This is what we call “m-n relationship”. In relational databases this is solved by an auxiliary table with two foreign keys, pointing to project and resource, respectively. The primary key of this auxiliary table is often a composite key made up of the two foreign keys.
• If you allow customers to log in to e.g. edit their own contact info, their login account info will typically be stored in a separate table. There will at most be one such account entry for each customer, and the account will pertain to only one customer. This is a 1-1 relationship. Typically, the same field will be foreign key and primary key.

While I mentioned the “typical” key cases above, there will be instances where it is pertinent to use other configurations, often using what is known as “candidate keys”. The requirement you outlined seems fairly straightforward, so the “typical scenario” is likely to work for you.

How to proceed

What I have provided is only a sketch. I mentioned the learning threshold.
Look for tutorials. There are videos and written material out there.


Some search terms:

• normal form
• relational database
• integrity
• redundant data

Good luck!

[CRDF]

@Pierre_M

How can I have different Tasks for each Division?

First of all you need to draw the DB schema on paper.
A priori:

1. Table PROJECTS is “parent” (1 to n) to DIVISIONS.
2. TASKS x DIVISIONS relate to each other via an intermediate table say “TD” (m to n relationship).

@Pierre_M

for each project (renamed to “Division”).

No, not renamed. Divisions are “childs” of Projects, and clients relate to them via the PROJECTS table.
Clients are clients of some Project, not Division of…

@Pierre_M

I’ve tried to modify the Relationships, but I can’t quite get it to do what I want.

First of all get it on pencil + paper.

[Villeroy]

Say yes to pencil and paper (don’t forget the eraser).
Less abstract description of the normalization process: Database normalization description - Microsoft 365 Apps | Microsoft Learn