By Karen Lopez on Thu, 02 Jul 2009 08:40:26 GMT

Ever have one of those long, back and forth email exchanges where when you’re done you feel as if you’ve written a few chapters of an upcoming book?  I’m going to be taking a few of these from my archive to share with others who may be getting the same types of questions from non-modelers.

Today, we will be discussing the question of why does resolving a many-to-many relationship require a new relationship?  Why do we have to create a new entity?

---

This is a fairly classical data modeling concept.  It might be referred to as an intersectional entity, a resolution entity, an associative entity, or in short form, a M:N entity.  Silverston calls this concept Intersection or Association entity.  Simsion and Witt call these Intersection entities, Associative entities, Resolution entities, or Relationship entities.  Riordan calls these Junction tables, even in the data model.  I’d say that the most common terms are Intersection or Associative entity, but I think it depends on what tool one uses and what types of data modeling books one reads.

A typical example might start out with a many-to-many relationship, say between cars and people:

PERSON >-|---owns----o-< CAR

This is a many-to-many relationship:  A person may own cars and a car may be owned by more than one person.  In my made up example, my business rule is that a car has to be owned by at least one person (which is sort of bending the real world rules, but bear with me).  We’ll also ignore the fact that there are other relationships between cars and people.  We’ll focus just on ownership.

We can’t leave many-to-many relationships that way in a relational database, so we need to resolve them.  There are also normally real business reasons why they need to be resolved, but I’ll leave that for another discussion, too.

To resolve a many-to-many, you create a new entity, in my example, OWNERSHIP:

PERSON - ||---registers----0-< OWNERSHIP >-|-----is registered on-----||-CAR

OWNERSHIP keeps track of the relationship between a specific person and a car.  It becomes the list of just two things: a person and a car.

Karen owns car 1234

Kirstin owns car 2345

Rob owns car 1234

Rob owns car 3456

In this list, notice that Karen and Rob jointly own car 1234.  Car 2345 is owned only by Kirstin and car 3456 is owned only by Rob.  Karen owns only one car, Rob owns two, and Kirstin owns one.  We can also assume that Richard owns no cars (according to the data) because he has no entry in OWNERSHIP. 

In the attributed model, the entity OWNERSHIP would look like this:

OWNERSHIP

=======================

Person.PersonID (fk)

Car.CarID (fk)

…in a very simple world where we don’t worry about time.   The real world reason why we need these associative entities is because they almost always involve an aspect of time and other attributes, but we’ll ignore that for now.

Each foreign key in this associative entity came from the relationship from CAR and PERSON to OWNERSHIP.  That’s why we need two relationships.  We could not drop one of them, because each plays the part of associating the two concepts to each other, one pair at a time. 

The PARTY AFFILIATION entity is a special case of the associative entity above because it started out (at least conceptually), as a recursive relationships (a relationship from an entity to itself).  These are more difficult to draw in ASCII data modeling, so I’ll just duplicate the entity:

PARTY >-o-----is affiliated with-----o-< PARTY

So just imagine the relationship being “dog eared” back to the same entity.

We created PARTYAFFILIATION  to do the same job as OWNERSHIP:

PARTY -||-------is affiliated via-----o-< PARTY AFFILIATION >-0----------is affiliated via-----||- PARTY

It would result in an associative entity that looked like this:

PARTYAFFILIATION

=====================

PARTY.PARTYID (fk)

PARTY.PARTYID (fk)

…which we can’t have, since the foreign key would have migrated twice with the same name.  So we rolenamed one of the relationships for it to be:

PARTYAFFILIATION

=====================

PARTY.PARTYID (fk)

PARTY.SUBPARTYID (fk)

Personally, I prefer to rolename both in these cases so it is very clear which role the foreign key is taking, but it works either way.

So if you check out your books on data modeling for the terms I mentioned in the beginning, you might come up with some more examples of why many-to-many relationships resolve to two relationships with an associative entity in the middle.

 

