|
Hi,
I have question on usage of composite primary keys. I know it is suggested not to use them for performance reasons (wide indexes). But when it comes to enforcing the integrity constraints, composite keys help me.
Scenario:
I have the following tables.
Projects and Employees.
These tables share a m:n relationship and there is an intermediary table called "ProjectAssignments" with the following structure.
ProjectID, EmployeeID, EmpRole, AssignedBy, AssignedOn. (Primary Key: ProjectID & EmployeeID)
Employees with reviewer role, review the tasks of their reviewees on the specified date. This is captured with the following structure.
ReviewID, ProjectID, EmployeeID_Reviewer, EmployeeID_Reviewee, ReviewDate, TaskDesc. (PrimaryKey: ReviewID)
I would like the design to enforce the constraint that reviewer and reviewee are on the same project. I have defined the foreign keys as follows.
FOREIGN KEY([PRJ_ID], [EMP_ID_Reviewee]) REFERENCES [dbo].[ProjectAssignments] ([AGN_PRJ_ID], [AGN_EMP_ID])
FOREIGN KEY([PRJ_ID], [EMP_ID_Reviewer]) REFERENCES [dbo].[ProjectAssignments] ([AGN_PRJ_ID], [AGN_EMP_ID])
Is this is a good design?
The other alternative I thought has the following design. But, this does not enforce the above mentioned constraint.
Table ProjectAssignments: AssignID, ProjectID, EmployeeID, EmpRole, AssignedBy, AssignedOn. (Primary Key: AssignID)
(Unique Constraint: ProjectID, EmployeeID)
Table ProjectReviews:
ReviewID, AssignID_Reviewer, AssignIDReviewee, ReviewDate, ReviewTask. (Primary Key: ReviewID)
Which one should I prefer and why? I
Thanks,
AGL
|
|
|
|
|
I prefer the second one. I assume that the reviewer is reviewing an employees actions on a particular project so I would revamp the ProjectReviews to look like this:
ReviewID (PK),
AssignID (FK - ProjectAssignment),
ReviewerEmployeeID (FK - Employee),
RevieweeEmployeeID (FK - Employee),
ReviewDate,
ReviewTask
The reason I would choose your second method is for maintenance reasons. I could look at the column structures and pretty much tell what links to what without having look too deeply into the FK constrants. This makes things much easier 2 years down the road.
I would enforce the relationship of the Reviewer/reviewee & AssignId in the ProjectReviews via a stored proceedure (or trigger). You could add deeper FK relationships to manage this for you but, the nature of the data does not seem to make it necessary. If a reviewer gets hit by a car and is removed from a project, is it necessary to remove all of his/her reviews? Only you can answer questions like that.
If you are expecting millions of records, then the second method will execute joins faster because of the smaller keys. If your database is small, the time saved will not be noticeable.
|
|
|
|
|
Thanks Mike. As you suggested, I chose the second option.
|
|
|
|
|
this is the msg that show when iam trying to apply this code
Dim f1, f2, f3, f4, f5, f6, f7, f8 As String<br />
<br />
f1 = "#" & DateTimePicker1.Value & "#,"<br />
<br />
f2 = "'" & ComboBox1.Text & "',"<br />
<br />
f3 = "'" & ComboBox2.Text & "',"<br />
<br />
f4 = "'" & TextBox1.Text & "',"<br />
<br />
f5 = "'" & TextBox2.Text & "',"<br />
<br />
f6 = "'" & TextBox3.Text & "',"<br />
<br />
f7 = "'" & TextBox4.Text & "',"<br />
<br />
f8 = "'" & Label10.Text & "'"<br />
<br />
s = "insert into sale"<br />
s = s & " (dte,op,nos,cost,coms,nii,npp)values "<br />
<br />
s = s & "(" & f1 & "," & f2 & "," & f3 & "," & f4 & "," & "," & f5 & "," & "," & f6 & "," & f7 & "')"<br />
<br />
cmd.CommandText = s<br />
cmd.ExecuteNonQuery()<br />
MsgBox("Saved succssefuly")<br />
<br />
any suggestion ?
plz help urgent
|
|
|
|
|
dte column looks datetime datatype
magedhv wrote: s = s & "(" & f1 & "," & f2 & "," & f3 & "," & f4 & "," & "," & f5 & "," & "," & f6 & "," & f7 & "')"
change the query to
s = s & "('" & f1 & "'," & f2 & "," & f3 & "," & f4 & "," & "," & f5 & "," & "," & f6 & "," & f7 & "')"
so that the date is incloded in single quotes
Regards
KP
|
|
|
|
|
Hi all,
i am using SQL server 2000 , i have created 3 database. Among these 3 one database is running slow.for each database atmost there will be 40 connections open & remaining databases are running fast.
Can any body help me to solve this problem.
Thanks
Raju.
|
|
|
|
|
|
raju_reddy wrote: for each database atmost there will be 40 connections open
Won't you close connection after database operations ?
|
|
|
|
|
Following-on from Pete's reply, you might want use the sp_who2 stored procedure to check if any users hold locks that are blocking other users.
|
|
|
|
|
HI
How to use insert and update query in a single stored procedure
if u know plz let me know too
Thanks in advance
Haritha
|
|
|
|
|
what exactly you are looking for
procedure can be created with both insert & update statements one after other.
Regards
KP
|
|
|
|
|
Hi
You can write any queries in stored procedures. Your procedure could be some thing like this
CREATE PROCEDURE InsertUpdateProcedure
AS
INSERT INTO TABLE VALUES(VALUE)
UPDATE TABLE SET COLUMN = VALUE WHERE CONDITION
GO
First insertion will be done and after that updation
Happy Programming
|
|
|
|
|
I have have 2 table on my database,
table 1
nama test_type
-- --
table 2
test_type value
-- --
I'm doing sql command and the result is correct, below:
name test_type value
a 1A 0.01
a 2A 0.1
a 3A 0.03
b 1A 0.2
b 2A 0.2
b 3A 0.05
But, how can I display as below (assume that table 1 and 2 is read only)
name 1A 2A 3A
a 0.01 0.1 0.03
b 0.2 0.2 0.05
Thanks lot !!!
|
|
|
|
|
SELECT name,
CASE WHEN test_type = '1A' THEN value ELSE 0 END AS '1A',
CASE WHEN test_type = '2A' THEN value ELSE 0 END AS '2A',
CASE WHEN test_type = '3A' THEN value ELSE 0 END AS '3A'
FROM tableA
works for SQL Server
Regards
KP
|
|
|
|
|
Hi Krish, after I run your code here the result:
name 1A 2A 3A
a 0.01 0 0
a 2A 0.1 0
a 3A 0 0.03
b 0.2 0 0
b 0 0.2 0
b 0 0 0.05
so, there is any code for displaying table like below?:
I still can't find it.
name 1A 2A 3A
a 0.01 0.1 0.03
b 0.2 0.2 0.05
Thanks a lot !!!
|
|
|
|
|
Yes is possible actually i've missed out using group by
SELECT name,
Sum(CASE WHEN test_type = '1A' THEN value ELSE 0 END) AS '1A',
Sum(CASE WHEN test_type = '2A' THEN value ELSE 0 END) AS '2A',
Sum(CASE WHEN test_type = '3A' THEN value ELSE 0 END) AS '3A'
FROM tableA
GROUP BY name
regret for delay. was not in station
Regards
KP
|
|
|
|
|
Ok Krish, it's work fine, thanks !!!
BTW, it's not work if value on decimal and
data type is character right ??? so data type must in float
or another type decimal.
I'm still looking on internet for tsql converting
character into float, still didn't found it.
But, your help much welcome.
B.regards
|
|
|
|
|
CAST ('123.234' AS Decimal(10, 2))
CONVERT(Decimal(10, 2), '123.234')
Regards
KP
|
|
|
|
|
OK Krish you great !!!
I try this code, and it's work.
But after few days, my database is update with more than one record in the same name,
I not tell you that I have another column name 'date',
Regarding I just want to display data depend on last date, like below:
name type value date
-----------------------------------------------------
a A 1 06/10/2007
a B 1 06/10/2007
a C 1 06/10/2007
a A 2 06/13/2007
a B 2 06/13/2007
a C 2 06/13/2007
the code is:
select name,date=max(date),
sum(case when type='A' then value else 0 end) as 'A',
sum(case when type='B' then value else 0 end) as 'B',
sum(case when type='C' then value else 0 end) as 'C'
from table1
group by name
the result is:
name date A B C
-----------------------------------------
a 06/13/2007 3 3 3
I'm expecting like this:
name date A B C
-----------------------------------------
a 06/13/2007 2 2 2
Thank you.
B.regards
|
|
|
|
|
Hi
I created two tables in my database as
topcs(topicid,topic,createdate,authorname)
posting(postid,content,postcreatename,postdate,topicid)
now i want to display all records in topics and( number of posts ,last post name, last post date) of each topic
how to write query for this plz help me
-- modified at 1:38 Thursday 5th July, 2007
Haritha
|
|
|
|
|
can you pls provide sample data for these tables
Regards
KP
|
|
|
|
|
Hi
In my database i created two tables . One table contains topicid as primary key and another table contains same field as foreign key . Now i want to count the records of each topic in second table and i have to display first table information in grid along with count of corresponding topic .
how to write query for this .....plz help me
Thanks in advance
Haritha
Haritha
|
|
|
|
|
SELECT topicid, count(*)
FROM tblA a
INNER JOIN tblB b ON a.topicid = b.topicid
GROUP BY topicid
this query works in SQL Server
-- modified at 3:31 Thursday 5th July, 2007
Regards
KP
|
|
|
|
|
Try this.. u may get some ideas.. (I haven't tested this code yet.)
<br />
SELECT t1.Field1, t1.Field1, COUNT(t2.Field2) <br />
FROM table1 t1 <br />
INNER JOIN<br />
table2 t2<br />
ON t1.Field1 = t2.Field1<br />
GROUP BY t1.Field1, t1.Field1<br />
<code> <br />
<br />
<br />
<br />
<div class="ForumSig">Thanks and Regards,<br />
Michael Sync ( Blog: <a href="http://michaelsync.net">http:
<br />
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message". Why vote? Plz Read <a href="http://www.codeproject.com/info/supporter.asp#supporters">it</a> here. Thank you. :) </div>
|
|
|
|
|
When I call the DB2 procedure GET_DBSIZE_INFO by the code following, the application always catch error as : ERROR [55032] [IBM] SQL1224N, and then the db2 server automated shutdown.
string connstr = String.Format("DATABASE ={0};UID=db2admin;PWD=db2admin", DBName.Trim());
DB2Connection conn = new DB2Connection(connstr);
DB2Parameter[] paras = new DB2Parameter[]{new DB2Parameter("@SNAPSHOTTIMESTAMP",DB2Type.Date),
new DB2Parameter("@DATABASESIZE",DB2Type.BigInt),
new DB2Parameter("@DATABASECAPACITY",DB2Type.BigInt)
};
paras[0].Value = DBNull.Value;
paras[1].Value = DBNull.Value;
paras[2].Value = DBNull.Value;
paras[0].Direction = ParameterDirection.Output;
paras[1].Direction = ParameterDirection.Output;
paras[2].Direction = ParameterDirection.Output;
conn.Open();
DB2Command com = new DB2Command("GET_DBSIZE_INFO", conn);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddRange(paras);
com.ExecuteNonQuery();
DBSizeInfo sizeinf = new DBSizeInfo();
sizeinf.Size = (int)paras[1].Value;
sizeinf.Capacity = (int)paras[2].Value;
conn.Close();
Nothing is impossible
|
|
|
|
|