Re: Database Design - Relationsships

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,

>> 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,

The DEFAULT will put in the dummy project number for you. Received on Fri Sep 20 2002 - 17:49:41 CEST