Need a field / flag / status number for mutliple use?

I want to create a field in my database which will be easy to query.

I think if I give a bit of background this will make more sense. My tab开发者_如何学运维le has listings shown on my website. I run a program which looks at the listings a decides whether to hide them from being shown on the site. I also hide listings manually for various reasons.

I want to store these reasons in a field, so more than one reason could be made for hiding.

So I need some form of logic to determine which reasons have been used.

Can anyone offer me any guidance on what will be future-proof aka new reasons and what will be quick and easy to query upon ?

you said

one listing has many reasons to be hidden

you implement the "one to many" relationship by giving the "many"-side a foreign key pointing to one of the rows in your "one" side. in this case this would mean two tables:

listing
reason_to_hide_listing

listing will probably have an id (int), and some text (text).

reason_to_hide_listing will have its own id (int), a foreign key column called listing_id (int), and a column named reason (text).

to find out if a listing is hidden, query the table reason_to_hide_listing for a listing_id equal to the listing you want to know if you should display or not. if one or more rows come back, this listing shouldn't be displayed.

SELECT COUNT(*) FROM reason_to_hide_listing WHERE listing_id = ?;

to find out all the reasons why one listing isn't displayed, select the reason from the table reason_to_hide_listing where the foreign key is the id of the listing at hand.

SELECT reason FROM reason_to_hide_listing WHERE listing_id = ?;

to hide a listing, and at the same time add a reason to why it's hidden, just insert a new row in reason_to_hide_listing with a listing_id pointing to the listing you want to hide.

INSERT INTO reason_to_hide_listing(listing_id, reason) VALUES(?, ?);

If you want to allow a user (for example, yourself :) ) to come up with their own reason, add a varchar(255) nullable field to your table to store the reason, and then query your table your table using where hide_reason is null or something.

If you want a fixed number of reasons that you can change occasionally by changing the table definition, use an ENUM field: http://dev.mysql.com/doc/refman/5.0/en/enum.html. (I don't know which database you're using, so I'll use my favorite one)

If you want a fixed number of reasons that you can change with not too much effort, and that you can show in a dropdown list for your users to choose from, @davogotland's solution really is the best. Adding a table is not that excessive in a database environment ;)

In any case, you could always choose to use a separate is_hidden field alongside your hide_reason field, for clarity, or because you want to remember the reason someone hid a field also when they unhide it again, or whatever. If you do, use a BIT / boolean / TINYINT field for this.

EDIT:

Ah, wait. I misread the answer above. You want a fixed set of reasons, and you want each listing to be able to have more than one reason. This means you are in a many-to-many situation (each listing can have more than one reason, and each reason can be assigned to more than one listing). There are 3 ways to do this that I am aware of:

1. The way you outlined above. Use "bits", or powers of 2. Great.
2. Use a SET data type, which is like an ENUM but with the possibility of having more than one value in it. see: http://dev.mysql.com/doc/refman/5.0/en/set.html. If you are using MS Access, there is a "Lookup Wizard" datatype which seems to do something similar (and supposedly includes an actual lookup wizard).
3. Use a link table, linking the listings to the reasons. You will need 2 new tables, reasons and listings_reasons, which look like this:

reasons:

reason_id
reason_text

listings_reasons:

listing_id
reason_id

Now, fill your reasons table with possible reasons, and link them to the listings using the listings_reasons table. This is the most common solution, since it allows you to add possible reasons without modifying your database structure.

Good luck!

You could use an Enumeration type as bit flags and store the value as an int in the database. Then all you need is a table to store the int and a FK of the related page.

[Flags]
enum HideReasons {
    None = 0x0,
    SomeReason = 0x1,
    OtherReason = 0x2
}

page.ReasonsToHide = HideReasons.SomeReason | HideReasons.OtherReason;

Edit: Just realized you may not be using C#. Hope this helps anyways.