The Universe - A Simplified Database Model

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:

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:

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:

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:

Now, let us do a SELECT here:

Similarly, we can find countries in Europe:

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:

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?

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