Re: Database Design - Relationsships

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 21 Sep 2002 13:49:40 -0700
Message-ID: <c0d87ec0.0209211249.24f54963_at_posting.google.com>

>> ... justifications for implementing three-valued logic is to
eliminate sentinel values of this sort ... A project number is not just unknown, it is also inapplicable. <<

SPARC had a list of 20+ different kinds of missing values that could occur in a database, besides Codd's two NULLs. I still like a sentinel values (and that term; I had only used in procedural programming to mean a dummy value in the data used to signal the program of some event).

The reason is that I can cover more situations with them. Example; ICD disease codes use 000.000 for "undiagnosed, just admitted and we are still looking for his insurance card" and 999.999 "diagnosed, but we ain't got a clue what is wrong and you're probably going to die" -- if I cna be a bit lose with the actual translations. Two very different missing data situations, both are important and not in the same class of events.

>> As a side note, this problem is also a good example of the issues
associated
with using identity values as you previously pointed out. <<

Thank you! I get so much hassle for fighting against IDENTITY, GUID, et al in the newsgroups, you'd think I was a war criminal.

>> If we think about the attributes of a project that make that
project uniquely identifiable such as (location, manager, begindate, etc) then thinking about inserting a sentinel seems more silly & obviously wrong. If the pk for projects was the composite above would we consider using 'nowhere', 'nobody',1492-01-01,...) knowing that we would never work in a place called nowhere with a manager named nobody etc, etc. In this particular case the job number might represent some actual object in the business process, but for analyzing the problem at hand it is helpful to define a job in more concrete terms as I proposed. <<

That is not how I read it. We have our people doing stuff for customers, but without a project number for tracking and billing. We have some information! If we had a dummy ("pro bono", "non-billable", etc.) project, then the timesheet would be complete, like 000.000 on a medical intake form.

>> It seems like what the poster needs is a table for jobs and a table
for
projects where some jobs are associated with projects and some are not. <<

That could work ... A project is one or more jobs, some jobs are scheudleed and some are pro bono:

CREATE TABLE Customers
(cust_id INTEGER NOT NULL PRIMARY KEY,
 ...);

CREATE TABLE Employees
(emp_nbr INTEGER NOT NULL PRIMARY KEY,
  ...);

CREATE TABLE Projects
(project_nbr INTEGER NOT NULL PRIMARY KEY,  proj_description VARCHAR(30) NOT NULL,
  ...);   

CREATE TABLE Jobs
(project_nbr INTEGER NOT NULL

             REFERENCES Projects(project_nbr)
             ON UPDATE CASCADE
             ON DELETE CASCADE,

 job_nbr INTEGER NOT NULL,
 job_type CHAR(1) NOT NULL DEFAULT 'B' -- billable, free, etc.

          CHECK (job_type IN ('B', 'F')),  job_description VARCHAR(30) NOT NULL,
  ...,
   PRIMARY KEY (project_nbr, job_nbr));

 CREATE TABLE ProjectAssignments
 (cust_id INTEGER NOT NULL

          REFERENCES Customers (cust_id)
          ON UPDATE CASCADE
          ON DELETE CASCADE,
  project_nbr INTEGER NOT NULL DEFAULT 0
              REFERENCES Projects(project_nbr)
              ON UPDATE CASCADE
              ON DELETE CASCADE,
  emp_nbr INTEGER NOT NULL
          REFERENCES Employees(emp_nbr)
          ON UPDATE CASCADE,

  PRIMARY KEY (cust_id, project_nbr, emp_nbr));

At least I can leave a note about the non-billable job in the jobs table and not lose data. Received on Sat Sep 21 2002 - 22:49:40 CEST