61–70 of 151 posts
Now we need a formatter/linter too
I'd love this tool. If you know a great one, please share it
http://www.red-gate.com/products/sql-development/sql-prompt/
... and this tool appears to have similar refactoring capabilities...
tblMember
---------
MemberID
MemberUsername
MemberDateJoinedIs this very non-standard formatting for SQL?
If you're using Rails, your life will be much simpler if you do use an 'id' column, use plurals for table names, and name your join tables 'bars_foos', i.e. using the plural on both sides and in alphabetical order. Don't really agree on indenting joins that are at the same level as FROM. There's not much semantic difference between the table in the FROM clause and one in a JOIN clause. Subqueries should be definitely…
I'm definitely with you there. I've written about the disadvantages of this kind of column alignment several times on HN, so won't repeat them here. (Well, if anyone asks, I'll dig up a specific comment or two.)
I have a suggestion that can help anyone cure the temptation to column align all the things: Try coding in a proportional font for a while.
Contrary to popular wisdom, proportional fonts work fine for most kinds of programming. The only thing you can't do in them is column alignment, and in most cases this limitation is a good thing.
Instead of column alignment, you'll be forced to use indentation, and you may discover that indentation alone gives you enough ways to make your code structure clear without arbitrary alignment.
In my case, it happened the other way around. I got tired of the problems that column alignment caused, so I stopped using it and switched to indentation only. Some time later I was curious and tried a proportional font, and the code was still just as readable as before. So now I use proportional fonts all the time, and I'm no longer tempted to use column alignment.
Regarding lower-case/hungarian notation etc, the membership table in my database is: tblMember --------- MemberID MemberUsername MemberDateJoined etc Is this very non-standard formatting for SQL?
Anyone use these fellas [] ?
Try to use only standard SQL? 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 mas…
Or avoid expensive, uncooperative, proprietary SQL vendors. I'll keep pretending and stick with sqlite and PLSQL.
SQL Server is in the top 3 best things to be published by Microsoft ever. (The other 2 are probably Excel and Flight Simulator)
Stuff:
1. Advice like "Use consistent and descriptive identifiers and names" - is general programming advice, not SQL style advice, and is so bland as to go without saying (would anyone seriously recommend being inconsistent or non-descriptive?)
2. Preferring standard SQL to vendor-specific SQL just isn't practical for nontrivial use cases.
3. "Use /.../-style comments where possible" - why? This makes commenting out large blocks of code really fiddly. Using double-dash comments, even for multiple-line comments, leaves /.../ usable for knocking out big blocks of code.
4. The advice on table naming - if a table contains one row per xyz, then calling the table xyzs and calling its id xyz_id is sound practice, arguably even if this does violence to the natural language in which xyz is a word. This reduces cognitive load on devs and makes the naming more predictable, especially bearing in mind that not everyone speaks the same first language.
5. No idea why "cars_mechanics" is worse than "services". "services" could mean practically anything.
6. There's a lot more to be said about table aliasing. Personally I prefer a, b, c... and then in derived tables, different groups of letters such as x, y... or p, q... . Stuff which requires an alias which will never be used (e.g. a derived table which is simply selected from) is aliased by _.
7. The guide says Hungarian prefixes are to be avoided, but a _num suffix denotes a number? Doesn't seem consistent.
8. All the advice on portability is unnecessarily opinionated. Portability is a concern that varies from completely unimportant to completely necessary, depending on what you're trying to achieve.
9. Indentation and layout - there isn't a "one size fits all" rule - it just comes down to judgement and taste. The given examples also look weird:
SELECT r.last_name
FROM riders AS r
INNER JOIN bikes AS b
ON r.bike_vin_num = b.vin_num
AND b.engines > 2
IMHO this is better:
SELECT
r.last_name
FROM
riders AS r
INNER JOIN
bikes AS b
ON
r.bike_vin_num = b.vin_num
AND
b.engines > 2
10. "Avoid UNION" - why??
11. Avoid vendor-specific data types - this just isn't practical real-world advice. Sometimes you commit to a certain DB because you need to use its specific feature set. That's OK.
12. "Prefer NUMERIC and DECIMAL to REAL and FLOAT because rounding errors are annoying". This is completely determined by your specific requirements! Essentially this advice is saying "always prefer arbitrary-precision arithmetic". 32-bit and 64-bit floats are much faster and more compact than arbitrary-precision and are accurate enough for a very wide class of use cases. IMO this advice should be turned on its head: "use floating-point unless you know you need higher precision and you understand the performance/storage implications" - which would be the default approach in most programming scenarios.
13. "Avoid table partitioning" - this is a design/performance concern, not a style concern, and it depends entirely on use case.
14. "Avoid EAV, use a different product" - not real-world advice. Most devs don't have the luxury of just introducing different products when they run into an awkward scenario, and even if they did, introducing a whole new DB engine carries major overhead. Sometimes you just suck it up and have a small EAV wart in your design.
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) { }
Honestly, they both look fine, but they do impart different meanings. "Staff" implies a set of employees dedicated to something like a company or project whereas "employees" could refer to multiple employees in any context. 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.
I basically end up with a "people" table which holds a very small amount of common data and is used in a lot of relationships.
Honestly, the distinctions require a great deal of documentation to fully understand. However, making the data less normalized and a little harder to comprehend also protects new people from making honest mistakes which could lead to legal issues.
Earlier quoted context omitted.
Or avoid expensive, uncooperative, proprietary SQL vendors. I'll keep pretending and stick with sqlite and PLSQL.
I don't know if you've ever worked with MS SQL Server in production but for us it's been the most amazing piece of technology in our stack for the past 15 years. SQL Server is in the top 3 best things to be published by Microsoft ever. (The other 2 are probably Excel and Flight Simulator)
Try to use only standard SQL? 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 mas…
It would be like coding in Java in such a way to make porting to C# easier in the future (or vice-versa).