Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am writing my first application for Android and will use the SQLite database so will be trying to limit the size as much as possible, but I think the question applies in general to database design.

I am planning to store records that will have text and the date of creation. The app is a stand-alone app, i.e. it will not link to the internet and only one user will be updating it, so there is no chance that there will be more than one entry with a given date.

Does my table still need an ID column? If so, what are the advantages of using the ID as a record identifier as opposed to the Date?

share|improve this question
    
SQLite will always create a integer column for rowid if you don't specify an integer PK. So don't count on not having a "ID" column as a way to save space. –  Codism Aug 9 '13 at 16:33
    
I'll add that in Android some classes need tables to have an _id column to work. More info at this SO answer. –  bigstones Aug 11 '13 at 14:40
4  
If you are getting the date from the phone itself and the user travels to an earlier time zone (and his/her phone updates the time automatically) then there is a slight chance that you can get the same time stamp more than once. –  Eugene Aug 14 '13 at 6:34
add comment

4 Answers

up vote 20 down vote accepted

IMHO, using a date column as a primary key is best avoided.

I have worked on systems where a date field is used as a primary key and writing queries to pull back subsets of the data is a bit of a drag if you're working with date fields.

Some other points you might want to consider:

You might think that a point in time is unique, but that rather depends on the granularity of the date column. Is it minutes, seconds, milliseconds etc. Can you be absolutely sure that you'll never get a primary key violation?

Finally, should you wish to migrate the database to another platform, you may again, encounter problems where the granularity of the date data differs between the platforms.

You do of course have to balance the ideal with what you have to work with. If space is really that much of a concern, using the date column could be the lesser of two evils. That is a design decision you'll have to make.

Edit:

I should point out that in no way does this indicate that it a poor design decision. Just that there could be issues with the practicalities of the RDBMS in question.

share|improve this answer
    
it's been awhile since I've written a SQLite query, but isn't filtering by dates identical to filtering by integers, aside from the more verbose declaration of the binding values? –  DougM Aug 9 '13 at 14:55
    
It is just more verbose and also on some RDBMSs you get that issue where the day and month element is reversed if the DB has been set up in US format. –  Robbie Dee Aug 9 '13 at 15:06
    
Thanks, these are all good answers, but your experience at work definitely sealed the deal. –  Nieszka Aug 10 '13 at 18:07
    
As a postscript to this: Only today I've been handed a support issue for an application audit table where they're getting a primary key violation for an employee number and access date/time PK because of a time difference between 2 client devices... –  Robbie Dee Aug 22 '13 at 13:49
add comment

No, you don't strictly need an ID column defined in your schema if you can guarantee that there will never be a duplicate date.

BUT...

...that said, you might as well use it anyways. The little secret here is that SQLite already has a unique, auto-incrementing ID for every table called ROWID. If you declare an auto-incrementing integer column in your table as a PK, SQLite will not create a new column - it will simply alias that pre-existing ROWID column.

In SQLite, every row of every table has an 64-bit signed integer ROWID. The ROWID for each row is unique among all rows in the same table.

You can access the ROWID of an SQLite table using one the special column names ROWID, ROWID, or OID. Except if you declare an ordinary table column to use one of those special names, then the use of that name will refer to the declared column not to the internal ROWID.

If a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for the ROWID. You can then access the ROWID using any of four different names, the original three names described above or the name given to the INTEGER PRIMARY KEY column. All these names are aliases for one another and work equally well in any context.

http://www.sqlite.org/autoinc.html

So, you wont be saving any space by not using an ID column since you are getting one per table whether you want it or not!

share|improve this answer
add comment

Use an ID field if any of the following is true:

  1. No natural key exist ( the date will not be unique )
  2. The date field will be changing often
  3. The date may not be known at the moment of insertion.
  4. A multicolumn identifier exceeds three columns, which would make joins too verbose.

Read this question: Is there a canonical source supporting “all-surrogates”?

Edit:

Since, in my opinion, it seems none of the above holds true, you don't need to use and ID field, but you can use one if you want.

share|improve this answer
1  
+1 ID columns are a schema code smell, indicating that your data doesn't really fit the relational model. –  Ross Patterson Aug 9 '13 at 10:53
10  
@RossPatterson I'm not so sure. I can think of a number of cases where no natural key might exist, but the data can still fit the relational model. Just one case off the top of my head: storing information about living persons. Many (not all!) countries assign unique identifiers to each citizen, but that doesn't mean that using that identifier is appropriate or even possible (it might not be known at the time of record creation, might not be assigned, or its use may be prohibited e.g. by applicable regulations). Does that mean that the data doesn't fit the relational model? I don't think so. –  Michael Kjörling Aug 9 '13 at 11:20
    
And there is the little funny fact that where there is such unique identifier, police (etc.) sometimes use duplicits for their fake IDs. And when it is not intentional, clerical error will ensure duplicities anyway. –  user470365 Aug 9 '13 at 11:29
4  
Whether it is built in (a la Oracle) or added as a bona fide column, they are very useful. As someone who has been on both sides of the fence (DBA & developer) it is far easier to dedupe a table with an id that you can guarantee will be unique. –  Robbie Dee Aug 9 '13 at 11:41
1  
@RobbieDee You are right. It's off the topic. –  user61852 Aug 9 '13 at 11:57
show 6 more comments

Keep in mind that you might also want to change meaning of "date" column from created_at to updated_at or any other change along those lines, which I find to be very common case.

Adding id column in some cases will give you more flexibility when your design changes.

share|improve this answer
    
+1 adding date_created and date_modified to tables is very useful for tracking when rows were created and updated. This is worth its weight in gold when investigating repository/data warehouse update issues. –  Robbie Dee Aug 23 '13 at 12:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.