1NF is the most basic of normal forms – each cell in a table must contain only one piece of information, and there can be no duplicate rows. 2NF and 3NF are all about being dependent on the primary key. Recall that a primary key can be made up of multiple columns. As Chris said … Read more
There’s a book in Martin Fowler’s Signature Series called Refactoring Databases. That provides a list of techniques for refactoring databases. I can’t say I’ve heard a list of database patterns so much. I would also highly recommend David C. Hay’s Data Model Patterns and the follow up A Metadata Map which builds on the first … Read more
@Bill Karwin describes three inheritance models in his SQL Antipatterns book, when proposing solutions to the SQL Entity-Attribute-Value antipattern. This is a brief overview: Single Table Inheritance (aka Table Per Hierarchy Inheritance): Using a single table as in your first option is probably the simplest design. As you mentioned, many attributes that are subtype-specific will … Read more
Postgres 15 or newer Postgres 15 (currently beta) adds the clause NULLS NOT DISTINCT. The release notes: Allow unique constraints and indexes to treat NULL values as not distinct (Peter Eisentraut) Previously NULL values were always indexed as distinct values, but this can now be changed by creating constraints and indexes using UNIQUE NULLS NOT … Read more
Three tables (one for storing all items, one for all tags, and one for the relation between the two), properly indexed, with foreign keys set running on a proper database, should work well and scale properly. Table: Item Columns: ItemID, Title, Content Table: Tag Columns: TagID, Title Table: ItemTag Columns: ItemID, TagID
From MySQL docs: A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. Let’s make the distinction clear with an example of an imaginary character set. Suppose that we have an alphabet with four letters: ‘A’, ‘B’, ‘a’, ‘b’. We give … Read more
Here you will find a better solution OLTP vs. OLAP OLTP (On-line Transaction Processing) is involved in the operation of a particular system. OLTP is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE). The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access … Read more
Try MySQL Workbench, formerly DBDesigner 4: http://dev.mysql.com/workbench/ This has a “Reverse Engineer Database” mode: Database -> Reverse Engineer
Storing “Simple” Repeating Patterns For my PHP/MySQL based calendar, I wanted to store repeating/recurring event information as efficiently as possibly. I didn’t want to have a large number of rows, and I wanted to easily lookup all events that would take place on a specific date. The method below is great at storing repeating information … Read more
If the latitude coordinate is reported as -6.3572375290155 or -63.572375290155 in decimal degrees then you could round-off and store up to 6 decimal places for 10 cm (or 0.1 meters) precision. If the coordinate reference system (CRS) is not EPSG:4326 (e.g., EPSG:3857) then the x and y values measure a distance in meters from the … Read more