Primary Keys

As noted in the previous section, an IDEF1X entity must have a primary key. This is convenient for us, because an entity is defined such that each instance must be unique (see Chapter 1). The primary key may be a single attribute, or it may be a composite of multiple attributes. A value is required for every attribute in the key (logically speaking, no NULLs are allowed in the primary key).

The primary key is denoted by placing attributes above a horizontal line through the entity rectangle. Note that no additional notation is required to indicate that the value is the primary key.

For example, consider the Employee entity from the previous section. The EmployeeNumber attribute is unique, and logically, every employee would have one, so this would be an acceptable primary key:

The choice of primary key is an interesting one. In the early logical modeling phase, I generally do not like to spend time choosing the final primary key attribute(s). The main reason for this is to avoid worrying too much about what the key is going to be. I tend to create a simple surrogate primary key to migrate to other entities to help me see when there is any ownership. In the current example, EmployeeNumber clearly refers to an employee, but not every entity will be so clear—not to mention that more advanced business rules may dictate that EmployeeNumber is not always unique. (For example, the company also may have contractors in the table. That’s not a good practice perhaps, but no matter how much I try to describe perfect databases in this book, not every table will end up being perfect.) Having to repeatedly go back and change the entity used for the primary key in the logical model over and over can be tiresome, particularly when you have a very large model.

It is also quite likely that you may have multiple column sets that uniquely identify a given instance of many of your entities. As an example, consider an entity that models a product manufactured by a company. The company may identify the product by the type, style, size, and series:

The name may also be a good key, and more than likely, there is also a product code. Which attribute is the best key—or which is even truly a key—may not become completely apparent until later in the process. There are many ways to implement a good key, and the best way may not be recognizable right away.

Instead of choosing a primary key at this point, I add a value to the entity for identification purposes and then model all candidate keys as alternate keys (which I will discuss in the next section). As a result, the logical model clearly shows what entities are in an ownership role to other entities, since the key that is migrated contains the name of the modeled entity. I would model this entity as follows:

Alternate Keys

As defined in Chapter 1, an alternate key is a grouping of one or more attributes whose uniqueness needs to be guaranteed over all of the instances of the entity. Alternate keys do not have specific locations in the entity graphic like primary keys, nor are they typically migrated for any relationship (you can reference an alternate key with a foreign key based on the SQL standards, but this feature is very rarely used feature, and when used, it will often really confuse even the best DBAs). They are identified on the model in a very simple manner:

In this example, there are two alternate key groups: group AK1, which has one attribute as a member, and group AK2, which has two attributes. There also is nothing wrong with overlapping alternate keys, which could be denoted as (AK1,AK2). Thinking back to the product example, the two keys could then be modeled as follows:

One extension that Computer Associates’ ERwin adds to this notation is shown here:

A position number notation is tacked onto the name of each key (AK1 and AK2) to denote the position of the attribute in the key. In the logical model, technically, the order of attributes in the key should not be considered even if the tool does display them (unique is unique, regardless of key column order). Which attribute comes first in the key really does not matter; all that ] matters is that you make sure there are unique values across multiple attributes. When a key is implemented, the order of columns will become interesting for performance reasons (because SQL Server implements uniqueness with an index), but uniqueness will be served no matter what the order of the columns of the key is.

Foreign Keys

Foreign key attributes, as I’ve alluded to, are also referred to as migrated attributes. They are primary keys from one entity that serve as references to an instance in another entity. They are, again, a result of relationships (we’ll look at their graphical representation later in this chapter). They are indicated, much like alternate keys, by adding the letters “FK” after the foreign key:

As an example of a table with foreign keys, consider an entity that is modeling a music album:

The artistId and publisherId represent migrated foreign keys from the artist and publisher entities. We’ll revisit this example in the “Relationships” section later in this chapter.

One tricky thing about this example is that the diagram doesn’t show what entity the key is migrated from. This can tend to make things a little messy, depending on how you choose your primary keys. This lack of clarity about what table a foreign key migrates from is a limitation of most modeling methodologies, because displaying the name of the entity where the key came from would be unnecessarily confusing for a couple of reasons:

