StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>As you say, you can pass the ProjectID down through each table. I don't think that's sloppy, and it enables you to create composite primary keys that enable reuse of e.g. TaskIDs across different projects. Once one realises that, one observes that the common practice of using <code>ID</code> columns in every table is a little superfluous. One can instead use semantically meaningful data as keys, which is usually my preference (sure it's more costly in terms of space, but the indexing results in relatively little impact on time):</p> <pre><code>CREATE TABLE Projects ( ProjectName VARCHAR(20) NOT NULL PRIMARY KEY ); CREATE TABLE JobTitles ( ProjectName VARCHAR(20) NOT NULL, JobTitle VARCHAR(20) NOT NULL, PRIMARY KEY (ProjectName, JobTitle), FOREIGN KEY (ProjectName) REFERENCES Projects (ProjectName) ); CREATE TABLE Tasks ( ProjectName VARCHAR(20) NOT NULL, TaskName VARCHAR(20) NOT NULL, ParentTask VARCHAR(20), PRIMARY KEY (ProjectName, TaskName), FOREIGN KEY (ProjectName) REFERENCES Projects (ProjectName), FOREIGN KEY (ProjectName, ParentTask) REFERENCES Tasks (ProjectName, TaskName) ); CREATE TABLE Assignments ( ProjectName VARCHAR(20) NOT NULL, TaskName VARCHAR(20) NOT NULL, JobTitle VARCHAR(20) NOT NULL, Email VARCHAR(255) NOT NULL, PRIMARY KEY (ProjectName, TaskName, JobTitle), FOREIGN KEY (ProjectName) REFERENCES Projects (ProjectName), FOREIGN KEY (ProjectName, TaskName) REFERENCES Tasks (ProjectName, TaskName), FOREIGN KEY (ProjectName, JobTitle) REFERENCES JobTitles (ProjectName, JobTitle), FOREIGN KEY (Email) REFERENCES Employees (Email) ); </code></pre> <p>Since MySQL doesn't support more powerful constraint validation, the only other option I can think of would be to define <code>BEFORE INSERT</code> and <code>BEFORE UPDATE</code> triggers on <code>Assignments</code> that raise errors to reject data that contains an invalid JobTitle. However, then you also would need to create triggers on <code>JobTitles</code> to handle situations where such referenced records change or are deleted; and then also on all the other tables that could give rise to a break in linkage. Ugly ugly ugly.</p> <p>Hence my preference is with the first approach given above.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload