Introduction
When designing your application’s database, it is very important to get the schema correct and follow an agreed upon naming convention, regardless of the size of the application. This helps everyone instinctively learn the schema and purpose of every table and field, even if they are not familiar with all aspects of the database. Getting the schema design correct the first time will also reduce (or eliminate) any changes required to the data access code later in the project’s lifespan.
Code Generation
Getting the database schema design correct will save you a lot of time if you are using code generators, such as Iron Speed Designer, to create the underlying data access and business layer code in addition to the front-end graphical user interface, whether your application is web-based, SharePoint-based, or even a Windows form application.
Iron Speed Designer relies on the table relationships in order to determine how best to provide the quickest and most efficient experience. What I mean by that is that when creating pages with bound data you tend to find on the whole that you want to see data that is related to some other data on the page. For example, when creating a SalesOrder page you will probably want to see related data, the obvious one being SalesOrderDetails. Not so obvious would be the customer this order is for and the status of the order. By linking these in the database, Iron Speed Designer will know how best to create the page layout for you using the wizard, it will use dropdown lists of information that saves you a lot of work. If you see what you consider a foreign key item (a link to another table) that is rendered as a text box rather than a dropdown it’s a fair bet you do not have a relationship set up in the database. Go back and create the relationship before trying to move on with Iron Speed Designer is our advice. You can of course create a virtual foreign key relationship in your code generator, a relationship that exists only in the application; no changes are made to your database schema.
Design Practices
Some useful design practices we have come across and use at Dot Net Architect are:
- Use well defined names for your tables and fields, i.e. SalesOrder, not order.
- Use the singular, i.e. SalesOrder not SalesOrders.
- Use leading capitals for name, i.e. SalesOrder not salesorder.
- Avoid spaces and other punctuation, i.e. SalesOrder not [sales order] or [tblSales order]. This avoids having to put [] around the name all the time.
- Use a single field primary key (PK). Even though you may have other fields that could compound to give you the PK, we find it better not to.
- Use the name of the table appended with ID as the name of the PK field where possible, i.e. SalesOrderID.
- Use bit fields to store Boolean yes/no values and start the name with Is, i.e. IsEnabled.
- For tables that store type information, i.e. a lookup-list, append the word Type, i.e. CurrencyType or CountryType.
- Add audit fields so you know who created and when and who edited and when.
- Add a sort order field so you can order the items, great for when alphanumeric is not good enough.
- Use the database diagrams to visualise your schema. This makes creating relationships a breeze.
- Use indexes to speed up queries, with use of the Profile Analyser to make suggestions.
- Use unique constraints/indexes. Avoids having duplicate data in the database where it shouldn't be.
- Use GUID's (unique identifiers) for key fields. There are many reasons for this, which can be debated at length. We find they work, are globally unique, more secure than integers and avoid errors when all other PKs start at 1 and increment by 1.
- Add a
DisplayText field that is a computed column that you can define as required. We point them to other fields in the table and always use this field as the display field for dropdowns etc. If we need to change it later, we just change the formula in the computed field in SQL Server.
- It is not always possible to delete records, instead use an
IsDeleted bit field to mark the record as deleted. Ensure all data is filtered by this.
- We also add a couple of extra fields where needed, IsHidden and IsEnabled, that allow the record to be filtered by the admin and the user, i.e. as an admin, I don't want a user to login any more I set the
IsEnabled to false, if later want them to login, I simply flip the flag. The IsHidden can be used to filter items that appear in dropdown lists, for example, without deleting it.
- Add an autoincrement field, but not for use as the PK. This can be useful when creating invoices, for example, and you need a visual number for the user.
- Use nvarchar fields to ensure all languages are catered for.
- Use varchar(max) when needed, avoid ntext or image unless really needed.
- Try to offload any image fields (for documents or images you want to store in the db) in a separate database or table and just have a pointer to them. That way you can reduce server load as you don't always want the image data coming back when selecting data. You can also backup or image the database separately. (We have an existing client that has over 40GB of data in a database, 35GB of that is PDF and word documents. If that was in a separate database, downloading the main database would take only 10 minutes not 10 hours!)
- Use triggers only when needed and insure you really understand them.
- Use views, we use them a lot with when we just want to show read only data with multiple joins.
- Use functions for repeated code, i.e. we have a suite of functions that return 1st day of month, 1st day of quarter, 1st day of year, etc.
- Keep user permissions to a minimum, it’s easy to give the user dbo permissions, try not to!
- When you have some functionality that requires speed, put it in the database. Move it out of the application to a stored procedure; you gain speed but loose the strongly typed environment.
- Write your stored procedures so they can be tested and benchmarked. Use SQL profile analyser to analyse speed and ensure you test with multiple records and users.
- Use Red Gate SQL Toolbelt for syncing your changes.
