Why not create a property table for each property (like propReservationStatus and propReservationSendStatus) that holds the statuses:
and so on and then define a column ReservationStatusId in your table as a foreign key to the property table?
This scenario has some advantages:
- translations of the status independant from id
- better maintenance/extensibility (after six months would you still know what 1/2/3 is?)
- complies with database normalization rules, for example if you need the same values somewhere else
Yes, you will need two tables: one for the SentStatus and one for the AcceptedStatus.
I would suggest following fields (pseudo sql types):
Id tinyint PK NN AI
Description string NN
IsDeleted char/bool default value 0 /* you can dynamically hide/show values without changing the property table */
SortOrder int /* this way you can change the order without changing the property table */
In the table Reservation create two columns ReservationSentStatusId and ReservationAcceptStatusId of type tinyint and make them both foreign keys to the corresponding table. This has the advantage that you will be not able to add a non-existing value like (in this case) 5.
An option would be to create a view so that you can easily change the order resp. the visibility of the property items and use this view when showing the options to the user instead of mapping values to texts:
/* pseudo sql */
create view viewPropertyReservationSentStatus
where IsDeleted = 0
order by SortOrder ascending
The view can be used to fill binding sources to show/set the values in a program/webpage (if for example you are using .net/asp.net).