Documenting your PostgreSQL database
craigkerstiens.com
craigkerstiens.com
Misguiding or incorrect comments caused by "Comment Rot" will actually cause more harm than having no comments at all. In fact, your code should be self explanatory (if you do it correctly) in terms of what it is doing. Adding extra comments to elaborate on "why" something is being done may be more useful - i.e. When the semantics of the code are not as easily deduced from the syntax.
For more information on "Comment Rot": http://blogs.msdn.com/b/ericlippert/archive/2004/05/04/12589...
However, I do find documenting database tables interesting and will consider doing it in the future under very specific circumstances. Thanks for sharing!
Similarly, if a table or a field has become "legacy", that is, no longer actively used but kept around because there's old data in it somewhere, that should be commented, too. Or, if a column has it's datatype changed for some reason while there is data in the table, that should probably be noted, too (e.g. char --> varchar, int-->string, etc).
Schema versioning is a huge problem at many companies, especially non-tech companies where the developers are completely at the mercy of their "business stakeholders", and since full-fledged data dictionaries don't exist for almost anything, using simple comments like this could be a boon. I dunno, ymmv, but I'd have appreciated it.
Legacy tables or fields also make sense because the code will remain the same and thus lead to no comment rot.
As long as the comments stay relevant and cause no extra confusion, then it is useful! I suppose it is also situation dependant as well as the preference of the development team at the time.
I work with a large Oracle DB which has a mixed attitude towards column comments. You don't have many characters to semantically name a column. If you're using table per hierarchy, or denormalised conventions then it can be entirely unclear which columns are supposed to be used for each row types.
Comment rot is a useful thing when it highlights that something is wrong - e.g. a new value in a table commented as accepting "Y" or "N", which prompts you to go an examine code elsewhere to find out what is going on.
My current thoughts are that all table and columns should have at least some form of comment.
You're adding the information in "git diff" for the .sql-files for the commit to the comment?
Unfortunately, it does not include any information about what the last_name field does.
Yeah, in a perfect world everything in a database would be obvious from its name, but this is not a perfect world and naming things properly is extremely hard.
last_name | character varying(50) | ... | required first name of user
Documentation that conflicts with the DDL makes for an easy code review at least. I look at it more like an interface specification, especially as there are several ways to write DDL - though there should really be a house style. For instance the NOT NULL constraint might be being added later on the script, so it isn't necessarily readable in the same way as normal code.
I don't as a general rule, the code itself expresses what it is, comments explain why it is. The examples here suffer the common problem of explaining a language/tool feature with naive examples. For example the columns in the users table are self explanatory apart from the HSTORE "data" column, which itself is likely a crime in its own right.
The exception I would make is with complex queries, SQL isn't the most transparent language especially the more advanced/vendor specific techniques.
As for a 'data' column name for hstore. This is something I've often talked about, hstore is a key value store. The big value of it is adding and removing things fluidly over time, for that reason data can be a good a column name as any in certain cases.
I usually do these comments on all objects regardless of how obvious the names and code may be. If even only for the reason of ensuring that doing this discipline remains second nature. I also like to remember that I'm not doing this for me, but rather for the client's team and others that come after me to work on things.
I have to agree that sometimes there are superfluous comments that harm more than help, since they add nothing to clarify the purpose of the column but consume your time. For example, in your post, the comment "required first name of the user" for an attribute called users.first_name adds no extra information. I'd also avoid using "required" as part of the comment, as that's implied in the NOT NULL constraint. However, other comments are very valuable, like the one for "created_at" or "data".
Regarding having a last, nullable "data" hstore or jsonb column, I think it's a very good practice. A nullable column is almost always free (takes no extra disk space) and allows for storing information without a prior, clearly defined, or variable schema, without having to be ALTERing tables frequently, which usually is a challenge on its own.
I guess that if you instead started by using something like MySQL or SQLite then you would have a wholly different view of what an SQL database is for, and would have a hard time viewing SQL as something other than a data container with optional sorting.
CREATE VIEW "Names of projects with open tasks grouped by email" AS SELECT users.email, array_to_string( ...; COMMENT ON VIEW "Names of projects with open tasks grouped by email" IS 'More info on query ...';
It is nice to have this on-hand, and it should also be in source control check-in comments / project management work items or tickets too... this can be picked up auto-magically.
It's definitely a good fit, especially for the powerful features around JSONB datatypes.