-1

I'm quite a beginner in general but I have a theory, idea, etc...

I want to create a task database, with a unique TaskID column [primary key or not] using the date. I need the entry to be auto-generated. In order to avoid collisions, I want to attach a number to the end, so this should achieve the goal of having all entries unique. So a series of entries would look like this:

201309281 [2013-09-28]

201309282

201309291

My thought is that I could use auto-increment that would reset at midnight EST, and start again at the given date, or something like that.

The advantage, to me, of having it work like this, is that you could see all tasks created on a given day, but then the particular task may not be completed or invoiced until, say, a week later. This way you could search by creation date, completion date, or invoice date.

I realize that there are many ways to achieve the end goal of task database. I was just curious if this was possible, or if anyone had any thoughts on how to implement it as the primary key column, or any other column for that matter.

I also want to apologize if this question is unclear. I will try to sum up here. Can you have an auto-increment column based on the date the row is created, so it automatically generates the date as a number [20130929] with an extra digit on the end in the following format, AND have that extra digit number on the end reset to "1" every day at midnight EST or UTC? And thoughts on how to accomplish?

eg: 201309291

EDIT: BTW, I would like to use an MVC4 web app to give users CRUD functionality. Using C#. I thought this fact may expand the options.

EDIT: I found this q/a on stack, and it seems similar, but doesn't quite answer my question. My thought is posting the link here might help find an answer. Resetting auto-increment column back to 0 daily

Community
  • 1
  • 1
Nick Fleetwood
  • 471
  • 1
  • 5
  • 22
  • 1
    Why not have an autoincrement column and a created date column and combine the two if and when necessary? – Ben Sep 29 '13 at 21:08
  • @Ben I thought of that, and it is an idea. I'm not crazy about it, but let's play it out. If I do that, how do I get the autoincrement column to reset to "1" every day? – Nick Fleetwood Sep 29 '13 at 21:31
  • 3
    You don't. It doesn't matter at all what the autoincrement column is. The first record of the day is the one with the minimum date. – Ben Sep 29 '13 at 21:33
  • @Ben Ah so like if we go down the line, the first record is 20130929-9876, and the next would be 20130929-9877. I suppose. My problem with that in order for a user to edit a previously created task, say to mark payment received, they would have to remember the number in the auto-increment column. My way, they would just remember the date they created the task, and then the secondary digit[s]. Also, with that way, you might as well do away with the date...no point in remembering so many digits. – Nick Fleetwood Sep 29 '13 at 21:44
  • The secondary digits are the same as having the autoincrement column. Ordered, ascending, numeric columns are the way most systems work for a very good reason. You simply give the user the number they have to put back in. If you wanted to you can add another column "job name" (or whatever) and make your table unique on creation date and job name so that a user can input some text to help them remember it later. This implies manual input which you don't have with just an autoincrement column. – Ben Sep 29 '13 at 21:51
  • @NickFleetwood DATETIME goes down to fractional seconds... so I don't see why you need a number (ID) at all to determine anything... the DATETIME alone determines that. PrimaryKey of INT is fine IMO. But I HAVE dealt with DBs that conflict on DATETIME so you will need something else as PK. Though combining fields seems like overkill and increases overall DB size. – MikeSmithDev Sep 29 '13 at 22:13
  • @MikeSmithDev That's a good thought and a possibility. I am worried about possible collisions using DATETIME, although unlikely at fractional seconds. As you point out, there can be a situation where there might be a collision, hence my thought for a type of autoincrement additional digit or something similar to eliminate the collision possibility. – Nick Fleetwood Sep 29 '13 at 22:51

3 Answers3

2

I take it you're new to db design Nick but this sort of design would make any seasoned DBA cringe. You should avoid putting any information in primary keys. The results you're trying to achieve can be attained using something like the code below. Remember, PK's should always be dumb ID's, no intelligent keys!

Disclaimer: I'm a very strong proponent of surrogate key designs and I'm biased in that direction. I've been stung many times by architectures didn't fully consider the trade-offs or the downstream implications of a natural key design. I humbly respect and understand the opinions of natural key advocates but in my experience developing relational business apps - surrogate designs are the better choice 99% of the time.

