SQL style guide

Post by wvenable » Mon, Oct 10, 2016, 6:48 PM UTC

carterehsmith wrote: post #117

wvenable wrote: post #75

Earlier quoted context omitted.

Why prefix your tables with tbl? Do you prefix your functions with "fun" and your variables with "var" and your classes with "cls"? It's completely redundant.

Some people do it to avoid clashes with reserved words. Example: in some DBMSs, you cannot name your table 'user' because it is a reserved word. "user" (double quotes) works, but there may be gotchas as sometimes double quotes suddenly make identifiers case-sensitive... but only if the filesystem is case-sensitive... things like that. So you can either deal with the gotchas or say fuck it, put a prefix "tbl" and call…

Post by thijsvandien » Mon, Oct 10, 2016, 7:24 PM UTC

exceptione wrote: post #39

dotancohen wrote: post #11

Though mostly good advice, I definitely disagree with this: DONT: Plurals—use the more natural collective term where possible instead. For example staff instead of employees or people instead of individuals. I like naming my tables as plurals so that foreign keys to the table rows can have a name that relates to the table name. For instance, having column `Orders.employee` as an FK to an `Employees` record makes much…

The debate about singular/plural naming for relations comes up frequently. In my opinion, it stems from the fact that English has only 2 forms for all the possible grammatical cases. It has only a different form for the singular and plural cases, and thats what people concentrate on. I propose to take a step back. There are quite some languages that have different forms for the nominative, accussative, dative and gen…

  FrequentCustomerArray

  FrequentCustomers

Post by dragonwriter » Mon, Oct 10, 2016, 7:42 PM UTC

Stasis5001 wrote: post #47

Weird, I really dislike a lot of the suggestions. In particular, an example: SELECT first_name AS fn FROM staff AS s1 JOIN students AS s2 ON s2.mentor_id = s1.staff_num; We already agreed that staff is a good name for a table, so why are we renaming it to s1? There are all sorts of subtle bugs that arise when s2.mentor_id = s1.staff_num is wrong, and the variable names provide no help here that we're doing things rig…

  SELECT manager.last_name AS manager_name, 
         employee.last_name AS employee_name
    FROM staff as employee
    JOIN staff as manager
      ON employee.manager_id = manager.staff_id

Post by greggyb » Mon, Oct 10, 2016, 8:14 PM UTC

Pxtl wrote: post #83

I never get the fussing with aligning. I get the importance of clean indenting, but so much SQL I see looks like the writer spent a long time tweaking the alignment with spaces to make sure the data types lined up perfectly or the ON clauses or what have you.... And then had do do it all over again the moment they changed something. Just indent when scope changes, and don't align things. Alignment is a time-sink.

Post by Numberwang » Mon, Oct 10, 2016, 9:06 PM UTC

terminalcommand wrote: post #124

Pxtl wrote: post #83

I never get the fussing with aligning. I get the importance of clean indenting, but so much SQL I see looks like the writer spent a long time tweaking the alignment with spaces to make sure the data types lined up perfectly or the ON clauses or what have you.... And then had do do it all over again the moment they changed something. Just indent when scope changes, and don't align things. Alignment is a time-sink.

I always think of the DB, as the last castle of a program. When everything else fails, the DB should function. Programs are maintained way more than they're written. No matter how hard I try to keep my code-base clean, it gets cluttered. That's why I strive to create a powerful base structure and make them bulletproof. Whatever may come (depression, personal problems, fatigue, stress, deadlines, etc.) this skeleton k…