How can I generate an ID each day through java ?

[Vighnesh Mu]

Hi,

I have a requirement where I need to generate one ID(preferably integer) for each day(only one ID need to be generated per day) and persist this to a DB table which should have a composite primary key comprising of ID generated by me and one more field. Can anybody tell how can I do this?

Thanks.

[Rob Spoor]

If there is need for only one ID per day, isn't the day itself the ID? You can then turn this into an int, e.g. the ID for today is 20110215. Tomorrow's ID is 20110216, etc.

[Vighnesh Mu]

Thanks for the reply. I thought of using different ID, but as you suggested it will be good to use date as ID instead of new one. Thanks again.

[Rob Spoor]

You're welcome.

[Campbell Ritchie]

Consider a composite key including date or datetime as one part and an auto-incrementing long integer as the other component.

I think this discussion would sit better on our databases forum, so I shall move it.

[Martin Vashko]

Better yet would be to use a DATE column in the database and create a composite primary key over the DATE field and the other field you need. Then, if you ever need to query records for multiple dates, the database might be able to generate better plans for your queries. (This might depend on the database: on Oracle using DATE instead of NUMBER encoded date leads to better cardinality estimates and therefore better work of the optimizer).

To make sure that only dates will be entered (no time part), create proper CHECK constrain for it. This is important, as otherwise there could be more than one record for a day, eg. 15.2.2011 0:00, 15.2.2011 13:45 etc.

Edit: some databases might have this different, eg. DATE/DATETIME datatypes Campbell mentioned - you might do without the constraint if you have a datatype that does not permit time. However, Campbell's suggestion probably is a bit flawed, as including autoincrement number as part of the key would defeat the uniqueness of the date.

[Paul Sturrock]

Even better is not to use a composite id at all, since these tend to just be extra complications of indeterminate value. Use a surrogate key and a unique index, this way your primary key has all the assurances a primary key should have (unchanging, unique, not null) and your SQL is less complex.

[Martin Vashko]

Paul Sturrock wrote:Even better is not to use a composite id at all, since these tend to just be extra complications of indeterminate value. Use a surrogate key and a unique index, this way your primary key has all the assurances a primary key should have (unchanging, unique, not null) and your SQL is less complex.

Surrogates have their drawbacks too. For example, with composite foreign key some joins might be avoided if the query can be answered with data from the child table only.

In some databases surrogates might prevent some optimizations from happening. In Oracle better partition pruning can be achieved if the parent and child table are partitioned on the primary/foreign key in the same way. Oracle is also able to eliminate some tables from complex queries altogether without your intervention, if it can answer the question from the rest of the tables. Using surrogates everywhere reduces the possibilities of such optimizations (intermediate table that might be otherwise eliminated is needed to resolve the surrogates). These optimizations are completely transparent to the application. Of course, in other databases things might be different.

Moreover, if you ever have to inspect data in the table manually, seeing the real values instead of surrogates is much better. True, this can be easily avoided using views that do the join and show real values instead of surrogates, but then you have to maintain these views.

The complexities of the SQL with composite keys generally boil down to having to specify multiple columns in the join clauses. I'm personally well with two- or three-column composite keys. I'd hesitate creating composite key from four or more columns, because the data model would look suspicious to me. In short, there are usually pros and cons everywhere, including surrogates.

Of course, if there is a possibility that the key might be mutable, then surrogate should definitely be used. However this is true regardless of whether the key is composite or not.

[Paul Sturrock]

Good points, Martin. Though of course denormilazation achieves much of what you mention without needing to make that denormalization part of a (possibly mutable) key (though of course, that has its own drawbacks). To my mind, its the mutablility part that is the real problem with composite keys. Over the years, I've seen too many bits of nasty legacy behaviour in applications with mature data models where a key or part thereof has had business meaning that it no longer has. Working round performance issues is always possible, often without code changes, working round a data model having a key that is no longer a key is far higher impact.

[Martin Vashko]

Paul, I wholeheartedly identify myself with the mutability problem I've personally experienced much pain with a natural primary key spread over tens of child tables that turned out not to be immutable after all. By chance it was not a composite key, so my experience didn't taught me composite keys being especially prone to mutability issues. However dates in general do seem suspicious as primary keys - dates change a lot ("How do you mean I didn't meet the deadline? I've filed my request yesterday! Go back to your computer and have it corrected at once!!!").

Regarding denormalization - I avoid it like plague, but it is just me. It certainly has its uses if properly handled. Regardless, Oracle optimizations I've mentioned depend on the existence of the FOREIGN KEY constraint, without it the optimizer does not have information it needs to do them. So denormalization does not help here. If you're not on Oracle, then this is not a concern for you, of course.