Do I need IDs in my database if the records could be identified by the date?

Question

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?

Answer 1

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.

Answer 2

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!

Answer 3

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.

Answer 4

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.