Uitgaan database (Part 1)

Ik heb voor het uitgaan database project even een ER diagram in elkaar gedraaid (met cacoo :mrgreen:) Ik ben er dus van uitgegaan dat er een verzameling mensen zijn, een verzameling uitgaansplekken en een verzameling consumpties (waarvan een subset drankjes is).
— Sheet 1: 4 entities, 1 parent-child relation

Op Sheet 2 kan je het daadwerkelijke database model zien, wat de relaties:

  • tussen mensen en uitgaansplekken “going_out’s” noemt
  • tussen uitgaansgelegenheden en consumpties een prijs noemt
  • tussen prijzen en going_out’s en users een “buys” noemt

— Sheet 2: Sheet 1 + 3 relaties

[cacoo width=”550″ height=”600″ theme=”cacoo” toolbar=true]https://cacoo.com/diagrams/VnrcKUQiqy2ZKxDU[/cacoo]

 /
 |-- Klik hier om naar het tweede blad te gaan
Klik op het plaatje om het beter te bekijken.

Dit converteren naar goede (My)SQL DDL is niet heel erg moeilijk, maar vereist wel enig denkwerk over de verschillende relaties.

-- going_out.sql
-- Last update: 2009/12/26 11:36


-- user
DROP TABLE IF EXISTS user;
CREATE TABLE user (
  user_id INT NOT NULL AUTO_INCREMENT,
  user_email TEXT NOT NULL,
  user_name TEXT NOT NULL,
  user_password VARCHAR(32) NOT NULL,
  PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
-- place
DROP TABLE IF EXISTS place;
CREATE TABLE place (
  place_id INT NOT NULL AUTO_INCREMENT,
  place_name TEXT NOT NULL,
  place_location TEXT NULL,
  PRIMARY KEY (place_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
-- consumption
DROP TABLE IF EXISTS consumption;
CREATE TABLE consumption (
  cons_id INT NOT NULL AUTO_INCREMENT,
  cons_name TEXT NOT NULL,
  PRIMARY KEY (cons_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
-- drink
DROP TABLE IF EXISTS drink;
CREATE TABLE drink (
  cons_id INT NOT NULL,
  drink_volume FLOAT NOT NULL DEFAULT 0.2,
  drink_alc_perc FLOAT NULL,
  PRIMARY KEY (cons_id),
  FOREIGN KEY (cons_id) REFERENCES consumption(cons_id) 
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
-- going_out
DROP TABLE IF EXISTS going_out;
CREATE TABLE going_out (
  go_id INT NOT NULL AUTO_INCREMENT,
  go_date DATETIME NOT NULL,
  place_id INT NULL,
  PRIMARY KEY (go_id),
  FOREIGN KEY (place_id) REFERENCES place(place_id)
    ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
-- price
DROP TABLE IF EXISTS price;
CREATE TABLE price (
  price_id INT NOT NULL AUTO_INCREMENT,
  place_id INT NULL,
  cons_id INT NULL,
  price FLOAT NOT NULL DEFAULT 2.0,
  PRIMARY KEY (price_id),
  FOREIGN KEY (place_id) REFERENCES place(place_id)
    ON UPDATE CASCADE ON DELETE SET NULL,
  FOREIGN KEY (cons_id) REFERENCES consumption(cons_id)
    ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
-- buys
DROP TABLE IF EXISTS buys;
CREATE TABLE buys (
  go_id INT NOT NULL,
  user_id INT NOT NULL,
  price_id INT NOT NULL,
  buys_qty INT NOT NULL DEFAULT 1,
  PRIMARY KEY (go_id,user_id,price_id),
  FOREIGN KEY (go_id) REFERENCES going_out(go_id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES user(user_id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (price_id) REFERENCES price(price_id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Dat is nogal wat SQL en ik weet niet eens zeker of dit correct is (even uit de losse pols ;)), maar dit is volgens mij wel een redelijk goede vertaling van het ER diagram. Ik heb voor elke entity en relatie een tabel gemaakt in de database, en alle foreign key relaties aangebracht.

Er is (impliciet) vanuit gegaan dat een “user” meegegaan is met een “going out” (uitgaansavond/gelegenheid) als hij 1 tot n consumpties besteld. Wat ook wel logisch is want anders zit je er maar een beetje bij. 😉 Daarom bestaat er dus geen aparte tabel om deze relatie te beschrijven, aangezien deze achterhaald kan worden door de tabel “buys”.

Wat mij nu nog rest is natuurlijk de invoer software…

— Edit (2009/12/26): Vernieuwde SQL met beschrijvingen van de foreign keys en actions

6 thoughts on “Uitgaan database (Part 1)

  1. nou, ik ben benieuwd wat het wordt, we hebben ook al weken geen uitgaven meer bijgehouden;) het is ook wat rommelig de laatste weken xD

  2. Je hebt geen foreign key relaties in je SQL? Geeft verder ook niet want je hebt niet voor niets Databases niet gehaald. 🙂
    Naah just kidding! Ziet er goed uit gozert! Ben benieuwd wat er uitkomt! Maak er een wordpress plugin van! 😉


  3. Bas Zalmstra:

    Je hebt geen foreign key relaties in je SQL?

    En wat is dit dan: go_id INT NOT NULL REFERENCES going_out(go_id),?
    Alleen jammer dat dat niet in MySQL werkt maar voor MS SQL en Oracle en Postgre zou het wel moeten werken. Maar die hebben dan weer geen auto increment…

  4. Die had ik over het hoofd gezien! Krijg je als je het niet goed leest. Waarom doe je PRIMARY KEY wel apart maar FOREIGN KEY niet?

    Maar die hebben dan weer geen auto increment…

    PostGre en Oracle hebben wel auto_increment. Postgre heeft SERIAL en Oracle heeft triggers en sequences. Hihi! Jaja ik weet dat triggers en sequences niet is waar je op doelt. 😉

Leave a Reply to Bas Zalmstra Cancel reply

Your email address will not be published.