BCSEi.com

Web Site Development

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. Note: this is a possible problem in SQL Server if different owners create objects with the same names.

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.