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?
Best Answer
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.