MySQL allows comments on tables and rows. PostgreSQL does as well. From other answers, Oracle and MSSQL have comments too. For me, a combination of UML diagram for a quick refresher on field names, types, and constraints, and an external document (TeX, but could be any format) with extended description of everything database-related – special … Read more
Not deleting will create a new class of bugs for all future queries. Don’t forget that query writing is often done by power users (i.e. non-IT professionals), and junior developers. So now every table that has invalid data marked only by a BIT active flag will need an additional AND in the WHERE clause for … Read more
How about the Materialized Path design? CREATE TABLE trie ( path VARCHAR(<maxdepth>) PRIMARY KEY, …other attributes of a tree node… ); To store a word like “stackoverflow”: INSERT INTO trie (path) VALUES (‘s’), (‘st’), (‘sta’), (‘stac’), (‘stack’), (‘stacko’), (‘stackov’), (‘stackove’), (‘stackover’), (‘stackover’), (‘stackoverf’), (‘stackoverflo’), (‘stackoverflow’); The materialized path in the tree is the prefixed sequence … Read more
Since you don’t want textbook definitions, loosely speaking, a super key is a set of columns that uniquely defines a row. This set can have one or more elements, and there can be more than one super key for a table. You usually do this through functional dependencies. In your example, I’m assuming: StudentNumber unique … Read more
OpenStruct? Brief example: require ‘ostruct’ person = OpenStruct.new person.name = “John Smith” person.age = 70 person.pension = 300 puts person.name # -> “John Smith” puts person.age # -> 70 puts person.address # -> nil
Normalization is basically to design a database schema such that duplicate and redundant data is avoided. If the same information is repeated in multiple places in the database, there is the risk that it is updated in one place but not the other, leading to data corruption. There is a number of normalization levels from … Read more
Probably you shouldn’t 🙂 The second most obvious answer is you should use it if your data isn’t relational. This usually manifests itself in having no easy way to describe your data as a set of columns. A good example is a database where you actually store paper documents, e.g. by scanning office mail. The … Read more
I follow a few rules: Primary keys should be as small as necessary. Prefer a numeric type because numeric types are stored in a much more compact format than character formats. This is because most primary keys will be foreign keys in another table as well as used in multiple indexes. The smaller your key, … Read more