Natural key for sign polarity

Question

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.

Are the down votes and close votes because it is opinion based or because one shouldn't store polarity in a DB, or some other reason? Thanks — user1032531, Mar 24 '18 at 13:09
Three votes on unclear. Can't you see the votes on your own question? — Andreas, Mar 24 '18 at 14:15
@Andreas I see the `close (3)` but no, I can't see the reason. Where do you see it? I will take steps to make more clear. Thanks — user1032531, Mar 24 '18 at 14:31
@Andreas I've edited the original question. Do you feel it is less unclear now? — user1032531, Mar 24 '18 at 14:40
I have not voted, I just answered your question, what the close votes was. — Andreas, Mar 24 '18 at 17:21
@Andreas Wasn't implying you did. Regardless, thanks for your response. — user1032531, Mar 24 '18 at 18:09

score 1 · Accepted Answer · answered Mar 24 '18 at 15:32

1

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

answered Mar 24 '18 at 15:32

Gordon Linoff

1,242,037
58
646
786

Any specific reasons you don't care for `bit`? Also, do you recommend `check` over my foreign key solution? Yes, using `+` and `-` is definitely intuitive (don't know why I didn't think of them!), and I will likely use. Thanks – user1032531 Mar 24 '18 at 15:56
@user1032531 . . . I associate a lot of overhead with bits, because of all the bit fiddling involved. Also, in databases, bits don't necessary save much space and definitely not time. They are often a sign of micro-optimizations focused on the wrong areas. – Gordon Linoff Mar 26 '18 at 07:19

score 1 · Answer 2 · answered Mar 25 '18 at 02:30

1

If one wishes to use the sign in arithmetic within a query, then 1/-1 might be the best choice.

answered Mar 25 '18 at 02:30

user1032531

24,767
68
217
387

Natural key for sign polarity

2 Answers2