• There is no limit (nor should there be) on how far a key will migrate from its original owner entity (the entity where the key value was not a migrated foreign key reference).
• It is not completely unreasonable that the same attribute might migrate from two separate entities with the same name, especially early in the logical design process. This is certainly not a design goal, but it is possible and can make for interesting situations.

One of the reasons for the primary key scheme I will employ in logical models is to add a key named < entityName > Id as the identifier for entities, so the name of the entity is easily identifiable and lets us easily know where the original source of the attribute is. Also, we can see the attribute migrated from entity to entity even without any additional documentation. For example, in the Album entity example, we instinctively know that the ArtistId attribute is a foreign key and most likely was migrated from the Artist entity just because of the name alone.

Domains

In Chapter 1, the term “domain” referred to a set of valid values for an attribute. In IDEF1X, you can formalize domains and define named, reusable specifications known as domains, for example:

• String: A character string
• SocialSecurityNumber: A character value with a format of ###-##-####
• PositiveInteger: An integer value with an implied domain of 0 to max(integer value)
• Truth: A five-character value with a domain of ('FALSE','TRUE')

Domains in the specification not only allow us to define the valid values that can be stored in an attribute but also provide a form of inheritance in the datatype definitions. Subclasses can then be defined of the domains that inherit the settings from the base domain. It is a good practice to build domains for any attributes that get used regularly, as well as domains that are base templates for infrequently used attributes. For example, you might have a character type domain where you specify a basic length, like 60. Then, you may specify common domains, like name and description, to use in many entities. For these, you should choose a reasonable length for the values, plus you could include requirements that the data in the column cannot be just space characters to prevent a user from having one, two, or three spaces each look like different values—except in the rare cases where that is desirable.

Regardless of whether or not you are using an automated tool for modeling, try to define common domains that you use for specific types of things (applying a common pattern to solve a common problem). For example, a person’s first name might be a domain. This is cool because you don’t have to answer “Hmm, how long to make a person’s name?” or “what is the format of our part numbers?” or any similar questions more than once. After you make a decision, you just use what you have used before.

Early in the modeling process, you’ll commonly want to gather a few bits of information, such as the general type of the attribute: character, numeric, logical, or even binary data. Determining minimum and maximum lengths may or may not be possible, but the more information you can gather without crushing the process the better. Another good thing to start is documenting the legal values for an attribute that is classified as being of the domain type. This is generally done using some pseudocode or in a textual manner, either in your modeling tool or even in a spreadsheet.

It is extremely important to keep these domains as implementation-independent datatype descriptions. For example, you might specify a domain of GloballyUniqueIdentifier, a value that will be unique no matter where it is generated. In SQL Server, a unique identifier could be used (GUID value) to implement this domain. In another operating system (created by a company other than Microsoft, perhaps) where there is not exactly the same mechanism, it might be implemented differently; the point is that this value is statistically guaranteed to be unique every time it is generated. The conceptual/logical modeling phase should be done without too much thinking about what SQL Server can do, if for no other reason than to prevent you from starting to impose limitations on the future solution prior to understanding the actual problem. Another sort of domain might be a set of legal values, like if the business users had defined three customer types, you could specify the legal string values that could be used.

When you start the physical modeling of the relational structures, you will use the same domains to assign the implementation properties. This is the real value in using domains. By creating reusable template attributes that will also be used when you start creating columns, you’ll spend less effort and time building simple entities, which make up the bulk of your work. Doing so also provides a way for you to enforce companywide standards, by reusing the same domains on all corporate models (predicated, of course, on you being diligent with your data modeling processes over time!).

Later on, implementation details such as exact datatypes, constraints, and so forth will be chosen, just to name a few of the more basic properties that may be inherited (and if Microsoft adds a better datatype in the future, you can simply change all of the columns with that domain type to the new type). Since it is very likely that you will have fewer domains than implemented attributes, the double benefit of speedy and consistent model assembly is achieved. However, it is probably not overly reasonable or even useful to employ the inheritance mechanisms when building tables by hand. Implementation of a flat domain structure is enough work without a tool.

As an example of a domain hierarchy, consider this set of character string domains:

