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