Accepted/Declined/Finished - statuses (flag) for a record in database

which will be the best approach for a table design for keeping statuses for a product:

• Product Accepted
• Product Declined
• Product Finished (Shipped)

These are my ideas:

1. a int field for "accepted" another for开发者_Python百科 "declined" and another for "finished" (with values 0 or 1)

2. a datetime for each "accepted", "declined" and "finished" (by default the field will be NULL)

3. just a field "status" with values 1, 2 or 3.

---

If the status can only be one of those three at any time, then there should only be one field. So, your third idea would be correct. Having three fields when only one is necessary would be a waste of storage space, so should be avoided.

The best solution is to simply have a status field that holds which one of the three statuses the product is at. This will also make adding or removing a status easy since you will only have to modify the code to insert a different value, not the database itself.

If you do need the date at which the status was last changed to also be stored, you could have a field called last_status_change which is a datetime and you update to the current time whenever you change the status.

---

Another variation of your 3rd option is some enum or else. But int is always a better idea. For performance may be even tinyint (less bytes).

But you should have then some product_status (id, name) table with 3 records.

---

It depends on how detailed you want future reporting to be. Your options 1 and 3 are effectively the same, so throw out option 1 in favor of the better option 3.

Now you're left with two options, and to pick one you need to answer the following:

Do I want to know when each status was set, or just if?

Personally, I would choose option 2 just in case you want to add any detailed reporting capability in the future. The DATETIME fields will take more space than a single INT, but in most cases this isn't a problem.

Edit:

Alternatively, you can do something like this:

CREATE TABLE `status` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(64),
  PRIMARY KEY (`id`)
)

CREATE TABLE `product_status` (
  `product_id` INT NOT NULL,
  `status_id` INT NOT NULL,
  `date` DATETIME,
  PRIMARY KEY(`product_id`, `status_id`)
)

That way, you have the flexibility of all different kinds of status fields, and by using the many-to-many table you gain the ability to see when each status was set. Plus, if you're using InnoDB tables you can set up foreign keys.

To query a product:

SELECT s.title, ps.date
FROM status s
LEFT JOIN product_status ps ON (s.id = ps.status_id)
WHERE (ps.product_id = MYPID)