The Database Design Resource Center

The Universe - A Simplified Database Model

Entity Relationship Modeling

This is a play with a simplified model of The Universe, just to illustrate the power of ER Modeling. Let us start with the above model of The Universe:

It looks rather impossible to fit in The Universe here. And, well, although we are allowed to use many-to-many relationships in an early stage of the Analysis Phase, we must at some point resolve them:

They are a violation of the First Normal Form. The method for doing this is to create a new entity and two one-to-many relationships towards that new entity: We call this an "Intersection Entity" (sorry about the language). We also find that some renaming would do well:

Entity Relationship Modeling

Look at the left relationship. We can read this as:

:A given Universe element may have one or more Element Parts; while a given element part must belong to one and only one Universe element.

We can read the right relationship as:

A given Universe Element may be a part of one or more sets of Element Parts, but only once for a given Universe Element set.

This is not easy reading, I admit, so we have to visualize it a little. Let us first create the tables:

Entity Relationship Modeling

A little explaining first: The tables were generated from the ER model, using an internal naming standard. Let us keep it. What happened was that the ELEMENT_PARTS table (plural name as table) got two columns, one for each relationship. In this case, they also form the primary key together.

I can now insert a few rows into UNIVERSE_ELEMENTS. I start with the Earth, inserting the following:

1 Earth
2 Europe
3 Asia
4 Australia
5 North America
6 South America

I go on to concentrate on Europe, inserting:

7 Norway
8 Great Britain
9 Germany

Enough, now (it's just an example, but you get the picture).

Let us look at what we have in the table:

Entity Relationship Modeling

Let us connect the continents together to form The Earth by inserting all continents and The Earth ids into ELEMENT_PARTS. Let us look at the contents of ELEMENT_PARTS:

Entity Relationship Modeling

Now, let us do a SELECT here:

Entity Relationship Modeling

Similarly, we can find countries in Europe:

Entity Relationship Modeling

This little structure of the Universe represented by two tables can store elements and how they relate to each other, no matter what volume we are talking about. We navigate and make our selections based on the ID of the owning (or master) Universe element.

It will be a lot of ID's to remember, though. What if we needed to classify these elements in order to navigate faster?

Oh, we didn’t think about that in the Analysis Phase. We were too eager to build it and test it, so we didn't allow ourselves to think about that fact. Well, then it's back to the drawing board again. (Ever heard of Project Management Failure?) :-)

We need to classify our Universe elements:

Entity Relationship Modeling

Now we can ask the database:

  • Names of all the cities in Europe?
  • Which kinds of trees grow in South America?
  • Which planets belong to a given Solar System?
if we have those Element Types defined (City, Tree, Solar System)

In the real world, a system for organizing the Universe would contain more entities than these three (you think so too?), but this is how you may start your Entity Relationship modeling of the Universe in the Analysis Phase.

Good Luck!

Return to Database Design home

Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant

Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:

What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials

Database Normalization eBook:

Database Normalization eBook

Copyright © / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.