121–130 of 151 posts
Earlier quoted context omitted.
It is just the terminology. Technically, a key is any column (or a set of columns) that you use to find records. It does not need to be unique. But it tends to be more useful if it is more selective, I guess that's why they say 'to some degree'. So now, if a key is also unique, it is called a 'candidate key'. In general, you can have more than one candidate key in a table. For example, a natural key (such as order#)…
> Technically, a key is any column (or a set of columns) that you use to find records. Isn't that an index rather than a key? I thought 'key' meant: - a superkey (any set of columns the values of which must be unique in any given row; in a properly relational model, any relation will have at least one, the trivial superkey, the set of all attributes in the relation) - a candidate key (a minimal superkey, i.e. one wit…
I am not familiar with that usage, I am used to 'key' being used as a logical concept, a constraint, while 'index' is a physical concept, some structure that is there 'just' to make shit go faster.
Anyways, some sources don't even define the word 'key', but use it to mean 'unique key'.
And some other sources (like the one by OP) use it to mean something else (totally not unique), as they recommend that it be 'unique to some degree'
And then some others use the definition I provided above. Is that the one meant by OP? I don't know.
I any case, I guess OP meant something like "indexes should be highly selective'. Which BTW is a performance tuning advice, I am not sure that that belongs in 'SQL Style Guide'.
Earlier quoted context omitted.
its not renaming its an alias if you have a complex sql statement having aliases just makes it easy to get you head around the code.
Only if they're meaningful. SQL isn't BASIC, and we aren't limited to x (1,2,4, etc) character variable names. Why not just use the existing meaningful name? They aren't that hard to type, and you might even be able to use autocompletion, depending on environment. I've spent far too much time re-writing queries to remove obfuscating aliases to accept a blanket statement that they make it easier to get your head aroun…
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.
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 keeps the app alive.
In my experience code that is written now, will stay and that complete rewrite I've been dreaming about will never happen :D.
So IMHO it's better to design the core funcionality exhaustively and try to create a failproof code-base.
My intellectual capacity fluctuates and I'm never as productive as I want to be. I need a safety rope for my dumber version, who has forgotten about the code and has no idea where to start :)
Indenting, and aligning helps a lot, when I try to load the code back into my memory.
An interesting read! I do follow certain rules when writing SQL, and I agree that having them and following them is a good idea. Plenty of what is in the article looks like good advice. However, these rules do not always appear to be consistent with how the code in (most) other programming languages is written. Consider indentation, for example. The usual approach is to line up those elements of the code which corres…
It's good advice, but the formatting shown is not conducive to quick editing imo. Here's your huckleberry: SELECT fs.id, fs.file_hash FROM file_system fs, other_table ot WHERE file_name = '.vimrc' AND fs.id = ot.file_system_id
fs.id
,fs.file_hash
file_name = '.vimrc'
AND fs.id = ot.file_system_id
Also, I cannot bring myself to writing SQL keywords in all caps. It feels ancient. Just a matter of taste.
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…
Not to mention with an ORM tool you'll have an Employee object, and probably call your collection employees. Which is better? foreach (var employee in staff) { } or foreach (var employee in employees) { }
foreach (var e in employees) {
// ...
}
"Where possible avoid simply using id as the primary identifier for the table." Has anyone had trouble by using surrogate primary keys? I've found the opposite of what the author said could be more true: composite keys should be avoided instead.
I am working with a 20 table db (not including lookups and session management tables) based on surrogate keys and it sucks ass for discoverability. It also subverts data checking based on SQL REFERENCES. I think natural / composite keys should used to design the db and establish relationships, surrogate keys added only if necessary, and then auto-generated, just like one waits to denormalize until there is a real nee…
"Where possible avoid simply using id as the primary identifier for the table." Has anyone had trouble by using surrogate primary keys? I've found the opposite of what the author said could be more true: composite keys should be avoided instead.
I've had lots of problems with surrogate primary keys where people don't put enough constraints on the natural key and then duplicates (sometimes slightly different) get in. Unless you're using innodb, primary key is not special, it's just another index. If you decide to use surrogate keys you also need to enforce the natural key too (if possible). There are also some nice performance advantages you can get from natu…
I have often seen commas placed at the start of each line when columns are being listed. I'm a little surprised this guide doesn't follow that convention. It would fit nicely with the established pattern of lining everything up: SELECT first_name , last_name , email_address FROM users Can anyone speculate why it's not done this way in the guide? I think the reasoning behind it was to make later modification easier be…
Earlier quoted context omitted.
One advantage of using user_id on both the pk of users as well as the fk side is you can do: INNER JOIN comments USING (user_id) instead of: INNER JOIN comments ON comments.user_id = user.user_id
Which is fine if you are aware of the differences between the two. Since they act differently.