Technorati Tags: Data Model,M:N,Many to Many,Relationships,Associative Entity,Intersection Entity,Intersectional Entity,Recursive Entity

Comments (0)

Karen Lopez: Musings on Data, Process, and Architecture

By Karen Lopez on Fri, 22 May 2009 12:11:53 GMT

While researching how people plan on using the new data at Data.gov, I came across this competition sponsored by Google Sunlight Labs, O’Reilly Media, and TechWeb.

Apps for America 2 is a contest for developers of applications based on the datasets at data.gov.

There are cash awards, with first place winning $10,000 USD.  There’s even a Super Bonus Visualization Prize of $2500 for the best visualization of data.

Criteria

Awards will be judged based on the following criteria:

1. Transparency: Does the app help citizens see things they couldn't see before the app existed?

2. Permanence: Will the app be usable over a long period of time? Does the idea have survivability?

3. Design & Visualization: Does the app look great? Does the app visualize data in a new and interesting way?

Get those apps cranking.  I’d love to see a really great application come from the data community.

Apps for America 2: The Data.gov Challenge

Technorati Tags: Data.gov,contests,Data visualization

By Karen Lopez on Friday, May 22, 2009 12:10 PM

Peter Aiken, Jim Harris and I will be discussing the highlights and important data management trends from the recent DAMA / Wilshire Conferences Enterprise Data World held in Tampa in April via a webcast hosted by Wilshire Conferences.

This free webcast is taking place on Wednesday, 27 May at 1 PM Eastern.  Registration is free.

 

Wilshire Conferences WebEx Enterprise Site

By Karen Lopez on Thursday, May 21, 2009 5:13 PM

The US Federal CIO Council has developed a website for accessing raw datasets, data extraction, and data mining tools.

About

The purpose of Data.gov is to increase public access to high value, machine readable datasets generated by the Executive Branch of the Federal Government.

As a priority Open Government Initiative for President Obama's administration, Data.gov increases the ability of the public to easily find, download, and use datasets that are generated and held by the Federal Government. Data.gov provides descriptions of the Federal datasets (metadata), information about how to access the datasets, and tools that leverage government datasets. The data catalogs will continue to grow as datasets are added. Federal, Executive Branch data are included in the first version of Data.gov.

Participatory Democracy

Public participation and collaboration will be one of the keys to the success of Data.gov. Data.gov enables the public to participate in government by providing downloadable Federal datasets to build applications, conduct analyses, and perform research. Data.gov will continue to improve based on feedback, comments, and recommendations from the public and therefore we encourage individuals to suggest datasets they'd like to see, rate and comment on current datasets, and suggest ways to improve the site.

Goal

A primary goal of Data.gov is to improve access to Federal data and expand creative use of those data beyond the walls of government by encouraging innovative ideas (e.g., web applications). Data.gov strives to make government more transparent and is committed to creating an unprecedented level of openness in Government. The openness derived from Data.gov will strengthen our Nation's democracy and promote efficiency and effectiveness in Government.

Currently there are catalogs of data in the following areas available:

• Environmentally-relevant data (copper smelters, energy usages, brownfields, soil geochemistries, clean air statuses, weather trends, earthquakes, etc.)
• Demographic data (earnings, ages, etc.)
• National income and accounts (Gross Domestic Products, income levels, etc.)
• Regulatory alerts
• Patent applications and grant information

Datasets are available in XML, CSV/TXT, KML/KMZ and ESRI, and Map formats. For example, the following is a map based on Google Maps of the location of copper smelters:

In addition to datasets, there are several widgets and tools:

• FBI Widget (links to FBI information)
• H1N1 Flu Widget
• Employer Sponsored Insurance data extraction tool
• US Federal Spending by Agency data extraction tool
• Alerts Widgets
• Recall Widgets
• …too many to list.

