21–30 of 151 posts
Earlier quoted context omitted.
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) { }
> and probably call your collection employees. Really ? IMHO this is a bad practice : it makes the code les readable and breaks the auto-completion ...
"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 disagree with this style point, and I think the author implicitly acknowledges that `id` should be the standard identifier, because further down he points out that the `_id` suffix should be used for columns that make reference to that identifier.
ON s2.mentor_id = s1.staff_num
staff_num is the primary key
To me this isan anti pattern because: - a primary should (can) never change; - composite primary keys are only useful (save space) in very large datasets
It also conflicts with his pattern: uniform suffix _id
If you embed encoding into a design where the data changes like putting png files into a column named icon_gif, you are deserving of physical punishment. A better design is column binary_blob_28 should really have a sister column named binary_blob_28_mimetype. And don't get cute and use silly names for mimetype, if IANA's never heard of it you probably shouldn't be using it as a name.
Oh and while you're all unicoding for a good time, standardize that everything you write is run thru a normalizer as appropriate, or at least "you tried".
Another fun area not covered (or I missed it) is nothing is more hilarious than a timestamp without associated timezone. So is 1846 a good Central time to eat dinner, or a good UTC time to eat lunch in the midwest USA, or ... some DB support a surprising amount of TZ conversion although TZ always turns into a surprising amount of pain. "We're storing every time as UTC" isn't the worst idea ever.
Where possible ALWAYS use "id" as the name of the table primary identifier and its always a bigint... why burn infinite limited brain cell cycles trying to figure out the prikey of an arbitrary table "hmm well table employee uses MD5(social_security_or_equiv) which is a 16 byte char as its primary key but table source_code uses git hashes now is it the full 40 bytes or is 7 byte prefix good enough (usually is...). If you have to look up every foreign key or even worse, guess, or worse yet, guess and get it wrong, thats the last time your prikey won't be a bigint named id. Oh and as a corollary all your foreign keys will always be named "something_id" type bigint where "something" is the exact table name, not just close.
For the largest subset of equipment you'll interconnect with, figure out the best hardware supported hash and use it for non-security (de-duplication, for example) purposes. That may very well be md5, or maybe the lower 4 to 6 bytes of md5, or whatever.
Speaking of security the guide didn't go into it, so unless you're conferred with someone who knows the problem domain plus a little about security, assume your DB will downloaded next week and published on wikileaks ... don't do something stupid with sensitive data. If theres no reason to store sensitive data, then don't store it at all to begin with!
Encoding. Needs a standard for encoding. "We are all UTF-8 here" As a parody of a bad design: this table is UTF-8 except for that column which is UTF-16 LE and that column of names is CJK and the street addresses column which is 7bit ASCII. If you embed encoding into a design where the data changes like putting png files into a column named icon_gif, you are deserving of physical punishment. A better design is column…
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) { }
If you're walking on to a new project, these subtleties make all the difference in understanding what's going on without having to rely on documentation.
Still dont get why sql uses all caps for keywords. It's one of the few languages that has it as a best practice. I can read C/JavaScript/go just fine without all caps. I do it anyway to conform.
Still dont get why sql uses all caps for keywords. It's one of the few languages that has it as a best practice. I can read C/JavaScript/go just fine without all caps. I do it anyway to conform.
LIMIT 10
SELECT *
FROM wtf
but many DB engines will be unhappy because LIMIT has to be after FROM, etc.
Once you're used to the strict order rules, you can skip reading anything in CAPS as long as the bug you're fixing isn't that they're myspeled or order out of.
"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 think the author was talking about (not) using `id` as the column name, e.g. `user.user_id` instead of `user.id`
Stopped reading right there.
There is no way you can port SQL Server to MySQL to Oracle to PLSQL without rewriting virtually every single query. They're all too different.
So why bother? Write idiomatic code that other programmers used to that engine will understand.
Not only that but different engines like different things. That blazingly fast nested subquery in MS SQL will become a massive performance problem in MySQL.
There is no such thing as portable SQL, don't pretend there is.
Not only that, often the standardised way is worse than the vendor specific way (e.g. INFORMATION_SCHEMAS vs built in sp_ from MS).