Here, String is the base domain from which you can then inherit Name and Description. FileName, FirstName, and LastName are inherited from Name. During logical modeling, this might seem like a lot of work for nothing, because most of these domains will share only a few basic details, such as not allowing NULLs or blank data. However, FileName may be optional, whereas LastName might be mandatory. Setting up domains for as many distinct attribute types as possible is important, in case rules or datatypes are discovered that are common to any domains that already exist. Things get good when you need to change all of your datatypes for all string types, for example, if you decide to make a blanket change from ANSI character sets to UNICODE, or to implement compression on all description type attributes but not name ones.

Domains are a nice feature of IDEF1X (and other methodologies or tools that support them). They provide an easy method of building standard attribute types, reducing both the length of time required for repeating common attribute types and the number of errors that occur in doing so. Specific tools implement domains with the ability to define and inherit more properties throughout the domain chain to make creating databases easier. During logical modeling, domains might optionally be shown to the right of the attribute name in the entity:

So if I have an entity that holds domain values for describing a type of person, I might model it as follows:

To model this example, I defined four domains:

• SurrogateKey: The surrogate key value. (Implementation of the surrogate should not be implied by building a domain, so later, this can be implemented in any manner.) I could also choose to use a natural key.
• Description: Holds the description of “something” (can be 60 characters maximum).
• PersonFirstName: A person’s first name (30 characters maximum).
• PersonLastName: A person’s last name (50 characters maximum).

The choice of the length of name is an interesting one. I searched on Google for “person first name varchar” and found lots of different possibilities: 10, 35, unlimited, 25, 20, and 15— all on the first page of the search! Just as you should use a consistent naming standard, you should use standard lengths every time like data is represented, so when you hit implementation, the likelihood that two columns storing like data will have different definitions is minimized.

During the implementation phase, all of the domains will get mapped to some form of datatype or, if you are so inclined, a user-defined type in SQL Server. However, the future implementation isn’t quite the point at this point of the process. The point of a domain in the logical model is to define common types of storage patterns that can be applied in a common manner, including all of the business rules that will govern their usage.

Naming

Attribute naming is a bit more interesting than entity naming. I stated earlier that my preference is to use singular, not plural, entity names. The same issues that apply in entity naming are technically true for attribute naming (and no one disagrees with this!). However, until the logical model is ­completed, the model may still have attribute names that are plural. Leaving a name plural during early modeling phases can be a good reminder that you expect multiple values, but in my final relational table model, all attributes are almost always singular. For example, consider a Person entity with a ­Children attribute identified. The Person entity would identify a single person, and the Children attribute would identify sons and daughters of that person.

The naming standard I follow is very straightforward and is intended to guide the names without being overly specific:

• Avoid repeating the entity name in the attribute name for most attributes except where it is natural to do so. The most common place where we include the table name is with some primary key attributes, particularly surrogate keys, since the key is specific for that table and some code values that are used in common language. For attributes that are not migrated to other entities, there is no need to prefix the name with the entity.
• Choose an attribute name to reflect precisely what is contained in the attribute and how it relates to the entity.
• Use abbreviations rarely in attribute names, especially for the conceptual/logical model. Every word ought to be spelled out in its entirety. If, for some reason, an abbreviation must be used (for example, due to the naming standard currently in use), a method should be put into place to make sure the abbreviation is used consistently, as discussed earlier in this chapter. For example, if your organization has a ZRF “thing” that is commonly used and referred to in general conversation as a ZRF, you might use this abbreviation. In general, however, I recommend avoiding abbreviations in all naming unless the client is insistent.
• Include no information in the name other than that necessary to explain the meaning of the attribute. This means no Hungarian notation of the type of data it represents (e.g., ) or prefix notation to tell you that it is, in fact, an attribute.
• End the name with a suffix that denotes general usage (often called a classword). It helps to standardize names and to let the user know the purpose of the column. Examples are:
  • UserName: A textual string referencing the name of the user. Whether or not it is a or is immaterial.
  • PaymentId: An identifier for a payment, usually a surrogate key. It can be implemented using a GUID, an integer, a random set of characters, and so on.
  • EndDate: The date when something ends. It does not include the time.
  • SaveTime: The point in time when the row was saved.
  • PledgeAmount: An amount of money (using a , or , or any sort of type).
  • DistributionDescription: A textual string that is used to describe how funds are distributed.
  • TickerCode: A short textual string used to identify a ticker row.

