Hacker Newsnew | past | comments | ask | show | jobs | submit | Treffynnon's commentslogin

Have you read Celko's book? If not I suggest you do. As the guide states at the very top that is where you'll find the detail you're after.


I think a lot of this comes down to what appears to be a simple misunderstanding.

Avoid - in this context means that where it does not make sense (for performance, readability, etc reasons) then don't follow the guide. Follow it where possible and be mindful of when you deviate that you're adding to tech debt.


Putting the comma before doesn't actually make commenting out columns easier - it just moves the problem to the other end of the list. Just try commenting out the first column in your SQL and you'll see what I mean.


I am totally lost when it comes to your comments on the second item. It seems you're trying to say that surrogate keys are easier to find or perhaps guess.

The indexes set out against a table can generally be accessed with a query making it very easy to work out what existing index suits your use case best.


Thinking multi-column indexes here.


Yes, but it's in the 'Constraints and keys' section. It even says 'Constraints, and their subset, keys'.

As far as I'm aware, an index is not a constraint, except for a unique index, which implies a unique constraint.

Having said that, I can't think of an explanation more likely than yours.


Well that's good seeing as though I wrote the guide in the first place.

An illustration in bad pseudocode:

    PRIMARY KEY (account_number, transaction_date)


> Well that's good seeing as though I wrote the guide in the first place.

Apologies, I didn't realise. That was crass of me.

But could you explain what you mean by unique in some degree? Surely in the example you give the key is just unique, with no further qualification?


OK, so I think this might be a language/understanding difference here - perhaps what I have written isn't clear enough - not sure. The statement to some degree, in my opinion, doesn't imply that the key isn't actually unique. It still means unique.

What I meant is that if you don't have a unique column to make, say, a primary key with then you need to make it unique by adding (an)other column(s) to it in the key definition. Each additional column being a degree of difference/separation in my mind as I was writing it.

I hope that explanation makes sense and doesn't confuse the issue further.

I'd like to get your feedback on what you think it could say to make this clearer - I want to convey:

a. it must be unique.

b. you can have multi-column keys (some users - especially those tied to an ORM - don't actually know that you can do this).


Basically this is the answer - besides that bit about acquiescing to the demands of an ORM.


This seems to be a simple misunderstanding.

Try - in this context means where it doesn't make sense (for performance, readability, etc reasons) then don't follow the guide. Follow it where possible and be mindful of when you deviate that you're adding to tech debt.


This is a pretty commonly asked question. The answer is two fold:

1. putting the comma before doesn't actually make commenting out columns easier - it just moves the problem to the other end of the list. Just try commenting out `first_name` in your example and you'll see what I mean.

2. because normal convention would be for the comma to come immediately after an item in written English.


Re; #1, I also use comma-first indenting and I actually put each field in it's own line specifically for ease in commenting (first_name would have its own line)


This doesn't solve the problem at hand either unfortunately. You'll still have a hanging comma in front of `last_name`:

  SELECT 
      -- first_name
       , last_name
       , email_address
    FROM users
When parsed it will look like this query error to the system:

  SELECT
       , last_name
       , email_address
    FROM users


This is an interesting idea. I will give it some thought as others have also suggested similar. Perhaps the guide could be relaxed a little to allow singular names.

Someone mentioned that tables can be considered like types - another interesting thought - in which case singular makes sense.

On the other hand it is a collection of something and therefore the collective term applies equally well.


When modelling data it should be the primary concern and not an ORM. Different applications will need access to the same information. ORMs are application specific and therefore should not determine how data is structured.

As for the non-English speakers finding the collective difficult; I can see that. I have not specifically considered their needs whilst compiling the guide. In the case of `flora` I am guessing you're thinking `plant`?


I suppose if you need a style guide for SQL, then you're likely in the business of building databases to be accessed by multiple applications. However, be aware that the majority of applications are initially developed using a database as a data store, alongside opinionated ORM systems that will prefer a specific naming convention (such as plural forms).

Speaking from experience, you can't even expect native speakers to know all the collective forms. Worse, in some cases the collective form may cause confusion if the collective is the singular of a different table (players in a team, teams in a league, doing a fantasy football league app).

Using the plural, your schema would be player -> team -> league. Using your system, it would be team -> league -> sport. Good luck explaining that to a new hire.


No, actually the collective of player isn't team in this case.

Not all players will be on the same team, but they still participate in the league so I disagree with this example terminology shift.

You would end up with a slightly more generalised naming like this in my opinion:

- athletes - teams - leagues

In this particular case there are no suitable collective terms.


Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: