database-design

Database design – articles, blog posts, photos, stories

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

What are the best practices for using a GUID as a primary key, specifically regarding performance? [closed]

GUIDs may seem to be a natural choice for your primary key – and if you really must, you could probably argue to use it for the PRIMARY KEY of the table. What I’d strongly recommend not to do is use the GUID column as the clustering key, which SQL Server does by default, unless … Read more

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-one: Use a foreign key to the referenced table: student: student_id, first_name, last_name, address_id address: address_id, address, city, zipcode, student_id # you can have a # “link back” if you need You must also put a unique constraint on the foreign key column (addess.student_id) to prevent multiple rows in the child table (address) from relating … Read more

Calculating and saving space in PostgreSQL

“Column Tetris” Actually, you can do something, but this needs deeper understanding. The keyword is alignment padding. Every data type has specific alignment requirements. You can minimize space lost to padding between columns by ordering them favorably. The following (extreme) example would waste a lot of physical disk space: CREATE TABLE t ( e int2 … Read more