As in pretty much all things, consistency is the key to proper naming, so if you or your organization does not have a standard naming policy, developing one is worthwhile, even if it is very simple in nature. The overarching principle of my naming philosophy is to keep it simple and readable and to avoid all but universally standard corporate abbreviations. This standard will be followed from logical modeling into the implementation phase. Whatever your standard is, establishing a pattern of naming will make your models easy to follow, both for yourself and for your programmers and users. As always, any standard is better than no standard, and sometimes just the statement “make new columns and tables use the same names as those around them” is a good enough start if you have existing systems you are modifying. Trying to enforce a new standard on new work in an old system can make life more complicated than it is worth.

Identifying Relationships

The concept of a relationship being identifying is used to indicate containership, that the essence (defined as the intrinsic or indispensable properties that serve to characterize or identify something) of the child instance is defined by the existence of a parent. Another way to look at this is that generally the child in an identifying relationship is an inseparable part of the parent. Without the existence of the parent, the child would make no sense.

The relationship is drawn as follows:

To implement this relationship in the model, the primary key attribute(s) are migrated to the primary key of the child. Because of this, the key of a parent instance is needed to be able to identify a child instance record, which is why the name “identifying relationship” is used. In the following example, you can see that the ParentId attribute is a foreign key in the Child entity, from the Parent entity.

The child entity in the relationship is drawn as a rounded-off rectangle, which, as mentioned earlier in this chapter, means it is a dependent entity. A common example is an invoice and the line items being charged to the customer on the invoice:

Without the existence of the invoice, the line items would have no purpose to exist. It can also be said that the line items are identified as being part of the parent.

Nonidentifying Relationships

In contrast to identifying relationships, where relationships indicated that the child was an essential part of the parent entity, the nonidentifying relationship indicates that the child represents a more informational attribute of the parent.

When implementing the nonidentifying relationship, the primary key attribute is not migrated to the primary key of the child. It is denoted by a dashed line between the entities. Note too that the rectangle representing the child now has squared off corners, since it stands alone, rather than being dependent on the Parent:

This time, the key attribute of the parent will not migrate to the primary key of the child entity; instead, it will be in the nonprimary-key attributes.

Taking again the example of an invoice, consider the vendor of the products that have been sold and documented as such in the line items. The product vendor does not define the existence of a line item, because with or without specifying the exact vendor the product originates from, the line item still makes sense.

The difference between identifying and nonidentifying relationships can be tricky but is essential to understanding the relationship between tables and their keys. If the parent entity defines the need for the existence of the child (as stated in the previous section), then use an identifying relationship. If, on the other hand, the relationship defines one of the child’s attributes, use a nonidentifying relationship.

Here are some examples:

• Identifying: You have an entity that stores a contact and another that stores the contact’s telephone number. defines the phone number, and without the contact, there would be no need for the .

• Nonidentifying: Consider the entities that were defined for the identifying relationship, along with an additional entity called ContactPhoneNumberType.. This entity is related to the entity ContactPhoneNumber, but in a nonidentifying way, and defines a set of possible phone number types (Voice, Fax, etc.) that a ContactPhoneNumber might be. The type of phone number does not identify the phone number; it simply classifies it. Even if the type wasn’t known, recording the phone number could still be valid, as the number still has informational merit. However, a row associating a contact with a phone number would be useless information without the contact’s existence.

The ContactPhoneNumberType entity is commonly known as a domain entity or domain table, as it serves to implement an attributes domain in a nonspecific manner. Rather than having a fixed domain for an attribute, an entity is designed that allows programmatic changes to the domain with no recoding of constraints or client code. As an added bonus, you can add columns to define, describe, and extend the domain values to implement business rules. It also allows the client user to build lists for users to choose values with very little programming.

While every nonidentifying relationship defines the domain of an attribute of the child table, sometimes when the row is created, the values don’t need to be selected. For example, consider a database where you model houses, like for a neighborhood. Every house would have a color, a style, and so forth. However, not every house would have an alarm company, a mortgage holder, and so on. The relationship between the alarm company and bank would be optional in this case, while the color and style relationships would be mandatory. The difference in the implemented table will be whether or not the child table’s foreign key will allow nulls. If a value is required, then it is considered mandatory. If a value of the migrated key can be null, then it is considered optional.

