SQL: Create new table check with if else in view

SQL: Create new table for keep a definition of status in other table OR check with if else in view.

I'm doing about reservation Database programming.

I have a table reservation that contain [status](char=> 0,1,2,3)

0 mean reserved,1 check in,2 check out,3 cancelled

which is better between create new table for keeping a definition of status or us开发者_Python百科e if case in view to show a definition such as

if(status=="0") print reserved
if(status=="1") pring ......
.....
...

thx,for your advice

If these statuses are unlikely to be expanded in the future, and are only used by this one table, you might want to keep the definition within a single table.

If you did this, you might want to create your table with something like the following:

CREATE TABLE T (
    /* Various columns */
    Status tinyint not null,
    constraint T_Status_Valid CHECK (Status between 0 and 3),
    StatusText as CASE Status
       when 0 THEN 'Reserved'
       when 1 THEN 'Check In'
       when 2 THEN 'Check Out'
       when 3 THEN 'Cancelled'
    END
)

This saves having to create any other objects in the database, whilst automatically providing the text (if required), and avoiding invalid status values.

If, OTOH, you expect the range of statuses to change in the future, or need to use statuses in multiple tables, or just feel that the above is "messy" (opinions can vary here), then create a separate table and use a foreign key.

Use a table with a foreign key for the status