I have to set up some tables which use the date as the primary key in each record.

I'd like to at least try storing the date in MySQL date string format (yyyy-mm-dd) for human readability purposes when I need to check the database through PHPMyAdmin.

I am wondering though, can I get MySQL to automatically increment that field by an extra day whenever a new record is added or would I need to use PHP to "manually" create a new primary key field for each new record?

having a date as a primary key is not a good practice, a primary key HAS to be unique. Dates are NOT unique.

I am wondering though, can I get MySQL to automatically increment that field by an extra day whenever a new record is added

What happens if two or more records are added in one day?
What if you have 1000 records starting from today? The index says it's an entry for a date 3 1/2 years from now.
What if a couple of days are missed between entries? Adding a record on the third day means the row will be dated 2 days previously.


Use a date as an indexed column by all means, but not as the primary index.

I'd like to at least try storing the date in MySQL date string format (yyyy-mm-dd) for human readability purposes when I need to check the database through PHPMyAdmin.

Dates are stored as a numeric value anyway

I agree with chrishirst, dates as primary keys are bad.

If you want to order something by a date put
"SELECT * FROM `table` WHERE WHATEVER='tree' ORDER BY `date` DESC LIMIT 0,30"

Its for a hotel room reservation system. So one record = one day.
Records are only ever added by the Administrator , or more likely populated by a PHP cron job out to whatever date the "booking horizon" might be. Records cannot be entered by users.

So "Date" seems like a logical choice for a primary key in such a scenario does it not?

Whether the date should be stored as a MySQL date string or as a UNIX time integer value as another question.

For a hotel room reservation system, surely the primary key should (at the very least!) be the date AND the room number? That is to say, if you really want the date as part of the key.

I do agree with the others, though... I wouldn't use the date as part of a key.

The only thing I've ever used as a primary key is a simple number 1, 2, 3, 4, 5, 6, etc...

surely the primary key should (at the very least!) be the date AND the room number? That is to say, if you really want the date as part of the key.

Certainly you would, but that would really be better as an INDEX NOT the primary key. IMO of course

You could use the date as a primary key, but only in the case of the hotel having one room only.

For a hotel reservation/booking system, apart from NOT using MySQL or at least using MySQL with InnoDB tables (Even small booking systems NEED transactions), using a numeric ID as primary key is the logical choice.

I think you're missing the point of primary keys and indexes. Let me briefly explain.

A primary key is a unique identifier for a single row, often used in other tables to reference that row. For example, if you had a room table and a reservation table, then the reservation table would refer to the room's primary key so that you could then get the room details while you get the reservation details by using an INNER JOIN.

An index is a column which has been treated specially by the DB engine so that information can be fetched more rapidly. For example, you'll want to quickly find the dates a room is reserved so that others do not reserve the same date, so indexing the reservation date column would speed up that query.

Let's assume that you have a database structure like this:

room
  room_number UNSIGNED SMALLINT (that gives up to 65535 rooms [room number > 0])
  beds UNSIGNED TINYTINT
  
reservation
  room_number (room's primary key)
  reservation_date
  customer_id UNSIGNED MEDIUMINT (assume a customer table exists and this is the primary key)

Now, we may want to fetch all reservations for a specific date. This query would do that:

SELECT * FROM reservation rv, room r, customer c WHERE rv.room_number=r.room_number AND r.customer_id=c.customer_id AND reservation_date='2008-12-08'

Notice how that links the tables together by their primary key? In each table which uses the primary key, you'll want to have the foreign key (the primary key from another table) indexed as well so that the database engine doesn't have to go searching through all 40,000 customers to find the one that matches or all 200 rooms to find the matching room -- instead, it uses the index to rapidly know to skip the first 25,376 customers and get to the correct one and skip the first 124 rooms to find the matching room.

Next, notice that the reservation table has 2 fields which create a unique combination: room_number and reservation_date. Creating a compound primary key of these 2 will help you speed things up. BUT, order matters. Going back to our query, we need to ask ourselves a question: Which order of the compound key will reduce the database engine's overall work. If we look at room_number and then reservation_date, then what would the database engine have to do? Well, it'd need to search through room_numbers before searching through reservation_dates to find the correct row. Since the room_number is not provided, this results in unnecessary searching. The reservation_date, however, is present in the query, so this query would run faster with the date first. However, if we have other queries in our system (VERY likely), then that convenience may not continue to hold true. Take, for instance, this query which tries to find the reservation status for a specific room:

SELECT * FROM reservation rv, room r, customer c WHERE rv.room_number=r.room_number AND r.customer_id=c.customer_id AND rv.room_number=3

The logic for that query is exactly the opposite of the first query! Now what? A compound key is not the best solution in this case! Instead, index each column separately and create a new primary key that is unique to each reservation. For example reservation_id AUTO_INCREMENT UNSIGNED INT (assuming a lot of reservations!). By making it auto-increment, it will do all the +1 stuff for you automatically. By having the separate indexes on columns, your 2 queries are then optimized.

I said "brief", but have gone on for awhile and not even performed a complete analysis, but I hope I've demonstrated that before designing your database and implementing your code, you first need to ask yourself about the underlying system and the questions it will ask of the database under typical usage. Once you know how the system will be used, you'll be better able to design a structure which will be optimized for speed. Caveat: Indexes take up additional space, so keep that in mind too -- it'd be a really bad idea to index every column.

That's a tutorial not a post Jeremy

Originally Posted by TWD

Its for a hotel room reservation system. So one record = one day.
Records are only ever added by the Administrator , or more likely populated by a PHP cron job out to whatever date the "booking horizon" might be. Records cannot be entered by users.

So "Date" seems like a logical choice for a primary key in such a scenario does it not?

In a calendar table, date is the most natural choice. Calendars are "special" in that they're normalized by definition. (And generally used for reporting.) Since the date is what uniquely defines each item of data in this table, use it as a PK and don't think twice about it.

Chris H is right, though, that MySQL probably isn't up to the task, at least unless you enable transaction support. Postgres might be a better choice, while staying free.

Originally Posted by Learning Newbie

In a calendar table, date is the most natural choice. Calendars are "special" in that they're normalized by definition. (And generally used for reporting.) Since the date is what uniquely defines each item of data in this table, use it as a PK and don't think twice about it.

Chris H is right, though, that MySQL probably isn't up to the task, at least unless you enable transaction support. Postgres might be a better choice, while staying free.

Thanks LN.
Could you elaborate a little more on what "MySQL with InnoDB" is? And why you think MySQL might not be up to the task? Is it a processing speed issue?

MySQL is the database engine (per se) and InnoDB is the storage engine. MySQL supports a number of table types, each good for various task(s). The most commonly used MySQL storage engine is MyISAM, but it doesn't support transactions. A transaction is a set of queries that must be executed together and in the event of a failure, all the changes made by the collection of queries will be reversed, leaving no messiness behind.

You are already, however, way in over your head. I'd recommend reading "MySQL: Database Design & Tuning" as a guide to how MySQL works, but you may want to start with basic database design and implementation principles.