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: --CELKO-- <71062.1056_at_compuserve.com>
Date: 20 Sep 2002 08:49:41 -0700
Message-ID: <c0d87ec0.0209200749.4a6452d9@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.

>> 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 - 10:49:41 CDT

Original text of this message

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