Yes/No columns in logical database design

Question

I have an assignment where I have to design a logical model.

I have a relation NURSE that has "nurse_id" and "certification". The certification attribute has yes/no values, so, should I move it to another relation, or is it okay to keep it in the same NURSE relation?

And is the suitable data type for that attribute (CHAR)?

Thank you,

Answer 1

As usual, it depends. Given your description, it seems already decided that certificate is a boolean type and if that's correct there is no reason to create a new relation.

However, in a real-world situation, there are probably more attributes involved. I can imagine something like:

NURSES:
    nurse_id primary key
    ...

CERTIFICATES:
    certificate_id primary key
    ...

NURSE_CERTIFICATES:
    nurse_id primary key
    certificate_id primary key
    date_of_approval 

Answer 2

For a logical database design you don’t need to specify TINYINT or CHAR — that’s a physical consideration. Some DBMSes such as Postgres have a BOOLEAN data-type which would be preferred, but again, a physical design.

In your logical model you could assume a binary-valued datatype with Yes and No as the two values (or a ternary-valued type if NULL should also be allowed).

You have two questions actually; for the other, yes, I would keep the flag in the same relation.

Answer 3

You can take a column "flagcertificate" TINYINT(1) datatype.

Additionally you can create a new Table named nurse_certificates to store certification information.

By adding new table you will be able to store one to one or one to many certification details for one nurse id.