This is an old revision of the document!


Database Design Fundamentals

Database design is an important task for each software developer. It is in most software development projects crucial to understand the real world problem thoroughly and be able to design the most simple database design possible. The key to success is as well a good understanding of the problem given as a the ability to simplify the real world to a good model. The most talented designers master the art of designing a data model that is universal and versatile and continues to work unchanged when there will be added entities and attributes in the future.

Entities

The real world is full of objects. If you look around you and think a bit you will find

  1. physical and tangible objects like houses, dogs, cars etc.
  2. virtual objects like orders, requests, code libraries, international organizations, countries etc.

We call each class of objects of the same type entity (from latin ens being).

An entity is usually represented in software by a form. In table-oriented databases, an entity can also very easily be understood and represented as a table (consisting of rows and columns). In Protogrid, each entity is defined as a Proto, a special Design Card that is used to define the attributes, appearance and behaviour of Data Cards.

Records = Tuples = Rows = Data Cards

Entity records have different names in different names in literature. In table-oriented databases it is represented as a table row. More generally it is called a tuple, i.e. a set of attributes defined in an entity. In Protogrid, records are represented as Data Cards An entity usually has many records. Each tuple has a unique key that allows to identify the tuple. In Protogrid, that key is called a Card Key.

Attributes

An entity has attributes. Each attribute describes a part of the entity. In table-oriented databases, an an attribute is represented as a column table column. In Protogrid, attributes are represented as fields on a Data Card, and are defined using Field Definitions on the Proto.

As an example, a house might have the following attributes:

  • its address
  • the number of floors
  • the type of roof
  • the color of the front
  • the number of parking lots
  • etc.

An attribute is typically of a specific data type, like:

  • text field
  • number field
  • date
  • time
  • date and time
  • etc.

Relations

There are relations between entities:

  • mother-child-relations: there might be many, one or no child, we call this a 1-to-many-relation and write 1:m
  • child-mother-relation: there is only one mother for each child, we call this a m-to-1-relation and write m:1

Many times we see mother-child-relations:

  • an organization has members, hence
    • there is a 1:m-relation between the organization and its members
    • the organization has (no, one or usually) many members
    • a member belongs to an organization
  • an organization has different office locations
    • there is a 1:m-relation between the organization and its office locations
    • the organization has (no, one or usually) many office locations
    • an office location belongs to exactly one organization
  • a house has floors
  • a floor has rooms
  • a room has doors

How do we represent these relations in software's user interface?

  • the mother entity is form with fields
  • the child entity is also a form with fields
  • on the mother form we have a list of all children
  • on the child form we have a field that shows us the name of the mother and a button or link to go to the mother form

We this knowledge we can build many different types of applications.

How to implement 1:m-relations in Protogrid
  1. create the proto for the child entity
  2. create the proto for the mother entity
  3. add a table view to the mother proto which relates to the child proto
  4. add a relation field to the child proto which relates to the mother proto

That all folks! It is very simple in Protogrid. This is where Protogrid truly shows its Rapid Application Development capabilities.

There is one more important variant:

The n:m-relation

Sometimes a child has not only one related entity (like a mother - there is only one for each child on this planet) but many. The closest example to think of are siblings.

Examples:

  1. an order might include several articles and an article might be part of several orders
  2. a person might be member of several organizations and an organization has usually several members

Any n:m-relation might be split into two 1:m-relations

Examples:

  1. there are the three entities order, article and article-in-order
    • the order has as attributes: order number, order date
    • the article has as attributes: article number, article description, price
    • the article-in-order has as attributes: the order number, the article number - just two attributes that is usually enough
  2. there are the three entities organization, person and member
    • the organization has as attributes: organization ID, name
    • the person has as attributes: person ID, name, age
    • the member has as attributes: organization ID, person ID - just two attributes
How to create an n:m-relation in Protogrid
  1. create the first proto (e.g. the organization)
  2. create the second proto (e.g. the person)
  3. create the relation proto with only two fields of type relation
  4. go to the first proto and add a tableview with the relation proto
  5. go to the second proto and add a tableview with the relation proto

Now you can see in the first proto in a tableview all relations to the other proto. And you can see in the second proto in a tableview all relations to the first proto, which is exactly what we need in most cases.

Summary

  • Entity ~ form ∼ table
  • Tuple ~ row in a table
  • Attribute ~ column in a table
Print/export