The optional case is signified by an open diamond at the opposite end of the dashed line from the black circle, as shown here:

In the mandatory case, the relationship is drawn as before, without the diamond. Note that an optional relationship means that the cardinality of the relationship may be zero, but a mandatory relationship must have a cardinality of one or greater (cardinality refers to the number of values that can be related to another value, and the concept will be discussed further in the next section).

So why would you make a relationship optional? Consider once again the nonidentifying ­relationship between the invoice line item and the product vendor. The vendor in this case may be required or not required as the business rules dictate. If it is not required, you should make the relationship optional.

For a one-to-many, optional relationship, consider the following:

The invoiceLineItem entity is where items are placed onto an invoice to receive payment. The user may sometimes apply a standard discount amount to the line item. The relationship, then, from the invoiceLineItem to the discountType entity is an optional one, as no discount may have been applied to the line item.

For most optional relationships like this, there is another possible solution, which can be modeled as required, and in the implementation, a row can be added to the discountType table that indicates “none.” An example of such a mandatory relationship could be genre to movie in a movie rental system­database:

The relationship is genre<classifies>movie, where the genre entity represents the “one” and movie represents the “many” in the one-to-many relationship. Every movie being rented must have a genre, so that it can be organized in the inventory and then placed on the appropriate rental shelf.

Role Names

A role name is an alternative name you can give an attribute when it is used as a foreign key. The purpose of a role name is to clarify the usage of a migrated key, because either the parent entity is generic and a more specific name is needed or the same entity has multiple relationships. As attribute names must be unique, assigning different names for the child foreign key references is often necessary. Consider these tables:

In this diagram, the Parent and Child entities share two relationships, and the migrated attributes have been role named as FirstParentPrimaryKey and SecondParentPrimaryKey. In diagrams, you can indicate the original name of the migrated attribute after the role name, separated by a period (.), as follows (but usually it takes up too much space on the model when you are using it):

As an example, say you have a User entity, and you want to store the name or ID of the user who created a DatabaseObject entity instance as well as the user that the DatabaseObject instance was created for. It would then end up as follows:

Note that there are two relationships to the DatabaseObject entity from the User entity. Due to the way the lines are drawn on a diagram, it is not clear from the diagram which foreign key goes to which relationship. Once you name the relationship (with a verb phrase, which will be covered later in this chapter), the keys’ relationships will be easier to determine, but often, determining which line indicates which child attribute is simply trial and error.

Relationship Cardinality

The cardinality of the relationship denotes the number of child instances that can be inserted for each parent of that relationship. Cardinality may seem like a fringe topic because the logic to implement can be tricky, but the reality is that if the requirements state the cardinality, it can be important to document the cardinality requirements and to implement restrictions on cardinality in the database constraints where possible. At this point, however, logical modeling is not about how to implement but about documenting what should be. We will talk implementation of data constraints starting in Chapter 6 when we implement our first database, and even more in Chapter 7, the data protection chapter.

Figures 3-1 through 3-6 show the six possible cardinalities that relationships can take. The cardinality indicators are applicable to either mandatory or optional relationships.

This is a good example of a zero-or-one to one-or-more relationship, and a fairly interesting one at that. It says that for a guardian instance to exist, a related student must exist, but a student need not have a guardian for us to wish to store the student’s data. Next, let’s consider the case of a club that has members with certain positions that they should or could fill, as shown in Figures 3-7 through 3-9.

Figure 3-7 shows that a member can take as many positions as are possible. Figure 3-8 shows that a member can serve in no position or one position, but no more. Finally, Figure 3-9 shows that a member can serve in zero, one, or two positions. They all look pretty similar, but the Z or 0–2 is important in signifying the cardinality.

Verb Phrases (Relationship Names)

Relationships are given names, called verb phrases, to make the relationship between a parent and child entity a readable sentence and to incorporate the entity names and the relationship cardinality. The name is usually expressed from parent to child, but it can be expressed in the other direction, or even in both directions. The verb phrase is located on the model somewhere close to the line that forms the relationship:

The relationship should be named such that it fits into the following general structure for reading the entire relationship: parent cardinality – parent entity name – relationship name – child cardinality – child entity name.

For example, the following relationship

would be read as “one Contact is phoned using zero, one, or more PhoneNumbers.”

