BCSEi.com
Relational Database Solutions
Buffalo Creek Software Engineering, Inc. (BCSEi) provides a range of software development and consulting services to
meet the needs of small to medium size businesses. Using a combination of proven technologies to bring our
customers' projects to realization on the internet, corporate network, desktop, and mobile devices.
An integral part of any enterprise is the information that must be kept for operational and historical purposes.
Whether consolidating disparate sources, building a data warehouse, or managing the entire business domain our skill
and experience with relational database systems will answers these needs. Relational databases are ideal to support web sites
or business applications for storing, searching, and retrieving data. The ability to easily produce business, statistical, and
analytical reports are among the other major benefits of using relational databases.
Database design practices
Our approach to database design, in a nutshell, is that the database system should
support the business rules and application logic but not implement them per se.
We expect the database system to enforce the rules and support the logic through
unique indexes, data constraints, and other relational constructs. While designing
a relational database we typically focus on the data properties of our business
domain, then create logical models that best support the functional dependencies,
relational keys, and normal forms for our system. Combined with a conversational
language, like SQL to provide access to the database, it facilitates the storage
and retrieval of application data in a persistent way.
In usually does not stop there in practice however, since any formidable RDBMS
provides user defined procedures, functions, and triggers that allow embedding
program logic into the database.
Since the same work is often done in other
tiers
it would seem to be a duplication of effort to have application logic and business
rules programmed into database objects. If you are working with an existing database
there is a good possibility that this is the case, and that is expected since
that is a widely adopted "best practice". ORM does not replace existing
work, it supplements the developers toolbox and supports the migration of legacy
procedures to another tier where the work is done in a high level, object oriented
language.
Database design considerations
The code generator is fairly robust and has been adapted to accommodate various
database design and programming styles however, observing a few "best practices"
will produce better results. To that extent, a few design considerations and naming
conventions are provided here.
Row identity
Each table should have a primary key and not more than one unique key defined.
The primary key provides each object instance with an identity and the unique
key is an alternative way to identify a single row in the table. Tables without
a primary key will map to an object that has only an insert method since the other
canonical methods require the object to have a unique identity within the realm
of the database.
Tables, Primary Keys, Unique Keys, and Foreign Keys names should be unique within
a database catalog. Conflicts may occur during code generation and/or compiling
the resulting code if those name are not unique.
Keyword and naming conflicts
Since we are mapping a relational database to a high-level programming language
there are bound to be conflicts with names that represent a keyword, identifier,
or other construct in one realm and not in the other. For example you can create
a table named "class" in SQL Server but this is a keyword in many programming
languages and your code probably will not compile. Case sensitivity can also be
an issue for example in C# you can have a class named "Class" but not
in VB. In practice these problems are few and usually show up right away as you
go through a generate and compile cycle.
Database naming conventions
Most software development teams will have established some naming conventions
and our suggestions are not a requirement, however the generator makes certain
assumptions for a few things that produce more meaningful names for properties
and methods in the data access objects.
Database Table names: Table names should represent what each row of the
table contains. For example, if you have a "Product" table each row
in the table would contain the data for a "Product" object. Some might
tend to name this table "Products" instead; we would avoid that as the
generator will produce a class named "Product" and any collection property
or methods that return a collection of objects usually end in an "s"
to denote the plurality (i.e. "GetProducts" not "GetProductss").
Primary Key column names: The default for a single column arbitrary identifier
value would be "ID" and a globally unique identifier (GUID) would be
"OID". For example, if products are identified by a unique integer value,
the "Product" table would have a column named "ID" with an
integer data type. The generator has many configuration values to customize the
prefixes and suffixes used for database objects.
Foreign Key column names: Referencing a primary or unique key from another
table is a fairly common practice. For example, if the product has user registrations
in a database table named "Registration" there would be an integer column
in the "Registration" table named "ProductID" and foreign
key referencing the "ID" column of the "Product" table. In
this case the generator would produce an associative method and a collection property
in the "Product" object named "GetRegistrations" and "Registrations"
respectively.
To null or not to null?
That is the question ... and a real stumbling block for programmers mapping database
values to native language variables. Improper handling of null values is often
a source of bugs and that is why the code generator builds that into the data
access objects. In general, our approach is to default database columns to "not
null" and use nulls only where really needed. Some database designers will
often go the other route as making nullable columns means that you can write insert
procedures with a minimal amount of columns included. Either way, the generator
is there to ease the pain and allow the programmer to get on with deciding the
meaning and use of null values and not have to constantly handwrite code to deal
with mapping them to native language variables.
Concurrency control
Once you have moved beyond a single-user system or trusting "optimistic locking"
to protect data from unwanted overwriting, you are likely to build in some sort
of concurrency control. This is especially important in "disconnected"
systems like web site applications. The BCSEi ORM code generator provides a method
to accomplish what we like to call "pretty good concurrency control"
simply by adding a uniquely named date/time column to any table you want to implement
that on. With SQL Server® it provides control with an accuracy of +/- 3 milliseconds.