Skip to main content

Rule

Only Use Lowercase Letters, Numbers, and Underscores​

Don’t use dots, spaces, or dashes in database, schema, table, or column names. Dots are for identifying objects, usually in the database.schema.table.column pattern.

Example:

select user_name from events

Use Simple, Descriptive Column Names​

If the users table needs a foreign key to the packages table, name the key package_id. Avoid short and cryptic names like pkg_fk; others won’t know what that means. Descriptive names make it easier for others to understand the schema, which is vital to maintaining efficiency as the team grows.

Don’t use ambiguous names for polymorphic data. If you find yourself creating columns with an item_type or item_value pattern, you’re likely better off using more columns with specific names like photo_count, view_count, transaction_price.

Use Simple, Descriptive Table Names​

If the table name is made of up of multiple words, use underscores to separate the words. It’s much easier to read package_deliveries than packagedeliveries.

Have an Integer Primary Key​

Even if you’re using UUIDs or it doesn’t make sense (e.g. for join tables), add the standard id column with an auto-incrementing integer sequence. This kind of key makes certain analyses much easier, like selecting only the first row of a group.

Be Consistent with Foreign Keys​

There are many styles for naming primary and foreign keys. Our recommendation, and the most popular, is to have a primary key called id for any table foo, and have all foreign keys be named foo_id.

Store Datetimes as Datetimes​

Don’t store Unix timestamps or strings as dates: convert them to datetimes instead. While SQL’s date math functions aren’t the greatest, doing it yourself on timestamps is even harder. Using SQL date functions requires every query to involve a conversion from the timestamp to a datetime.

Don’t store the year, month, and day in separate columns. This will make every time series query much harder to write, and will prevent most novice SQL users from being able to use the date information in this table.

UTC, Always UTC​

Using a timezone other than UTC will cause endless problems. Great tools (including Sisense for Cloud Data Teams) have all the functionality you need you convert the data from UTC to your current timezone. In Sisense, it’s as easy as adding :pst to convert to from UTC to Pacific Time.

Example:

select [created_at:pst], email_address
from users

Have One Source of Truth​

There should only ever be one source of truth for a piece of data. Views and rollups should be labeled as such. This way consumers of that data will know there is a difference between the data they are using and the raw truth.

Prefer Tall Tables without JSON Columns​

You don’t want to have super-wide tables. If there’s more than a few dozen columns and some of them are named sequentially (e.g. answer1, answer2, answer3), you’re going to have a bad time later.

Don’t Over-Normalize​

Dates, zip codes, and countries don’t need their own tables with foreign key lookups. If you do that, every query ends up with a handful of the same joins. It creates a lot of duplicated SQL and a lot of extra work for the database.

Avoid reserved keywords​

Check sql language reserved words table.

PostgreSQL example:

TEMP
USER
DESC