I have a reservation table (a user fills out a form to request a reservation).
It has two parts that need to be confirmed. isReservationAccepted (decline,accept,waiting) and hasReservationBeenSent (sent, declined, waiting)
Not sure how to structure my table, thinking about making a lookup table for each column, or maybe just creating them as int, and having 1 = accepted/sent, 2 = declined, 3 = waiting.
not sure if there is a better way (probably)
Why not create a property table for each property (like propReservationStatus and propReservationSendStatus) that holds the statuses:
Id Description --------------- 1 Decline 2 Accept 3 Waiting
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:
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
begin
select
Id, Description
from propertyReservationSentStatus
where IsDeleted = 0
order by SortOrder ascending
end
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).
