For the past week or two we've been delving into Overall Design and Design Philosophy. Today we are going to give specifics about what to do and what not to do in the Database tier and why.
Database
There are thousands of books written about the philosophy of
database design. The fact is that each
design has its own strengths and weaknesses.
We will start with the assumption that the reader is familiar with the Boyce-Coddnormal forms.
Again, Use English
Table names are Singular
We realize that every table is a collection of rows, we don’t
have to have the additional ‘s’ or pluralize it to know that.
Don’t Hate Vowels
When we eliminate vowels, we promote the use of codes. Our job is to make applications that are easy
to understand, easy to use and require no specialized knowledge. Make Table and Column Names descriptive of
what they contain and easy to understand.
Don’t Use Prefixes or Suffixes
The table containing student information should be named
Student. If you have a need to group the
table with other tables do it in a Schema. PersonalInformation.Student instead of
PIStudent
Primary Keys
Primary keys are either integers (int or larger bigint) or
Globally Unique Identifiers (GUID). If you use int or bigint. Use IDENTITY to
generate the keys. If you use a GUID,
set a default value of NEWID(). The keys have no intrinsic or extrinsic value,
that is, they uniquely identify a row in a table and contain no other
information. This is vitally
important. For example, in the Retailer
table the StoreID should never be shown to the user. If it is the user will begin referring to the
Montgrove Rd. location as Store number 27.
Which sounds great until that location closes, and the new location is
11379 and there isn’t a spot on the Distribution Center line for a box labelled
11379 so it sits in the floor while there is a hole in spot number 27. Pickers in the center literally walk from box
26 to the end of the line drop products into the 11379 box and walk back to the
28 box in the DC. Yes, this is going on
right now in every Ross Distribution
Center in the world.
Discrete v. Continuous Values
In case you aren’t familiar,
a discrete value is something you might pick from a list of values, that is,
Zip Code, Gender or Transaction Type. A
Continuous value is everything else, like length, Total Amount or First Name.
Discrete values should always
have their own lookup table in the database. Yes, this sounds like a lot of
extra work, but we will show you how to avoid every bit of this work and to
have the flexibility to modify that table later. Yes, I know some things will never change,
like gender. What? Gender is more than Male and Female now? Never say never.
Relationships
Database relationships are one of only two reasons we use
the relational database (the other is indexes).
Generally any relationships will be on indexed values in a well-designed
database so they work together. SQL
Server enforces relationships. This is
so some user can’t put an alternator in your car’s tire table. Relationships also allow you to use the
abovementioned lookup tables. Sometimes
it is less than convenient to translate the TireID of 35216 in the CarTire
linking table to the TireName of ‘Michelin Pilot Sport Cup2 345/30ZR20’ in the
Tire table. If you find a need to use
this table frequently, build a view to return the information the user expects
to see instead of the ID column.
Be Additive
An update is a delete and a re-add. A delete is incredibly expensive and should
be avoided except in instances where it is necessary. Instead of updates and deletes, add state instead
of change state. When the student in our
example registers for classes in the spring, we don’t delete or update the fall
registration, we add the new classes to the StudentClass linking table with a
new SemesterID
Linking Tables
We’ve made several references to linking tables above
without really defining them. With the
normal forms come two kinds of relationships
One-to-Many where the Primary Key of the linked table is inside the primary
table as a Foreign Key, and Many-to-Many where there is a table between the
primary and the linked table.
One To Many Relationship Example
Many To Many Relationship Example
There are a couple of things to note here:
·
The StudentClass table is named according to
which tables it links
·
The StudentClass table does not have a compound primary key, but has its own
key generated at the time of insert. This
avoids having a key that encompasses all of the columns and probably doesn’t
really describe the data anyway
·
Notice that StudentClass and Class both have
Foreign Keys that point to other tables instead of implicit, hardcoded values
(SemesterID and MajorID not shown)
·
The ‘Student’ in front of FirstName is a
preference and not part of the framework.
Don’t use it if you don’t like it and cite the “don’t use prefixes and
suffixes” rule. The ID column however
probably should use the prefix since it will be used to relate tables
together. We don’t want to relate
ClassID with ID in the Class table, nor do we want to have ID several times as
foreign keys to several different tables.
Security
Security is a very very sticky
wicket in the modern world and we hear about security breaches almost every
day. The steps outlined here will secure
the data in software designed according to the principles in this book. That means that your company will NEVER
appear on the front page of the Wall StreetJournal for a security breach and you won’t lose your job or worse your
company for having one. ...a data breach, not a job.
Create a Database User/Login with only guest
permissions
Standard has been that
this login is named <databasename>User.
Allow that user to see and login to the database as a guest but with no
other permissions. Deny 'DENY ALTER, CONTROL,
DELETE, INSERT, REFERENCES, SELECT, TAKE OWNERSHIP, UPDATE, VIEW CHANGE
TRACKING, VIEW DEFINITION’ on all user defined tables. Grant execute on
Additive Stored procedures. There is a
procedure in Appendix A - Set Permissions
Stored Procedure that will do this automatically.
Create a [User] (or equivalent) Table
Include whatever information you like about the user, but
make a column that holds a GUID identifying the User’s session. Traditionally I have called it SessionGUID.
[User] Table Example
In this example we are modeling users at credit
unions. Since we have sensitive
information about clients, we want to limit what each Credit Union Employee can
see to the Clients of his or her particular credit union.
Create a LogIn Stored Procedure
The purpose of this procedure is to check the username and
password of the requesting user and to deny them access in case of failure or
to set the SessionGUID and allow them to see what they are supposed to in the
application.
Create a LogOut Stored Procedure
The purpose of this procedure is to set the logout time and
delete the SessionGUID from the [User] table effectively locking the user out
of the application until such time as they log in again. An example LogOut procedure can be seen in
Appendix A – LogOut Stored Procedure.
Create a ValidateUser Stored Procedure
The purpose of this procedure is to validate
every incoming call to the database. It takes as a parameter the SessionGuid
and returns the UserTypeID which can in turn be used to limit what the user can
and cannot see in the database.
Create a TimeOut Stored Procedure
The purpose of this procedure is to check on a
schedule whether any users have not had activity in the timeout period (see
ValidateUser to see and set the timeout period). If not, the procedure sets the LastActivity
and SessionGuid in the [User] table to null and Logs the Logout in the
UserAudit table. Discussion of the
UserAudit table is beyond the scope of this book. Schedule the TimeOut procedure to run
automatically in SQL Server Agent every <time out minutes value> -1
minutes.
What we have accomplished here is a simple way to completely
secure your data with a custom security process at the very lowest level. This combats every kind of data theft
including SQL Injection and should be virtually impervious to any kind of
attack. This does not require the use of
Enterprise Service Bus (ESB), Message Queueing (MQ) or any other kind of
external permissions tracking devices.
Tomorrow, We are going to look at the various and sundry best practices for your middle tier objects. Stay tuned: Same Bat Time, Same Bat Channel.