(BTW, you don't really even need the createdt field in the RANK clause, you could use the auto-increment PK instead in the ORDER BY clause of the PARTITION).

CREATE TABLE tbl(
    id int IDENTITY(1,1) NOT NULL,
    dt date NOT NULL,
    createdt datetime NOT NULL

    CONSTRAINT PK_tbl PRIMARY KEY CLUSTERED (id ASC)
)
go

'I usually have this done for me by the database 
'rather than pass it from middle tier
'ALTER TABLE tbl ADD  CONSTRAINT DF_tbl_createdt  
'   DEFAULT (getdate()) FOR createdt


insert into tbl(dt,createdt) values
    ('1/1/13','1/1/13 1:00am'),('1/1/13','1/1/13 2:00am'),('1/1/13','1/1/13 3:00am'),
    ('1/2/13','1/2/13 1:00am'),('1/2/13','1/1/13 2:00am'),('1/2/13','1/1/13 3:00am')
go

SELECT id,dt,rank=RANK() OVER (PARTITION BY dt ORDER BY createdt ASC)
from tbl
sisdog
  • 2,649
  • 2
  • 29
  • 49
  • Okay, though this is effectively what I commented you're making me cringe on so many levels. This is overly condescending firstly, misguided secondly _"Remember, PK's should always be dumb ID's, no intelligent keys!"_ what? You may have been eaten by the surrogate key monster but that's no reason to force it down everyone's throat. Natural keys are perfectly acceptable and have many uses; if there is a natural key (there isn't always obviously). Lastly, this is incorrect. You're partitioning by a datetime... which doesn't help much in finding the first record that day. – Ben Sep 29 '13 at 21:37
  • I hear you, and I understand the logic of having the PK be a dumb ID, and I'm not opposed to doing that. The task number [201309291] is a method for say a sales agent to search for a record, so the idea is that the date+1 is fairly easy to remember. The example I would give is that what if you were on your phone and you needed to edit a record? Remembering a random identifier would be hard, but remembering the date you initially created the record would be easier. So to me, the PK can be a separate dumb ID, no problem. I still want this date+1 reference field, if possible. – Nick Fleetwood Sep 29 '13 at 21:50
  • 1
    You can still do that exact search without compromising the design! If you want to let them find a record by date+rank I already wrote that query for you above. But that interface with the user is strange: so are you saying that if you're a sales rep, for the next 90 days you're going to remember to reference a deal by it's date+rank? Don't you have a UI where they can query and see their open deals? Query by name, or date, or sales size, or product, etc? – sisdog Sep 30 '13 at 02:26
  • 1
    @Ben, I'm well versed on the the natural vs. surrogate debate (http://stackoverflow.com/questions/63090/surrogate-vs-natural-business-keys) and yes, I'm a strong advocate for surrogate. Misguided? I'll humbly respect your opinion but in 25 yrs of db design work I've seen natural keys designs turn into disasters many times while I can't remember the last time a surrogate key design led to problems. I'd be happy to hear your experience. Regarding my example you're right, I typo'ed datetime instead of date but you'll notice that my data had no time component. Would you allow me one mistake? – sisdog Sep 30 '13 at 04:01
0

I would say that this is a very bad design thought. Primary keys ideally should be surrogate in nature and thus automatically created by SQL Server.

The logic drafted by you might get implemented well but due to lot of manual-engineering it could lead to lot of complexities, maintenance overhead and performance issues.

For creating PKs you should restrict yourself to either IDENTITY property, SEQUENCES (new in SQL Server 2012), or GUID (newID()).

Even if you want to go with your design you can have a combination of Date type column and an IDENTITY int/bigint column. And you can add an extra computed column to concatenate them. Resetting IDENTITY column every midnight would not be a good idea.

Manoj Pandey
  • 1,307
  • 12
  • 14
0

Ok, I found an answer. There may be problems with this method that I don't know about, so comments would be welcome. But this method does work.

CREATE TABLE [dbo].[MainOne](
[DocketDate] NVARCHAR(8),
[DocketNumber] NVARCHAR(10),
[CorpCode] NVARCHAR(5),
CONSTRAINT pk_Docket PRIMARY KEY (DocketDate,DocketNumber)
)
GO
INSERT INTO [dbo].[MainOne] VALUES('20131003','1','CRH')
GO
CREATE TRIGGER AutoIncrement_Trigger ON [dbo].[MainOne]
 instead OF INSERT AS
 BEGIN
 DECLARE @number INT
 SELECT @number=COUNT(*) FROM [dbo].[MainOne] WHERE [DocketDate] = CONVERT(DATE, GETDATE()) 
 INSERT INTO [dbo].[MainOne] (DocketDate,DocketNumber,CorpCode) SELECT (CONVERT(DATE, GETDATE    
 ())),(@number+1),inserted.CorpCode FROM inserted
END

Any thoughts? I will wait three days before I mark as answer. The only reason I'm not marking 'sisdog' is because it doesn't appear that his answer would make this an automatic function when an insert query is run.

Nick Fleetwood
  • 471
  • 1
  • 5
  • 22