It appears that the datasets available now are heavy on the earth sciences areas, but according to the FAQ, more datasets will be available.  There’s even a place to request new datasets.  Most surprising, to me, is the fact that the site offers the ability to rate the utility, usefulness, and ease of access for the data.  I wonder how many of us are providing that feature to our users?

The FAQ also gives short definitions of “data” and “metadata”.

I selected the Interactive Access To National Income and Product Accounts Tables and found a great deal of interesting metadata about this dataset, including:

• Agency that provided the data
• Release date
• Date updated
• Time period
• Frequency
• Description
• Keywords
• Unique ID
• Geographic coverage
• Collection Mode
• Data Dictionary
• Information Quality instrument
• Data Quality Certification
• Privacy & Confidentiality
• Technical Documentation

This data was available as a CSV and an XLS file.

I’m thinking there are going to be many new mashups circulated based on these datasets in the future. It appears that many of the datasets were already publicly available, but having a single go-to site for finding data and metadata is the right thing to do. 

Since this site proposed to be the source for government transparency, I’d love to see datasets about IT project costs, benefits, and risks, as well as project statuses.  I’d also like to see government enterprise architectural models provided as additional metadata.

What datasets would you like to see?  What formats do you think should be supported?

Finally, I want to see a Data.gov Widget to alert me (perhaps via Twitter) when new datasets are added or updated.

Data.gov

By Karen Lopez on Wed, 20 May 2009 19:23:07 GMT

I’ve heard from John P. Zachman that the first vendor product will be submitted for Zachman Certified status.

Zachman International announces today it’s working relationship with Sybase® to demonstrate PowerDesigner® 15 as a Zachman Certified™ – Enterprise Tool for implementing The Zachman Framework™.

Enterprise Architects have been clamoring for consistency in Enterprise Architecture and for ways to implement The Zachman Framework™ for many years. Sybase® has come up with an answer by seeking Zachman Certification in their flagship modeling and metadata management tool for Enterprise Architecture: PowerDesigner® 15.

With PowerDesigner® 15’s great strength in enterprise information management, it seemed a natural fit to evolve that strength into full-scale business planning using Enterprise Architecture and The Zachman Framework™. They have sought certification of their tool to support the Zachman Enterprise Architecture concept of building primitive models in order to derive business implementation composites. “Having a partner like the Zachman group, allows us to accelerate our move into Enterprise Architecture with the world-recognized authority on the subject.” - David Dichmann, Sr. Product Manager, Design Tools, Sybase®, Inc.

Zachman International has long been educating the community about transforming and integrating the primitive models of The Zachman Framework™ into implementation solutions. Many methodology experts have had a hard time envisioning that The Zachman Framework™ is about creating composite models for implementation from design primitives. Without integration and transformation, which allows the building of composite models, The Zachman Framework™ appears to them as a dis-joint set of classes. Now with PowerDesigner® 15 in the first phase of certification, implementation is attainable from a Tool perspective. “For the first time, we are seeing a vendor willing to step up to the plate and take Enterprise Architecture into the Business Design domain as it was originally intended to be, far beyond Information Systems and Technology.” - John A. Zachman, CEO, Zachman International, Inc.

Phase one of Zachman Certified™ – Enterprise Tool designation is directed at the Scope lists (Row 1) and the Inventory models (What Column) with their corresponding integrations and transformation matrices for analysis. This working relationship has allowed Zachman and Sybase® to assist a number of common customers to integrate PowerDesigner® 15 and The Zachman Framework™ to move their businesses ahead in stressful economic conditions.

Sybase® will demonstrate this proof of concept in the Zachman Modeling Workshops in Stockholm in May and Denver in June.

I believe that it is important that Zachman (John A. Zachman) work towards formal certifications of tools and other solutions since there are some vendor staff who have been claiming endorsements/certifications by John.  Having a formal program will clear up market confusion.

Press Release: Sybase® and The Zachman Framework