Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts

Thursday, August 3, 2017

How to Make a DBA Salty

If you want to upset you DBA, and possibly get privileges revoked or at best not be able to ask for any favors, then here's how you can go about that.


Open a SQL editor (SSMS, Toad, Dbeaver, whatever you use to connect to and execute SQL). Connect to the highest level database you have access to. Make sure any kind of autocommit settings are off if this applies.


Now you want to make sure your not going to do anything really damaging like drop tables or something crazy like that - probably you won't have access to do that anyway if the security policies are set up correctly. But you should be able to update some data.


Now open a transaction:


BEGIN TRAN;
or
START;


or whatever your specific DB uses to start a transaction.


Now update some tables from within the transaction. It's best to do a whole range of updates and a few inserts just to ensure that table level locks are taken. Do this on a few tables just to be sure.


From within that same transaction, select from all those tables you've altered to verify that the data has changed. And that's it...don't commit the transaction, just leave it open. Before you know it, warnings will go off, alarms will sound, alerts will get sent. Anything using the DB will grind to a halt.


Eventually, a DBA will find that you've caused this and will lose trust and faith in your abilities. Lock the database like this a few times and you'll be put in the corner. Would you lose your job over it? Perhaps....


If you've left a transaction open like this and closed the window with the query, the connection and the transaction might even be left open. Then its up to the DBA to kill the connection and rollback the transaction.


BTW, you should probably ROLLBACK the transaction eventually, perhaps the DBA will ask you to close the trans...that means to roll back the changes you've made so that they go away without actually updating the DB or to COMMIT them which would apply them to the database. In this case, you were just messing with the data so you probably don't really want to commit the changes - they'd be saved permanently!

Friday, August 15, 2014

RMDB Surrogate v Natural Key

An integral part of transactional relational databases is the Primary Key (PK). A PK is a value that defines the data row and that related tables use to relate via a Foreign Key (FK).

Four standard criterion are commonly used to measure a PK.

1. Is it unique?
2. Is it immutable?
3. Is it minimal?
4. Is it never null?

A key can consist of one or more columns in a table as long as the combination meets the criteria.

My recommendation is that you stick to a Surrogate Key if you don't know. Here's why-

Natural keys made under an assumption that some business rule will not change can cause significant problems later when it does.

Most arguments for natural keys place emphasis on ease of use. If you find yourself joining multiple columns because your PK is a combo key then you lose that value. Create views to ease your suffering from joining a few more tables in your queries.


The exception I would make is for SOME lookups. Esp when a well defined code is used as the natural key. Bit even then, shit changes all the time. Suppose you assumed that the US state codes were immutable. Or telephone area codes. You would have to write scripts to update all those FKs that use 'Calif' or use some hacked in solution to fix your data and update all the apps that consume the data to support the hack. Contain volatility in your data!

Friday, July 25, 2014

SQL Server single row table

In SQL Server 2008 R2 I made a single row table that had Application Settings.

In order to enforce a table constraint of one row, create the table with a PK constraint on and id column with a CHECK contraint of idCol = 1. This will do two things, it will enforce that every row is unique and not null with the PK and the CHECK will ensure that the only val possible is 1.

Then, all you have to do is insert a row with id 1 and all other columns are setting vals (global settings for the app). If you need multiple settings, join tables to this one.

For Multi-tenant applications, consider adding a table for tennant specific settings. This table is for global settings only.

Wednesday, July 9, 2014

SOLID DB principles

In my last post, I rattled off a little bit about creating a db model that conforms to SOLID development principles. I presented the idea to some colleagues and it seemed to go over well. Today I will attempt to expound upon the principle and how a good database design can conform to each piece of the SOLID acronym.

S - single responsibility - each table should have a single responsibility. This means no god tables with nearly infinite nullable columns that can capture more than one type (sub-type actually) of thing.

O - open-closed - open to extension, closed to modification. Create linking tables or type tables to extend a base table, don't change the base table. Add views to create reusable components. Always create list/type tables so that new types of data can be linked to metadata. Reuse data when possible (no is no). If you really, really need to change the attributes frequently, consider EAV - but with caution.

L- Liskov Substitution - Comment is a base table, PostComment is a sub table. They map to types in object-land. Learn TPT, TPH etc.

I - interface segregation - there aren't really interfaces. Just make sure again that your tables, and perhaps views and other objects such as stored procedures/functions (especially these) aren't doing more than one thing.

D - dependency inversion (DI) - this applies more to the DAL than anything. Using DI allows for switching the database technology later, or even spread your data model across technologies. Either way all other code/modules/layers should be agnostic of the data store - abstract that baby away! One could go so far as to write adapters that adapt the db access technology to a custom interface should one want to switch from something like Entity Framework to NHibernate. EmberJS does something like that in the ember-data library. Consumers make all calls through ember-data objects, but can map the calls to whatever their storage technology requires. I truly can't think of any way to apply DI in SQL, perhaps noSQL implementations will have varying abilities to do so with map/reduce functions. I would say that the best way to practice good DI with SQL is to avoid any sort of business logic in stored procedures. I have seen some pretty tricky DI being simulated in stored procedure chains, perhaps some EXEC calls on dynamic SQL and things like that. I avoid those sorts of things like the plague these days unless it's generated SQL via EntityFramework.

Anyways, that's my attempt to SOLIDify database design. I feel kind of floppy about the whole DI rambling, if anyone has some good ideas about that one feel free to chip in!

Happy Coding!