Unfortunately, so far, people seem to fixate on item one of my guidelines and I've had no additional suggestions.John,I believe you and I think alike. The truth is, I was brought on as a consultant to help this client, so I do not have the authority to fire the developers. Rather, I am trying to help them fix the absolute worst designed DB and coding I have seen in 15 years of working with PostgreSQL. So I've asked for input on additional guidelines to help try to help them understand the right way to do things.
So is this a style guide for this one client or a best-practices guide? From the subsequent elaboration I'll assume it is a style guide for a client...
1. OK, better they simply do this going forward than break existing warnings-only stuff by enabling standard conforming strings.
2. I do not get how the advice reconciles with the comment. Besides, presumes an unstated client application to actually execute those statements. Again, this is why it is a style guide for one client and not a general purpose best practices.
3. Yes - though to be honest this only matters at the top-level of the query. Sub-queries can make use of "SELECT *" without the performance downside (I think) and so there boils down to communication with the reader.
4. Good general advice but a handful of simplistic examples seems like inadequate training.
5. I would include how and when to use underscores.
6. My argument here would be that since in some cases you must use the constraint syntax (e.g., multi-column PK) it should be used in all cases for consistency. If you are going to recommend they provide their own name the form of that name should be specified. But the default is usually adequate so that seems like a very minor point to bring up and divert concentration and memory too.
7. (going from memory) Why hasn't PostgreSQL adopted the MySQL syntax of allowing "COMMENT ..." directly within CREATE DDL? Especially for column comments.
8. Agreed; I find this potentially opens a "do you prefix all table columns" discussion and have seen arguments to the effect of "but my ORM expects the 'id' convention'.
9. I agree with the sentiment but the example and extent of explanation seems lacking IMO...
10. Style.
Mechanics: 1, 3, 5, 6
Usability: 4, 7, 8
Style: 2,10
Modelling: 9
Based upon your definition of DBA only the following are in-scope:
1 - applications works but logs are full of warnings
3 - application consumes more resources than needed
7 - helps the DBA understand what data is in the database
10 - toss the DBA a bone by having all of their indexes have a consistent form.
If you want to expand the DBA role to application support and maintenance some of the other items would possibly come into scope.
In so far as a poorly defined model can load the database #9 is important...but I would not mix PostgreSQL style and usage suggestions with data modelling education.
David J.
