libertus

What could I do with this?

CREATE TABLE child (
  id BIGINT NOT NULL AUTO_NUMBER PRIMARY KEY
, parent_table VARCHAR(64) NOT NULL
, parent_id BIGINT NOT NULL
, FOREIGN KEY parent_table REFERENCES INFORMATION_SCHEMA.tables(name)
, FOREIGN KEY parent_id DEREFERENCES parent_table(id)
) ENGINE=fantasy COMMENT=’Don’t run this at home, folks!’;

AND

Why is this…

CREATE TABLE category (
  id BIGINT NOT NULL AUTO_NUMBER PRIMARY KEY
, name VARCHAR(19) NOT NULL UNIQUE
);

INSERT category (name) VALUES ('first_of_many_maybe');

CREATE TABLE lookup (
  something_id BIGINT NOT NULL
, category_id BIGINT NOT NULL
, value VARCHAR(255) NOT NULL
, FOREIGN KEY category_id REFERENCES category(id)
);

… better than this …

CREATE TABLE lookup (
  something_id BIGINT NOT NULL
, category_name ENUM('first_of_many_maybe') NOT NULL
, value VARCHAR(255) NOT NULL
);

… ?

Given the latter schema, explain why this query could return instantaneously.

SELECT DISTINCT category_name FROM lookup;

Rewrite the query above to operate identically with the former schema. Should it differ in performance?

You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.