How I Write SQL, Part 1: Naming Conventions (2014) • Hacker News Board

Re: How I Write SQL, Part 1: Naming Conventions (2014)

#31

Post by kbenson » Mon, Apr 23, 2018, 6:07 PM UTC

mtone wrote: post #27

pbreit wrote: post #22

Earlier quoted context omitted.

As a newbie, this makes more sense to me: SELECT * FROM person, team WHERE person.team_id=team.id (no unnecessary aliasing, no weird JOIN phrasing)

I can understand not using aliasing in a simple query, but I'd recommend against using these non-ANSI joins (deprecated syntax). What you're writing can be interpreted as either: SELECT * from person CROSS JOIN Team WHERE person.team_id=team.id SELECT * from person INNER JOIN Team ON person.team_id=team.id That they happen produce the same result in a query is practically just luck. Changing the join from INNER to LE…

This is good advice, IMO. You will have to use left/right joins at some point, and having the join type at the join location is useful, but having the join conditions at the join location is immensely useful.

It's harder to see in your example, likely because you tried to keep it similar to the example presented, but once there's more than a couple joins, having the join conditions close to the join is essential for keeping track of what's going on. e.g.

  SELECT * FROM person, team, role, group, person AS lead
  WHERE person.team_id=team.id
    AND person.role_id=role.id
    AND person.group_id=group.id
    AND group.lead_id=lead.id

Compared to:

  SELECT * from person
    INNER JOIN team ON person.team.id=team.id
    INNER JOIN role ON person.role_id=role.id
    INNER JOIN group ON person.group_id=group.id
    INNER JOIN person AS lead ON group.lead_id=lead.id

And let's be clear, we know these should be left joins, because the chance some person doesn't have a team, role, group, or a group ends up without a lead is high when sampled over time. And simulating left joins with the non-ANSI joins quickly gets unwieldy.

Re: How I Write SQL, Part 1: Naming Conventions (2014)

#32

Post by zo1 » Mon, Apr 23, 2018, 6:15 PM UTC

sbov wrote: post #14

sytelus wrote: post #2

Lot of these is debatable. For example, I have preferred FirstName or even “[First Name]” instead of first_name in sql because lot of tooling uses these names to generate UX. Similarly using Person.PersonID instead of Person.ID gives consistency in diagrams and foreign key naming. I have used both approaches with its own pro and cons.

> Similarly using Person.PersonID instead of Person.ID gives consistency in diagrams and foreign key naming. I assume you mean you just use PersonID as the foreign key. This oftentimes introduces ambiguity into what the relationship actually is. I prefer names that describe the actual relationship (e.g. author, owner, approver, etc) rather than letting other people guess what it is.

I think he's referring to using "[Table]ID" as the primary key on [Table]?

So now you have to join with the Person table on Person.PersonID from your local column PersonID. I much prefer the other way around: Table.ID with foreign keys being "TableID".

Re: How I Write SQL, Part 1: Naming Conventions (2014)

#33

Post by youpassbutter » Mon, Apr 23, 2018, 6:15 PM UTC

No. That's how you write sql within your organization. Also there are syntactic differences between SQL flavors ( postgres, mysql, mssql, oracle, etc ) that make a SQL standard unrealistic.

The only generic rule is "be consistent". Whatever convention/style you choose, it should be consistent.

Re: How I Write SQL, Part 1: Naming Conventions (2014)

#34

Post by deburo » Mon, Apr 23, 2018, 6:16 PM UTC

matthewmacleod wrote: post #8

walshemj wrote: post #5

Earlier quoted context omitted.

Don't use an ORM is the answer just put in the effort to lean SQL

That is a weird middlebrow dismissal of a response with essentially no value to anybody. Use an ORM when appropriate; when using one, follow its conventions. Don't use an ORM if it's not appropriate. This is much better advice.

>Use an ORM when appropriate; when using one, follow its conventions. Don't use an ORM if it's not appropriate.

It's just as vague. If you know of a good article that goes over the details, that'd be cool :^)

Re: How I Write SQL, Part 1: Naming Conventions (2014)

#35

Post by kbenson » Mon, Apr 23, 2018, 6:16 PM UTC

dspillett wrote: post #26

jermaustin1 wrote: post #12

Earlier quoted context omitted.

I use T-SQL, and this is actually why I always join with the table name: SELECT * FROM Person JOIN TeamMember on PersonId = Person.Id

