Determining the Information to Store in the Database

Using the notes you took in the previous section, you’ll now try to find every area each point has an interest in. Looking at the list of areas that require information to be recorded and stored within the database, it’s clear you need to arrange them in some sort of order. You’re still looking at your solution from a logical viewpoint, and you’re not ready to start building anything in SQL Server yet. First off, let’s scan through the points listed and try to group the information into specific, related areas. The list items are numbered so I will be able to easily demonstrate the groupings. The following list shows some initial groupings and reasons for them

Determining the Information to Store in the Database

1.  You are dealing with more than one product. You need to record each product
2. Statements will be produced for each product, and specific product information for those statements will be recorded here, such as the name of the product
3.  You need to record what type of premium is associated with this product.
4.  This point deals with a financial product’s premium collection.
5. This point deals again with statement production.
6. Customers can request statements.
7. You need to record the amount of interest for a customer.
8. A list of the different products associated with each customer is required.
9. For each share, you need to keep a current value.
10. You need each customer’s address in order to send a statement.
11. As with point 2, you need the customer’s address to send a statement.
12. You trade shares on the stock market; therefore, you need to record share information. 

These five distinct groups could translate into five distinct tables within the proposed database. At this point in the logical design process, you would base your design on these five tables. From here, it is then possible to start examining the information that should go into these logical tables. There might be a duplication of data with columns in the “wrong” table, and the potential for multiple columns to hold “similar” information or the same column in more than one table. Let’s look at the list points in turn in the following sections and examine what information should be stored in each “table.” The information listed in the sections that follow is taken from the discussion with the users, and a list of the columns is supplied that may initially form the basis of the tables. Each column has a description, so when you go back to the users, they’ll understand the purpose of the columns. Also at this stage, you will add columns to hold any identifiers for finding the records; in the following sections, these are denoted with (K). These are potentially the keys, which I’ll cover in more detail later in the “Building Relationships” section of this chapter.

Financial Products

The aim of this table is to hold the different products the company sells. From bank accounts to life insurance, all products will be held here. This table will be used when producing statements and creating transactions when the user’s balance changes—for example, when buying further shares:

• Financial Product ID (K): This is a unique identifier.
• Financial Product Name: This is the name of the product, such as checking account, share, loan, and so forth
• Frequency of Payment: For each product, this indicates how often payments are collected for those products that require it, such as a loan, a regular savings account, and so on.

Customers 

This table will hold customer details, such as the customer’s products and balances. To clarify and reiterate, there will be items currently within this table that will no longer reside within it once you normalize the data. For example, you will see an attribute for “Account Numbers for Each Product.” When you proceed through normalization, you will see how attributes such as this are “moved”:

• Customer ID (K): This is a unique ID for each customer
• Financial Product Balance: This is the current balance of each product.
• Title: This is the customer’s title (Mr., Ms., and so on).
• First Name: This is the customer’s first name.
• Last Name: This is the customer’s last name
• Account Numbers for Each Product: This is the account number of each product the customer owns.
• 
  

External and Ignored Information 

At this point, you have a first draft of the logical tables and attributes, but there are still no relationships between these tables. There is one more piece of information that you need to know, which concerns information not recorded because it won’t be included within this database. The example database will not hold every item of information that is required to make the system complete. This is to keep the example simple and to avoid having extra tables that will not be used within the book’s exercises. However, there may be other times when you may want to implement only some of the tables—for example, when performing a viability study (in other words, when you’re building part of a system to prove the viability of an idea). Or perhaps a third-party tool can fill in the gaps. For example, a system might use an external addressing system, and instead of holding all customer addresses within the system, it may use a cross-reference ID. A table could also exist to hold all of the financial transactions for products not covered where specialized tables are required, such as for company pension plans. Next, let’s move on to consider relationships between the tables in the database.

Building Relationships

Much like people, databases can be temperamental creatures and need a bit of TLC. Good relationships can provide this kind of care and attention. At the moment, the tables in the example database are essentially single, unrelated items. Of course, they have columns with the same name in different tables, but there is nothing tying them together. This is where defining relationships between the tables comes in. Binding the tables together in this way ensures that changes in one table do not cause data in another table to become invalid.

Using Keys

A key is a way of identifying a record in a database table. You can use keys to build relationships between tables because a key refers to a whole record—a property you can exploit when working with columns that, for example, have the same name in different tables or even a different name in different tables but the columns hold the same information. Using a key as a shortcut, you can make the link between the two very easily. Keys can also uniquely identify a record in a table when that is an important part of the database’s design. A key can be defined on a single column if that’s enough to identify the record, or it can be defined on more than one column if not. The sections that follow introduce the three kinds of keys you can use in a database: primary, foreign/referencing, and candidate/alternate. You will also look at using a SQL Server method called a constraint instead of a primary key

Primary Key

The primary key is probably the most important key type. First and foremost, the column (or combination of columns) on which the primary key is defined must contain only unique values. A primary key cannot be defined on a column, or a sequence of columns, that does not return a single row. To this end, it is not possible to define a primary key for any columns that allow NULL values. A further restraint is that a table may have only one primary key. A primary key can be used to link data from one table to data from another. For instance, in your example database, you have two tables: one holding customers and another holding customer banking transactions. You will define a primary key on the Customers table on the customer ID that is generated uniquely each time a new customer record is inserted. This is then used to link to the many records within the banking transactions table, to return all the transactions for that customer ID. The link between these two tables is the customer ID, which as previously mentioned is defined as a primary key in the Customers table. Later in this chapter, you’ll see how to join tables together and define a relationship between them. A join and a relationship are essentially the same thing: a logical link between two or more tables that can be defined through a specific column or set of columns between the tables.

Foreign/Referencing Key

There will be times when you have two or more tables linked together in a relationship, as demonstrated in the earlier example, where the link between the Customers and Transactions tables is the customer ID column. This column returns a unique row in the Customers table; hence, it is defined as the primary key of the Customers table. However, there has to be a corresponding foreign (or referencing) key in the Transactions table to link back to the Customers table, which is the customer ID column of the Customers table. When it comes to creating relationships within your example database, you will later see how a foreign key is created that will create a link, or a relationship, between two columns. This link is created through a constraint, which is a method SQL Server uses to check the details built into the relationship. From the viewpoint of a foreign key, this constraint, or check, will ensure that the relationship follows the conditions set with it. You’ll examine foreign keys in more depth in the “More on Foreign Keys” section.

Candidate/Alternate Key

As mentioned previously, a table can have only one primary key. However, there may be another key that could just as easily be defined as a primary key. This is known as a candidate key because it is a candidate for being the primary key.