Everyone who writes code has their own naming standard, but it’s also important to have database naming standards. Here is what I use when working with SQLite.
Tables
First, all my tables are named in singular using mixed case. So a table containing all the teams in my kids Little League would be called Team.
Whether you specify it or not, SQLite assigns a rowid column to every table you create. The rowid is a unique ID for each row. I don’t like to rely on rowid, particularly because older versions of SQLite would change the rowid values on tables that had deleted rows when you VACUUMed a database.
So I give all my tables their own artificial key and I call it simply ID. So for the above Team table, it would have its first column defined as:
ID INTEGER PRIMARY KEY AUTOINCREMENT
My columns are always named using mixed case, except for short acronyms (such as ID). So if I add team name and coach name columns they would be called Name and CoachName. I never, ever user the underscore (_) in any of my names.
Views
In my world, views use the same naming convention as tables.
Indexing
For column indexes, I always add Index to the column name (or names). So if I had put an index on the Team.Name column, it’s index would be called NameIndex. If it’s a unique index then I would use UniqueIndex.
Triggers
For table triggers, I use a mixed case name (usually containing a verb) describing what the trigger does.
Foreign Keys
I like all my foreign key values to consist of the table name followed by ID. A player table that has a column to track the team that a player is on would be defined as:
TeamID INTEGER
FOREIGN KEY(TeamID) REFERENCES Team(ID)
This makes it very easy to figure out what the related table is without having to actually look at the table definition. When joining, I will quickly know that Player.TeamID = Team.ID.
Remember that foreign key support is only available in SQLite 3.6.19 or later and you have to issue a Pragma (each time you connect to the database) in order to enable it:
PRAGMA foreign_keys = ON;
Data Types
SQLite doesn’t really use data typing like nearly every other database on the planet. The data types are just there for convenience. And in fact, there are only 4 defined data types: INTEGER, REAL, TEXT, BLOB. That’s it. There’s no DATE or BOOLEAN. SQLite maps unknown type names to one of its “affinities”. This means you could certainly use a type of DATE or BOOLEAN so that you know what to expect in the column and I tend to do that. So the type names that I use are:
- INTEGER
- REAL
- TEXT
- BLOB
- DATE
- DATETIME
- BOOLEAN
SQL Syntax
When writing SQL statements, I always put SQL commands and keywords in all caps. For table and column names, I use their actual mixed case names:
SELECT Name, CoachName FROM Team WHERE Name LIKE '%Bob%';
But I also go one step further and break the line after each significant section:
SELECT Name, CoachName
FROM Team
WHERE Name LIKE '%Bob%';
This makes it much easier to back later to modify the command.
Consistency
As with any set of naming standards, it doesn’t much matter what you choose for your standards. What does matter is that you use your standards consistently. Don’t have one table called Team and other called players. Don’t have some columns called CoachName and others called First_Name.
You’ll appreciate it when you look at your code later.
LogicalVue makes the following products for SQLite developers
SQLite Studio: The elegant SQLite IDE
SQLite Migrator: Migrate to SQLite from any database in 3 easy steps