Nulls are negatively viewed from the perspective of database normalization. The idea being that if a value can be nothing, then you really should split that out into another sparse table such that you don’t require rows for items which have no value. It’s an effort to make sure all data is valid and valued. … 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
You could do this: post: * post_id (PK) * title * body photo: * photo_id (PK) * filepath comment: * comment_id (PK) * body comment_to_post * comment_id (PK) -> FK to comment.comment_id * post_id (PK) -> FK to post.post_id comment_to_photo * comment_id (PK) -> FK to comment.comment_id * photo_id (PK) -> FK to photo.photo_id There’s … Read more
You should always enforce referential integrity by using “normal” FOREIGN KEYs. In a nutshell, FOREIGN KEYs have the following advantages: They are already implemented within the DBMS. They are declarative, self-documenting and “obvious”. They cannot be bypassed (unless explicitly disabled or dropped). They are correct. They are fast. They support cascading referential actions (such as … Read more
In most cases . . . “Staff” always describes people. Some customers are people. Some customers are businesses (organizations). “Suppliers” are usually (always?) organizations. Staff can also be customers. Suppliers can also be customers. There are serious problems with having separate tables of staff phone numbers, supplier phone numbers, and customer phone numbers. Staff can … Read more
This is called the Observation Pattern. Three objects, for the example Book Title=”Gone with the Wind” Author=”Margaret Mitchell” ISBN = ‘978-1416548898’ Cat Name=”Phoebe” Color=”Gray” TailLength = 9 ‘inch’ Beer Bottle Volume = 500 ‘ml’ Color=”Green” This is how tables may look like: Entity EntityID Name Description 1 ‘Book’ ‘To read’ 2 ‘Cat’ ‘Fury cat’ 3 … Read more
Let’s say you have a FOO table that admins and users can update. Most of the time you can write queries against the FOO table. Happy days. Then, I would create a FOO_HISTORY table. This has all the columns of the FOO table. The primary key is the same as FOO plus a RevisionNumber column. … Read more
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
What do you think about having a related translation table for each translatable table? CREATE TABLE T_PRODUCT (pr_id int, PRICE NUMBER(18, 2)) CREATE TABLE T_PRODUCT_tr (pr_id INT FK, languagecode varchar, pr_name text, pr_descr text) This way if you have multiple translatable column it would only require a single join to get it + since you … Read more
Here’s one way to implement supertype/subtype tables for your app. First, the supertype table. It contains all the columns common to all subtypes. CREATE TABLE publications ( pub_id INTEGER NOT NULL PRIMARY KEY, pub_type CHAR(1) CHECK (pub_type IN (‘A’, ‘B’, ‘P’, ‘S’)), pub_url VARCHAR(64) NOT NULL UNIQUE, CONSTRAINT publications_superkey UNIQUE (pub_id, pub_type) ); Next, a … Read more