I prefer to keep ID column names descriptive even if it does lead to repetition like Person.PersonID. That way columns that identify a person always carry the same name and you are never left guessing what a more anonymous "ID" refers to or fall into one of a couple of traps where the parser disambiguate one in a way you were not expecting (though this is also caught by consistently using two+ part names when referri…

Although the alternative allows for a bit of metadata to be encoded in the name. For example, if "id" is always the local item descriptor, you can almost always assume anything ending in "ID" or "_id" is a foreign key, and either the table name or relation is encoded in the prior part of the name.

It's a small thing. You'll likely know enough about the tables to be able to know this information anyways. Then again, knowing which "id" field you want is pretty obvious too. In the end, there are pros and cons to both, and it's mostly preference.

That said, once it leaves the database, I much prefer my records have short id fields, which likely influences by schema design to some degree.

Re: How I Write SQL, Part 1: Naming Conventions (2014)

#36

Post by debaserab2 » Mon, Apr 23, 2018, 6:18 PM UTC

mtone wrote: post #18

jermaustin1 wrote: post #12

Earlier quoted context omitted.

I use T-SQL, and this is actually why I always join with the table name: SELECT * FROM Person JOIN TeamMember on PersonId = Person.Id

I'd bring it a little further and would write: SELECT * FROM Person as P INNER JOIN TeamMember as TM on TM.PersonId = P.Id I have: - Aliased each table and prefixed every field names with their table alias in my join conditions. - Explicited the JOIN type. The above: - Reduces mistakes due to ambiguities that tend to generate unwanted duplicates rows in SQL. - Increases the likelihood of getting an error at parse tim…

Aliasing a query that short just obfuscates intention. It might be necessary for some larger queries, or joining the same table multiple times, but there is a readability cost you are paying for it.

For example:

JOIN team_member ON team_member.person_id = person.id

It's completely self evident what is being joined without the need to trace back to the table aliases.

Pretty much all of your advice is premature optimization in my eyes. You can/should do those things when they are needed, but there is no reason to automatically write every single SQL query that explicitly.

Re: How I Write SQL, Part 1: Naming Conventions (2014)

#37

Post by dvh » Mon, Apr 23, 2018, 6:29 PM UTC

cryptonector wrote: post #10

walshemj wrote: post #4

How come I have never in a multi decade career come across "i18n". I though the canonical way of doing this was to write KEYWORDS in caps and use camel case for Variables. Also never really brought into adding the type as part of a name - your type is already defined in your schema.

Really? I have. In some circles (e.g., the IETF), i18n is an ancient acronym. People who've worked on operating systems (e.g., OS X, Solaris, RHEL, whatever) have to deal with L10N (localization). G11N (globalization) is I18N + L10N. And then there's a11y: accessibility. This is all about making user interfaces accessible for people with low or no vision, low or no hearing, difficulty typing, and so on. There are gen…

V10N --> Velociraptor

T15X --> Tyrannosaurus Rex

D11S --> Dilophosaurus

B11S --> Brachiosaurus

T9S --> Triceratops

S9S --> Stegosaurus

Re: How I Write SQL, Part 1: Naming Conventions (2014)

#38

Post by autokad » Mon, Apr 23, 2018, 6:34 PM UTC

sytelus wrote: post #2

Lot of these is debatable. For example, I have preferred FirstName or even “[First Name]” instead of first_name in sql because lot of tooling uses these names to generate UX. Similarly using Person.PersonID instead of Person.ID gives consistency in diagrams and foreign key naming. I have used both approaches with its own pro and cons.

if you need “[First Name]” so it shows up in an UI, you could always do first_name as 'First Name'. But I would say that's still general bad practice. In many cases, you shouldn't be exposing your column names through a UI, and most UI allow for alias

Re: How I Write SQL, Part 1: Naming Conventions (2014)

#39

Post by always_good » Mon, Apr 23, 2018, 6:38 PM UTC

sbov wrote: post #14

sytelus wrote: post #2

Lot of these is debatable. For example, I have preferred FirstName or even “[First Name]” instead of first_name in sql because lot of tooling uses these names to generate UX. Similarly using Person.PersonID instead of Person.ID gives consistency in diagrams and foreign key naming. I have used both approaches with its own pro and cons.

> Similarly using Person.PersonID instead of Person.ID gives consistency in diagrams and foreign key naming. I assume you mean you just use PersonID as the foreign key. This oftentimes introduces ambiguity into what the relationship actually is. I prefer names that describe the actual relationship (e.g. author, owner, approver, etc) rather than letting other people guess what it is.

I usually want to reserve the noun like "author" as the embedded record after a join. That way "author_id" is always the key and then "author" is the json_agg joined object that embeds the whole record.

Otherwise you're actually introducing ambiguity imo.

Re: How I Write SQL, Part 1: Naming Conventions (2014)

#40

Post by reaperducer » Mon, Apr 23, 2018, 6:38 PM UTC

irrational wrote: post #25

walshemj wrote: post #4

How come I have never in a multi decade career come across "i18n". I though the canonical way of doing this was to write KEYWORDS in caps and use camel case for Variables. Also never really brought into adding the type as part of a name - your type is already defined in your schema.

You've worked on English only applications?

That could be it. Like him, I didn't run into that abbreviation for my first 20 years of coding. It wasn't until I needed to do a dual English/Czech project that it came up.

We're always learning.