Aug 12, 2016

SQL How to handle different type of your domain entities ?

Sometimes I feel that I need user variables in MySQL views to achive my goals.

I have a table event in my database. My domain has different event types:

This domain condition reflected in database design:

CREATE TABLE event
(id primary key auto_increment,
date datetime,
type smallint)

Every kind of even has it’s own type code: 0 — Birthday, 1 — Anniversary, 2 — Meeting.

In app I have several places where I prepare some aggregational calculations for UI and these calculations based only on Birthdays and Anniversaries.

And I prefer not to repeat this condition queries every time where I need only Birthday and Anniversaries.

There are decision(s):

CREATE VIEW special_events as SELECT
t.*
FROM event t
WHERE t.type IN(0, 1)

# then you can easily do some calculations using this view

# to show number of birthdays and anniversaries
SELECT COUNT(*) FROM special_events GROUP BY DATE(date);

# to select all birthadys and anniversaries
SELECT * FROM special_events;

Pros: simple and usable.

Cons: I do not like hard coded 0 and 1 types in a view. I would like to have something readable.

2. You may change type column to ENUM type.

CREATE VIEW special_events as SELECT
t.*
FROM event t
WHERE t.type IN ('birthady', 'anniversary')

PROS: Your view will have readability.

3. You may create a event_types table.

# first given table must be changed alittle

CREATE TABLE event_types (
id primary key auto_increment,
name varchar
);

CREATE TABLE events (
  id primary key auto_increment,
  type int,
  FOREIGN KEY type (type) REFERENCES event_types(id)
);

# then your view will look like

CREATE VIEW special_events as SELECT
t.*
FROM event t
JOIN event_types et ON et.id=t.type

WHERE et.type IN ('birthday', 'anniversary');

PROS: Desired readability.

CONS: ??? I do not know

There can be some more solutions than just above 3. And I just want to discuss it with my friends and followers.