Q:

Database Design: Multiple States for single item

I'm designing a database and I would like to know what's the best way to do a thing like this:
My database will store items. Each item can be in many states:

Pending for validation
Validated
Pending for content.
Deleted

... and so on.
Those states can be on at the same time. For example, an item can be pending for validation + Pending for content.
In addition, each item will have different states depending on the country where it is, let's say I have three countries: UK, Brazil and China.
So I have an Item X with those properties:

Pending for validation (UK) - (Yes/No)
Pending for validation (Brazil) - (Yes/No)
Pending for validation (China) -(Yes/No)
Validated (UK) - (Yes/No)
...

When the states of the items grow, the number of combinations also grows exponencially.
My idea is to use binary states, having a table like:
tbl_item:

status_uk:0000
status_br:0101 (Pending for validation + Waiting for content)
status_cn:0001 (Pending for validation)

And use the binary operator in SQL.
If you have any advice for me it would be great!
Thanks.

A:

You seem to want a table that is ItemCountries.  Something like:
create table ItemCountries (
    ItemCountriesId int auto_increment primary key,
    ItemId int not null,
    CountryId int not null,
    Status ?,
    foreign key (ItemId) references Items(ItemId),
    foreign key (CountryId) references Countries(CountryId)
    unique (ItemId, CountryId)
);

I left the Status column open.  I would probably make it a varchar(255) and control the value in the application.  (In other databases, I would use a check constraint, but MySQL does not enforce those.)
You have other options, including a reference table for valid statuses (very important if you need to translate the status values to other languages, for instance) or an enum.

