70-442: Data Modeling (LD, 2)

From Chapter 2 of “Pro SQL Server 2005 Database Design and Optimization”, by Louis Davidson.

Louis Davidson (LD) describes the process of converting a list of requirements into a blue print for the actual database design work.

LD recommends the IDEF1X modelling language, but the key thing is finding one you like and learn it. Interestingly he says that the Management Studio Database Diagrams is not a modeling tool – which of course makes sense as this works directly on underlying tables. I have however previosly used this against a mock database and I think this saves time as you can create the core schema, discuss this with colleagues and amend very quickly and then generate your initial CREATE script from this. The problem with this approach is that it tangles the implementation up with the conceptual and logical phases (but in most busy production environments you will find database diagrams a god sent – as a design tool as well!).

At this stage the process is concerned with entities and attributes, not tables and columns – however we are naming stuff and LD gives some good advice on Naming Conventions:

  • Entity names should never be plural (we agree – once you start usign ORMs you will notice this yourself anyway).
  • Don’t use prefixes for neither entities or attributes. Prefixes like “tbl” or “int” (Hungarian notation) is not only ugly but in T-SQL it is mostly quite obvious what you are dealing with.
  • Attribute names should also not be plural – but LD recommends using plural during the modeling phase as a reminder that an attribute covers several details and needs to be split into several later (e.g. Addresses).
  • For all it goes that you should use CamelCase, avoid special_characters and keep it short, sweet and precise.

Modeling Attributes

PK Attributes: At this stage add a simple surrogate key (i.e. a made up key, auto id) and then mark all natural candidates as natural keys (will most likely become indexes).

Alternate Keys: An alternate key is a primary key candidate – so this needs to be unique for the entity.

Foreign Keys: AKA “migrated attributes”. Normally named Id. Louis Davidson’s IDEF1X also allows saving these as resusable specifications, i.e. “SocialSecurityNumber: A character value with a format of ###-##-####”. These are then used when creating columns to save time and enforce consistency across projects.

Domains: A set of valid values for an attribute.

Attribute Naming: Same as entitties, but during modelling leaving a name plural can be a good reminder that you expect multiple values (e.g. Adresses – which will later be split into several columns).


Modeling Relationships

Identifying Relationships: Where the child object makes no sense without the parent, e.g. Invoice and InvoiceLine.

Nonidentifying Relationships: E.g. Invoice and Vendor.

Role Names: This is basically just renaming FKs if the parent entity name is too generic.

Relationship Cardinality: denotes the number of child instances that can be inserted for each parent of that relationship.

Recursive Relationships: Nothing new on this really.

Subtypes: special type of one-to-zero or -one relationship, used to indicate the type of a generic entity. This can be a Complete subtype like e.g. a person’s gender, or incomplete like employee type (i.e. they may be type 1, 2 or other). I guess these would often map to Enumerations.

Many-to-Many Relationship: Nothing new on this really.

Relationship Names: known as verb phrases during modelling – taking the time to include these in the model makes this a good source of documentation.


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: