mysqlsqlstates

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:

  1. Pending for validation
  2. Validated
  3. Pending for content.
  4. 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:

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:

And use the binary operator in SQL.

If you have any advice for me it would be great!

Thanks.

Solution

  • 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.