SQL Table and Code Help :)

[tudorH]

Hi

I am basically designing a database to store our development team metrics...here are some of my tables. What do you guys think? I am having a lot of trouble coding this on SQL Server so that question is asked towards the bottom :).

ProductTable:
productID (PK)
productDescription

SprintTable:
sprintID (PK)
sprintDescription
success
estimateAccuracy
availableHours

ProductTransTable:
productID (PK,FK)
sprintID (PK,FK)
points
incidents
devHours

CustomerValueTable:
serviceTypeID (PK)
productID (PK,FK)
sprintID (PK,FK)
billedHrs
sales

*I need the CustomerValue table to be identifiable by all three serviceTypeID, productID and sprintID, is this the best way to do it?

ServiceType:
serviceTypeID (PK)
serviceTypeDescription

Ok now here is my SQL code to do this in SQL server and the error I get:

CREATE TABLE Product (
 productID int NOT NULL,
 productDescription varchar(50) NOT NULL,

 PRIMARY KEY (productID)
);

CREATE TABLE Sprint (
 sprintID int NOT NULL,
 sprintDescription varchar(25) NOT NULL,
 sprintSuccess varchar(5) NOT NULL,
 estimateVariance float NOT NULL,
 estimateDeviation float NOT NULL, 
 availableHours float NOT NULL,

 PRIMARY KEY(sprintID)
);

CREATE TABLE ProductTrans (
 productID int references Product(productID) NOT NULL,
 sprintID int references Sprint(sprintID) NOT NULL,

 points float NOT NULL,
 incident int NOT NULL,
 devHours float NOT NULL,

 PRIMARY KEY(productID, sprintID)
);

CREATE TABLE ServiceType (
 serviceTypeID int NOT NULL,
 serviceTypeDescription varchar(50) NOT NULL,

 PRIMARY KEY (serviceTypeID)
);

CREATE TABLE CustomerValue (
 serviceTypeID int references ServiceType(serviceTypeID) NOT NULL,
 productID int NOT NULL,
 sprintID int NOT NULL,

 billedHours float NOT NULL,
 sales varchar(5) NOT NULL,

 CONSTRAINT productID_FK FOREIGN KEY (productID) REFERENCES ProductTrans(productID),
 CONSTRAINT sprintID_FK FOREIGN KEY (sprintID) REFERENCES ProductTrans(sprintID),

 PRIMARY KEY(sprintID, productID, serviceTypeID)
);

Now the error I get is only when I execute the CustomerValue code...I have tried recreating everything, I have tried doing it all one by one and I'm still getting this error....

"There are no primary or candidate keys in the referenced table 'ProductTrans' that match the referencing column list in the foreign key 'FK__CustomerV__produ__19DFD96B'."

I can see that my keys are in the ProductTrans table but still it can't seem to want to reference them. What am I doing wrong? I'm getting kind of worried about this issue now :(...

This is how my tables look in SQL Server Management Studio
http://i828.photobucket.com/albums/zz208/TooT3R/SQL_Issue.png

[BitBlt]

The problem is that your primary key in ProductTrans consists of TWO columns, not one. Therefore, you either have to change your foreign key references in CustomerValue to point to Product and to Sprint, or you need to combine them into a single constraint pointing to ProductTrans with both columns.

[tudorH]

Yeah, I figured it out. This is what you mean right?

CONSTRAINT productID_FK FOREIGN KEY (productID, sprintID) REFERENCES ProductTrans(productID, sprintID

[BitBlt]

Yep, you got it! Except for the missing close-parenthesis at the end... :-)