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)

  • You could also store the statuses in configuration file so you don't have to go to database to lookup string. – imel96 Dec 5 '13 at 22:22

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:

  • 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
    Id, Description 
 from propertyReservationSentStatus 
 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).

  • so I would have two tables one for ReservationSentStatus and ReservationAcceptedStatus? – datatest Dec 6 '13 at 13:54
  • @datatest i have enhanced my answer with some suggestions. – keenthinker Dec 7 '13 at 13:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.