printer control puzzle

the case

  • there are 5 printers in a shop, but users are keeping selecting
    wrong ones for printouts
  • now we need a new table that derives the correct printer for each
    user at runtime
  • 3 of the printers have dedicated users (‘chacha’, ‘lee’, ‘thomas’)
    • if ‘lee’ initiates another print, he gets only the
      printer he already uses (‘LPT2’)
  • if ‘mr_paul’ initiates another print, he needs to be assigned to
    only one of the free printers

CREATE Statement

CREATE TABLE PrinterControl
(user_id CHAR(10), --null means free printer
printer_name CHAR(4) NOT NULL PRIMARY KEY,
printer_description CHAR(40) NOT NULL);

Existing table

solution

  • the problem is in the table — it does not correspond to the needs of
    our scenario:

    • you know that this is (more or less ) a static table
    • looking at user_id it is incorrect to have two nulls
      (id should be unique)
    • also you need a load balancing mechanism so that
      LTP4 or LTP5 is not overused
  • DROP the existing table

NEW CREATE STATEMENT


CREATE TABLE Puzzle_8_Printer_Control
(user_id_start CHAR(10) NOT NULL,
user_id_finish CHAR(10) NOT NULL,
printer_name CHAR(4) NOT NULL,
printer_description CHAR(40) NOT NULL,
PRIMARY KEY (user_id_start, user_id_finish)
);
INSERT INTO Puzzle_8_Printer_Control VALUES
('chacha', 'chacha', 'LPT1', 'First Floor')
('lee', 'lee', 'LPT2', 'Second Floor')
('thomas', 'thomas', 'LPT3', 'Third Floor')
('aaaaaaaa', 'mzzzzzzz', 'LPT4', 'Common Printer #1')
('naaaaaaa', 'zzzzzzzz', 'LPT5', 'Common Printer #2');
SELECT * FROM Puzzle_8_Printer_Control;

  • Queueing is done in another table I guess this is a static table
  • The list of possible strings for a username is between aaa*-zzz*:
    mr_paul is q
  • if ‘chacha’, ‘lee’, or ‘thomas’ (pre-existing users) want to print,
    the query selects a printer with the lower ordinal (LTP1 not
    LTP4)

RESULTING QUERY


SELECT MIN(printer_name)
FROM Puzzle_8_Printer_Control
WHERE :my_id BETWEEN user_id_start AND user_id_finish;
        

  • User_id is evaluated with WHERE + BETWEEN + AND combination.
    • Usually, this combination works the other way around
      (WHERE column name BETWEEN value AND value).
    • Here, we turn it around! WHERE value BETWEEN column
      AND column — you want to SELECT a proper printer
      name based on assignment to a proper range
      defined via adjacent columns
      (user_id_start–user_id_finish)

Leave a Reply

Your email address will not be published. Required fields are marked *