Published: Fri 21 October 2016
By Thomas Langston
In SQL .
You may be one of the lucky. One of the developers who will never create a SQL table. You’ve always had a Database Administrator on staff. Your Object-Relational Mapping tool has always auto-generated flawless migrations from your model.
I’m not one of you.
I’ve worked at companies where developers had to ship their own schemas, procedures, and indexes. We hadn’t had a DBA in years. I’ve been on projects where the ORM was a in-house tool made to tightly integrate our code to finely tuned stored procedures and carefully calibrated table structures. I’m not a DBA. I still get angry when I’m trying to diagnose the root cause of a deadlock. But I can create a table, and I know a few things about what a good one looks like.
But what if you don’t? What if you’re fresh off the bootcamp, staring down an SQL interview question or your first project. What if you’re ten years into your career and trying to land the next job, but you’ve been in magic happy SQL Christmas land with DBAs and ORMs strictly following convention over configuration. What if you’ve got no clue what a good table looks like?
You need somewhere to start. You need..
A Developer’s Checklist For Creating A SQL Table .
WARNING! This list opinionated. It is incomplete. It is wrong. And it is exactly 9 things you need to get started learning how to make a good SQL table.
1. PRIMARY KEYS ARE IN MY OTHER PANTS
I usually should have an integer ID column marked as my primary key. Should I be using a GUID here instead for sharding? Does this table actually need a composite primary key (multiple columns identify a single unique thing). Am I incorrectly assigning the primary key to something that changes during the thing’s lifetime?
2. FOREIGN KEYS TO THE CITY (BASICS & ONE TO ONE)
What IDs from other tables am I including? Those columns should probably be named [OtherTableName]ID e.g. a table named Student should have foreign keys named StudentID in other tables.
3. CHECKING LISTS TWICE (ONE TO MANY)
If I have an object with a list of another type of object, (e.g. Basket of Oranges) proper SQL normalization says we probably need at least two tables. The listed object table will have a foreign key to the containing object (Orange Table: PK ID, FK BasketID).
4. MUTUALLY ASSURED LISTS (MANY TO MANY)
If I have an object with a list of another object, and those objects could belong to other lists (Classes of Students), SQL normalization says the number is three tables. One for each object type, plus a third for associations (and any details pertinent to that association). Name this table Object1Object2 (e.g. ClassStudent) and give it foreign keys for both tables (ClassStudent Table: FK ClassID, FK StudentID, Grade)
5. TYPICAL TYPES
Did I use numeric types for numeric data, including bit flags? Boolean (Bit) for Boolean values? Does my database support more specific types for the purpose of these fields? Currency, Time, etc.?
6. TO NULL OR NOT TO NULL
Is OK for a row to be missing a value? Multiple values? All values except the ID? Am I making a draft record that doesn’t represent a complete object? Do I have to store historical data that doesn’t include all the required fields? Does a recent change make a field optional? Resist the urge to use special values other than NULL to mean “missing” (0, -1, empty string, etc.). Use NOT NULL constraints if values are required. if you do, use a default value if possible.
7. SIZING IT UP (SMART VARCHAR SIZING)
Can I not tell? Use Max. Feel bad about those unused bytes? What’s the largest example of the data in your domain? Is it specified strictly? Example: URLs/Email Addresses/File Paths have strict technical limits. Names, titles, and other physical world descriptors can be Googled in the form of “What’s the longest X?”.
8. A NEED FOR SPEED (INDEXING)
My primary key needs an index. Where else am I using logic like Objects.FindBy(X)? Am I searching my users by email address? By phone number?
9. A TIME FOR ALL SEASONS
Do you have created, updated, deleted/archived timestamp columns in your table? If you don’t now, you’ll probably wish you had them later when you are debugging an issue and the client swears the value wasn’t changed in that table. Spend a little time thinking about time. Then start thinking about CreatedBy, ModifiedBy, and DeletedBy fields with Foreign Keys to your User table.
That’s it. If you can think about these nine things before creating your next table, you’ll be in better shape that developers with nearly a decade of experience I’ve seen walk in thru my doors. Good luck, and may the force of SQL be with you!