SQL Server Performance Forum – Threads Archive

Records status to display or hide them in the app

I have a table called â€˜offers#%92 in which I store offers published by users in my website. Now I decided they can made its offers to be displayed in the web page or not. To do so, in the user#%92s private area, she/he can change the status of every offer in the website between â€˜Active#%92, â€˜Not Available#%92 and â€˜Eliminated#%92. â€˜Active#%92 means display the offer normally, â€˜Not Available#%92 means display the offer but with a message that says â€˜Not Available Offer#%92, and â€˜Eliminated#%92 means not display the offer. Which is the best way to handle this information in the DB? Now I have a field in offers table called â€˜status_num#%92 where I store that information. Then, in that column Null value means when database administrator wants to hide completely the offer (included to user who published the offer), 1 means Active, 2 means Not Available and 3 means Eliminated. So in the queries to display offers in the web page I must include: (Among other conditions) WHERE status_num Is Not Null And status_num <> 3 And……
And in the user#%92s private area to change offers status, I must include in the queries: (Among others conditions) WHERE status_num Is Not Null And……
Is this a good way to handle that information in the DB? Ideas/suggestions? Thank you,
Cesar

You can use BIT data type for such status of records, I don’t see any issue in the current approach.
You must say whether you’re getting any issues. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

He can’t use the BIT data type, since he mentioned possible values of 1,2,3 (probably NULL?). So, most likely it will be a TINYINT. Other than that, I think that’s the correct way to deal with it. Don’t make the mistake to combine multiple stati into one column and create a bitmask in an INT column, for example. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Oh yes it is, thanks for the correction Frank [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. <hr noshade size="1">Satya SKJ Contributing Editor & Forums Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center>

Ok thanks, finally I changed a bit my code [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]. Now ‘status_num’ column is NOT NULL, so only can be stored (1,2,3 and 5) value, and I created another column (‘status_adm’) which indicates whether the offer_id is swiched On or Off by administrator, I mean visible or not visible for all users (0=on and 1=off). I suppose that ‘a bitmask’ is a column like ‘status_adm’, isn’ t it? And instead of filter thus: <pre id="code">WHERE status_num Is Not Null And status_num <> 3 And……</pre id="code"> I filter thus: <pre id="code">WHERE status_num In(1,2) And status_adm = 1 And……</pre id="code"> And instead of filter thus: <pre id="code">WHERE status_num Is Not Null And……</pre id="code"> I filter thus: <pre id="code">WHERE status_adm = 1 And……</pre id="code"> In this way is much more better, isn’t it? Thank you

A bitmask would be something you would query in a way
…
WHERE some_column & some_value = some_other_value Not only violate such column 1 NF, imho, but also can’t SQL Server efficiently handle them.
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

I would use a text label and not a number, for clarity. That eliminates the need to remember or communicate to others what 1, 2, 3 mean, because it becomes self-evident. Also, it would be a good idea to constrain the possible values with either a check constraint or a second table and foreign key constraint, so that it’s not possible to accidentially set an invalid status. Eschew obfuscation, whilst doggedly pursuing the reduction of complexification.

]]>