Search code examples
phpmysqlsqlbooleannatural-key

Natural key for sign polarity


A many-to-many table joins two entity tables. I need an additional column in the many-to-many table to represent polarity and it should only have two values where one represents positive and the second represents negative.

To implement this, I plan on adding a table called sign which will have a single column called sign (which is also the table's primary key), and the table will contain only two values where one represents positive and the other represents negative.

The aforementioned many-to-many table can then include sign.sign as a foreign keys, and only the two values will be allowed.

If this is a poor solution, please comment why you feel this way and what might be a better solution.

If an acceptable solution, then what should the two values be? Possible answers are:

  • positive and negative (will not be using)
  • p and n (probably not)
  • 1 and 0
  • 1 and -1

I've purposely included the php tag to indicate that I will be using PHP on the chance that one solution over the other may simplify PHP implementation.


Solution

  • I am not strictly a fan of bit, but something like this should do what you want and be clear:

    create table . . . (
        . . . 
        sign_is_positive bit(1) not null,
        . . . 
    )
    

    Another method is to use a check constraint, which the more recent versions of MariaDB support. I might be inclined to do:

    create table . . . (
        . . . 
        sign char(1) not null,
        . . . 
        constraint chk_t_sign check sign (sign in ('+', '-'))
    )
    

    Personally, I think the best symbols for "positive" and "negative" and + and -.