Show Menu
Cheatography

DB rules for out projects

General rules

Engels
Unieke naam zonder gerese­rveerde MSSQL keywords
Namen maximaal 30 karakters lang
snake_­casing
SQL keywoorden altijd met hoofdl­etters (SELECT, WHERE)
Altijd AS gebruiken bij aliassen
Aliassen volledig uitsch­rijven, niet één letter
Joins altijd specif­iceren welke join het is
Joins sorteren op volgorde: LEFT, RIGHT, INNER
Where clause altijd op een nieuwe regel
FROM altijd maar op één tabel

Select

GOOD:

SELECT
projec­ts.name AS projec­t_name,
COUNT(­bac­kin­gs.id) AS backin­gs_­count
...


BAD:

SELECT
projec­ts.name projec­t_name,
COUNT(­bac­kin­gs.id) backin­gs_­count
...

Where

GOOD:

SELECT
name,
goal
FROM projects AS projects
WHERE
country = 'US'
AND deadline >= '2015-­01-01'
...


BAD:

SELECT
name,
goal
FROM projects AS projects
WHERE country = 'US' AND deadline >= '2015-­01-01'
...
 

Tables

Namen in meervoud
Geen prefix
Koppel­tabel niet table1­_ta­ble2, correcte naam geven

Achter­voe­gsels

_id
unieke identifier
_total
totaal of som van andere veden
_number
voor numerieke velden
_name
voor naamvelden
_date
datumv­elden

Join Sorting

GOOD:

INNER JOIN backings AS backings ON ...
INNER JOIN users AS users ON ...
INNER JOIN locations AS locations ON ...
LEFT JOIN backer­_re­wards AS backer­_re­wards ON ...
LEFT JOIN ...


BAD:

LEFT JOIN backer­_re­wards AS backer­_re­wards ON backings
INNER JOIN users AS users ON ...
LEFT JOIN ...
INNER JOIN locations AS locations ON ...

From

GOOD:

SELECT
projec­ts.name AS projec­t_name,
COUNT(­bac­kin­gs.id) AS backin­gs_­count
FROM projects AS projects
INNER JOIN backings AS backings ON backin­gs.p­ro­ject_id = projec­ts.id
...


BAD:

SELECT
projec­ts.name AS projec­t_name,
COUNT(­bac­kin­gs.id) AS backin­gs_­count
FROM projects AS projects, backings AS backings
WHERE
backin­gs.p­ro­ject_id = projec­ts.id
...
 

Columns

Enkelv­oudige naam
Id velden: tabeln­aam_id
Geen column met de naam van de tabel
Foreign key velden: tabeln­aam­_ve­ldnaam

Stored procedures

Prefix sp_
Naam moet een werkwo­ord­/ha­ndeling bevatten
Comments alleen indien echt nodig, deze dan boven de select zetten

Join Specif­iceren

GOOD:

SELECT
projec­ts.name AS projec­t_name,
COUNT(­bac­kin­gs.id) AS backin­gs_­count
FROM projects AS projects
INNER JOIN backings AS backings ON ...
INNER JOIN ...
LEFT JOIN backer­_re­wards AS backer­_re­wards ON ...
LEFT JOIN ...


BAD:

SELECT
projec­ts.name AS projec­t_name,
COUNT(­bac­kin­gs.id) AS backin­gs_­count
FROM projects AS projects
JOIN backings AS backings ON ...
LEFT JOIN backer­_re­wards AS backer­_re­wards ON ...
LEFT JOIN ...

Case

GOOD:

CASE WHEN category = 'Art'
THEN backer_id
ELSE NULL
END


BAD:

CASE
WHEN category = 'Art'
THEN backer_id
ELSE NULL
END

Help Us Go Positive!

We offset our carbon usage with Ecologi. Click the link below to help us!

We offset our carbon footprint via Ecologi
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          SQL Server Cheat Sheet
          Essential MySQL Cheat Sheet
          Oracle SQL Developer Keyboard Shortcuts