Of course, the sentence may or may not make perfect sense in normal conversational language; for example, this one brings up the question of how a contact is phoned using zero phone numbers. If presenting this phrase to a nontechnical person, it would make more sense to read it as follows: “Each contact can have either no phone number or one or more phone numbers.”

The modeling language does not take linguistics into consideration when building this specification, but from a technical standpoint, it does not matter that the contact is phoned using zero phone numbers, since it follows that the contact would have no phone number. Being able to read the relationship helps you to notice obvious problems. For instance, consider the following relationship:

It looks fine at first glance, but when read as “one contactType classifies zero or one Contacts,” it doesn’t make logical sense. It means to categorize all of the contacts, a unique ContactType row would be required for each Contact, which clearly is not at all desirable. This would be properly modeled as follows:

which now reads, “one contactType classifies zero or more Contacts.”

Note that the type of relationship, whether it is identifying, nonidentifying, optional, or mandatory, makes no difference when reading the relationship. You can also include a verb phrase that reads from child to parent. For a one-to-many relationship, this would be of the following format: “One child instance (relationship) exactly one parent instance.”

In the case of the first example, you could have added an additional verb phrase:

The parent-to-child relationship again is read as “one Contact is phoned using zero, one, or more phoneNumbers.”

You can then read the relationship from child to parent. Note that, when reading in this direction, you are in the context of zero or one phone number to one and only one contact: “zero or one phoneNumbers may be used to phone exactly one contact.”

Since this relationship is going from many to one, the parent in the relationship is assumed to have one related value, and since you are reading in the context of the existence of the child, you can also assume that there is zero or one child record to consider in the sentence.

For the many-to-many relationship, the scheme is pretty much the same. As both entities are parents in this kind of relationship, you read the verb phrase written above the line from left to right and from right to left for the verb phrase written below it.

Information Engineering

The information engineering (IE) methodology is well known and widely used. Like IDEF1X, it does a very good job of displaying the necessary information in a clean, compact manner that is easy to follow. The biggest difference is in how this method denotes relationship cardinalities: it uses a crow’s foot instead of a dot and lines and dashes instead of diamonds and some letters.

Tables in this method are denoted as rectangles, basically the same as in IDEF1X. According to the IE standard, attributes are not shown on the model, but most models show them the same as in IDEF1X—as a list, although the primary key is usually denoted by underlining the attributes, rather than the position in the table. (I have seen other ways of denoting the primary key, as well as alternate/foreign keys, but they are all very clear.) Where things get very different using IE is when dealing with relationships.

Just like in IDEF1X, IE has a set of symbols that have to be understood to indicate the cardinality and ownership of the data in the relationships. By varying the basic symbols at the end of the line, you can arrive at all of the various possibilities for relationships. Table 3-3 shows the different symbols that can be employed to build relationship representations.

Table 3-3. Information Engineering Symbols

Symbol	Relationship Type	Description
	Many	The entity on the end with the crow’s foot denotes that there can be greater than one value related to the other entity.
	Required	The key of the entity on the other end of the relationship is required to exist. A line on both ends indicates that a child is required for a parent row to exist, just like a “P” on the end of an IDEF1X model.
	Optional	This symbol indicates that there does not have to be a related instance on this end of the relationship for one to exist on the other. It can appear at the parent or the child.
	Nonrequired	A set of dashed lines on one end of the relationship line indicates that the migrated key may be null.

Figures 3-11 through 3-14 show some examples of relationships in IE.

I have never felt that this notation was as clean as IDEF1X, because much of the symbology seems a bit complicated to master (I have heard the same about IDEF1X from crow’s feet users as well, so it could just be a taste thing). IE conveys the information well though and is likely to be used in some of the documents that you will come across in your work as a data architect or developer. IE is also not always fully implemented in tools; however, usually the circle, dashes, and crow’s feet are implemented properly.

Chen ERD

The Chen Entity Relationship Model (ERD) methodology is quite a bit different from IDEF1X, but it’s pretty easy to follow and largely self-explanatory. You will seldom see this methodology anywhere other than in academic texts, but since quite a few of these types of diagrams are on the Internet, it’s good to understand the basics of the methodology. Here’s a very simple Chen ERD diagram showing the basic constructs:

