While designing a database system for a personal project, I have realized that this seems like the best solution. However, I feel that it might not be a good idea, though I don't know why.
It makes sense if you know you will always have exactly one record for that date. If there’s any circumstance or possible future where you might end up with multiple, then don’t. Also consider any implications of that for foreign keys referencing that table.<p>There’s also nothing wrong with using an integer or UUID PK as an additional field even on a table that has another field that would make a natural primary key.<p>Date as primary key is a pattern sometimes used in star schemas for OLAP databases, though separate y/m/d tables are more common afaik.
Does a date meet the requirements for choosing a primary key?<p>- Unique<p>- Irreducible (i.e. no part of the candidate key also qualifies as a primary key)<p>- Stable (does not change over time)<p>- Simple (a built-in type of the database engine qualifies)<p>So, yes, a date meets the requirements.<p>In a properly normalized schema all of the attributes (fields, columns) in the relation (row) <i>depend on</i> the primary key.<p>You describe a table for a newsletter published no more than once per day. In that case the date makes a good primary key.<p>Note that a date value for primary key is logically equivalent to an auto-incrementing integer surrogate key, assuming uniqueness holds (one newsletter per day). The advantage of the date is that it means something, whereas a surrogate key has no meaning, it exists only to satisfy the uniqueness requirement (and often indicates something went wrong when normalizing the schema).<p>If you might ever post revisions on the same day and want to indicate that you would have to add that information to the primary key, e.g. (published-on DATE, revision INT) to maintain uniqueness.<p>Date, date/time, and timestamp attributes either implicitly or explicitly refer to a specific timezone. That may or may not matter in your case (I suspect it doesn't), but timezone conversions and confusion give one reason a date or date/time value may not make a good key.
Both ULID and UUID v7 have a time code component which can be extracted.<p>It would be best for indexing to store the actual value in binary, though not strictly necessary as these later UUID standards (unlike conventional UUIDs) use time code prefixes (so indexing clusters.)<p><a href="https://uuid7.com/" rel="nofollow">https://uuid7.com/</a><p><a href="https://github.com/ulid/spec">https://github.com/ulid/spec</a>
In general, no, primary keys are unique. So, unless your table is something to record data about specific dates with a unique row for each date then it generally doesn't work to make it a primary key.