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.