Each entity is again a rectangle; however, the attributes are not shown in the entity but are instead attached to the entity in circles. The primary key either is not denoted or, in some variations, is underlined. The relationship is denoted by a rhombus, or diamond shape.

The cardinality for a relationship is denoted in text. In the example, it is 1 and Only 1 Parent rows < relationship name > 0 to Many Child rows. The primary reason for including the Chen ERD format is for contrast. Several other modeling methodologies—for example, Object Role Modeling (ORM) and Bachman—implement attributes in this style, where they are not displayed in the rectangle.

While I understand the logic behind this approach (entities and attributes are separate things), I have found that models I have seen using the format with attributes attached to the entity like this seemed overly cluttered, even for fairly small diagrams. The methodology does, however, do an admirable job with the logical model of showing what is desired and also does not rely on overcomplicated symbology to describe relationships and cardinality.

Visio

Visio is a tool that many developers use for designing databases; often, they already have it in their tool belts for other types of drawings and models (such as process flow diagrams). By nature, Visio is a multipurpose drawing tool and, as such, does not lend itself well to being a fully featured database design tool. That said, Visio is not the world’s worst tool to design a database either. It does lack a refined manner of going from conceptual to logical and finally to an implemented database, but unless you are doing serious enterprise-level designs, this limitation may not matter much to you. (Also, like many of us, you may not have the ducats to shell out for a more fully featured tool, and using Visio is better than just using Management Studio’s diagrams.)

Models created using Visio have a distinctive style that shares some features with object-oriented design tools. While the tool supports many of the features of more powerful tools, the picture of the model is pretty basic:

The arrow points to the parent in all cases but does not indicate ownership, cardinality, or even optionality. It tells you what columns are primary keys in two ways (using the line and the “PK”), as well as telling you what columns are part of foreign keys with “FK” plus a number, in case there are more than one. Alternate keys are denoted with “U” plus a number. In the preceding model, the Parent entity/table has two alternate keys.

Visio implements a good amount of detail to define columns, include comments, and set cardinalities via dialogs and editors. All in all, the tool is not a terrible choice for modeling if it is the only one available, but far better choices are out there for what, over the lifetime of the tool, will be a pretty reasonable amount of money.

Management Studio Database Diagrams

The database diagramming capability built into SQL Server Management Studio is not a modeling tool, though often, it can be a developer’s only available tool for modeling. It is a very useful tool to provide a graphical view the structure of an implemented database (a picture really is worth a thousand words!), but because it works directly against the implemented tables, it is not overly useful for design but only for the final implementation. You can use this view to modify structures as well, but I would not suggest it. I typically suggest that one use the T-SQL code in the Management Studio query window to make table changes. (All table structures in this book will be done in this way, and doing so is a best practice for repeatability purposes. I will talk more on this in the latter half of the book.) New to SQL Server 2012 is a tool called SQL Server Developer Tools that will allow you to create a database as well. In this book, I will largely be tool agnostic, choosing to do almost everything via scripts, because it will behoove you as a designer/coder to know what the tools do behind the scenes anyhow.

You will find that Management Studio diagrams are excellent tools when you are looking at a database in an environment where you have no other tools, just to look at the structures. As such, the following is an example of a one-to-many relationship in Management Studio:

The primary keys are identified by the little key in an attribute. The relationship is denoted by the line between the entities, with the “one” end having a key and the “many” end having an infinity sign. You can display the entities in several formats by just showing the names of the entities or by showing all of the attributes with datatypes, for example:

While the database diagram tool does have its place, I must stress that it isn’t a full-featured data modeling tool and shouldn’t be used as such if you can avoid it. I included coverage of the SQL Server modeling capabilities here because it’s included in SQL Server, and in some situations, it’s the best tool you may have access to. It does give access to all implementation-specific features of SQL Server, including the ability to annotate your tables and columns with descriptive information. Unfortunately, if you decide to implement a relationship in a trigger, it will not know that the trigger exists. (I cover triggers in Chapter 6 and Chapter 7, so if you have no idea what a trigger is right now, don’t worry.)

In most cases, the SQL Server tool isn’t the optimal way to see actual relationship information that is designed into the database, but it does offer a serviceable look at the database structure when needed.