Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Database Design - Relationsships

Re: Database Design - Relationsships

From: todhsals <todhsals_at_hotmail.com>
Date: Sat, 21 Sep 2002 03:29:12 GMT
Message-ID: <cIRi9.535043$UU1.88080@sccrnsc03>

"--CELKO--" <71062.1056_at_compuserve.com> wrote in message news:c0d87ec0.0209200749.4a6452d9_at_posting.google.com...
> In the future, would you please post DDL instead of some vague,
> personal notations? This means we don't have to guess at datatypes,
> constraints, keys, etc.
>
> Next, the tables confuse me.
>
> Easy enough:
>
> CREATE TABLE Customers
> (customer_id INTEGER NOT NULL PRIMARY KEY,
> ...);
>
> Now, we have to start guessing at rules. What is the diffrence
> between a project number and a project id? Project id looks redundant
> -- surely nobody would be slapping IDENTITY or auto-numbering columns
> into a base table!
>
> CREATE TABLE Projects
> (project_nbr INTEGER NOT NULL PRIMARY KEY,
> customer_id INTEGER NOT NULL
> REFERENCES Customers(customer_id)
> ON UPDATE CASADE
> ON DELETE CASADE,
> ..);
>
> Now TimeLog looks redundant since (project_nbr, customer_id) appear in
> Projects. It is also weird not to see a timestamp in a table with a
> name like that. You mention employees, but they do not appear in the
> schema.
>
> >> You can enter a record in the timelog table without having a
> project e.g. an employee may do a job for a customer that does not
> have any project numbers. <<
>
> create a dummy project number, say zero, for this situation.
> Otherwise, you cannot track anyone's time. It is not a NULL becuase
> you know whqt they were doing, so there is no missing value.
>

IIRC one of the justifications for implementing three-valued logic is to eliminate sentinel values of this sort. In RM/V1 the equivalent of SQL NULL is MAYBE, not specifically UNKNOWN. In RM/V2 Codd proposes four-valued logic to differentiate the possible meaning of MAYBE to UNKNOWN BUT APPLICABLE and UNKNOWN AND INAPPLICABLE. This is the case here. A project number is not just unknown, it is also inapplicable.

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

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.

Tod

> >> I want to make sure if the user picks a project it is a project
> that has the same customerid as the entered customerid in other words:
> a Time log entry should not be able to have one customerid and a
> project with another customerid. <<
>
> The goal of normalization is to avoid storing redundant data, like you
> are trying to do. Chris Date has a few papers on this topic you might
> want to look at. I'd get rid of the timelog as redundant and go with
> something like this:
>
> 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,
> ...);
> INSERT INTO Projects VALUES (0, 'non-project work', ...);
>
> 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 -- dummy project nbr
> 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));
>
> The DEFAULT will put in the dummy project number for you.
Received on Fri Sep 20 2002 - 22:29:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US