Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-07-02 : 11:03:04
|
| Anatoli writes "Hi SQL Guru! I need to guarantee the version control to department of firm. The table is:Departments( department_id int, valid_from datetime, valid_to datetime, department_desc varchar(30))The primary key should be department_id and the interval between valid_from and valid_to. How can I do it without using datetime column as pimary key?" |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-02 : 12:25:41
|
| by creating a surrogate key as the primary key, such as department_valid_id int identity not null primary key. . .<O> |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-07-02 : 12:31:37
|
| couple of things to be clarified first....based on a simple (presumed) data set of the following....1 , 15/01/2002, 31/01/2002, dept desc #1by your comments you seem to indicate that you are interested in the primary key to identify this record to be 1, 16..ie dept 1, 16 daysbut what if you also had1 , 31/01/2002, 16/02/2002, dept desc #1.1this would also give you (if my maths works out right!!) a primary key identify this record of 1, 16..ie dept 1, 16 days...which would be a duplicate!.....is this what you want/are willling to live with?but if the from/to dates (and thus the interval) was down to the milli-second that the change happened, then the chances of a duplicate occurring could be minimal and may be workable.in which case why not setup a calculated column on the table...deptid, fromdate, todate, interval, descand use deptid, interval as the primary key....seperately.....what's the problem in including the datetime in any key? it can be done..... |
|
|
|
|
|
|
|
|
datetime as primarykey