After eight years on StackOverflow and more than 600 answers, these are my favourite three.
- How to understand the fifth normal form? [🔗]
- Composite primary key vs. an additional ID column. [🔗]
- How to design this database to avoid the cyclic dependency? [🔗]
These are not the highest voted answers, but I like them. All three rely on basic time-tested knowledge and principles, favour simplicity and reasoning over confusion and technical trickery.
Over the last few years, I was too busy to work on a blog — when I found some time I published on StackOverflow. I do plan to activate this blog again, will publish some ideas and tricks I have learned along the way.
I used to have a MediaWiki installation as a knowledge base for certain projects. However, due to large amount and persistence of spam I had to shut it down.
All traffic is now over HTTPS, and I have removed any video links that require Flash. The comments are disabled, so this will be just a read-only site. This way it is easier to deter spam and at least minimize user tracking. As far as cookies and tracking is concerned, I did the best I could — take a look at Cookies & Privacy.
The post relates to a stackoverflow question on DB design and my answer there.
To summarize relationships:
- One report can list many observations, an observation can appear in many reports.
- One subject (under observation) can undergo many observations, an observation relates to one subject only.
- An observation is of a specific type, there can be many observations of the same type.
- Measurement and trait are types of observations. Measurement is a numeric observation, like height. Trait is a descriptive observation, like color.
This is a simplified model based on Fowler’s observation pattern, for more details see Analysis Patterns by Martin Fowler.
Again, related to a stackoverflow question on DB design and my answer there.
Simple three-table Kimball star to allow for easy reporting. You can consider dimDate and dimEmployee as lookup tables for date and employee attributes.