1

I have the following table:

CREATE TABLE Employee
(
    UID VARCHAR(10) NOT NULL,
    Name VARCHAR(20) NOT NULL,
    Type ENUM('Class_A', 'Class_B', 'Class_C') NOT NULL,
    Class_A_Speed ENUM('Fast', 'Medium', 'Slow') DEFAULT NULL

    PRIMARY KEY (UID)
);

Type defines three categories of employee (think departments). For "Class A" employees, we further categorize them based on how fast they work.

My concern is that, for all "Class A" employees, they should have a non-NULL speed; the other two classes should have Class_A_Speed set to NULL.

I can add constraints but I rather avoid it. I am trying to learn and this design seems very hacky to me...

4
  • What part of the design seems hacky? Mar 30, 2018 at 17:17
  • @ypercubeᵀᴹ The DEFAULT NULL. Maybe I can split this into 2 tables, instead of having NULL if the Employee is not class_A? Also, this complies with 4NF, correct?
    – John SK
    Mar 30, 2018 at 17:19
  • what is speed classes, tell us more. Mar 30, 2018 at 17:28
  • @Evan Carroll Nothing special, we categorize employees into 3 classes (think departments). And then employees from department A are categorized based on how fast they work.
    – John SK
    Mar 30, 2018 at 17:30

1 Answer 1

-1

Even on MySQL, you don't need to specify DEFAULT null. That's always the default, well usually always because MySQL is a special monster.

  1. Use PostgreSQL.
  2. UID should be a number, not varchar.
  3. Class_A is pretty silly, even in an enum. If your classes are all one letter, just make them a char(1) or the like and just add "type" to the column name.
  4. Class_A_Speed sounds like a function over a real speed, rather than a category. Just add the function to the database, class_from_speed(speed)

Like this,

CREATE TABLE employee (
  employee_id   int       GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name          text,
  class_type    "char",   -- or char(1)
  speed         int
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.