Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision | ||
protogrid:database_design_fundamentals [2017-11-16 04:27] – mmu | protogrid:database_design_fundamentals [2018-03-25 22:38] – dru | ||
---|---|---|---|
Line 1: | Line 1: | ||
====== Database Design Fundamentals ====== | ====== 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. | + | 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 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 === | + | ==== Entities |
The real world is full of objects. If you look around you and think a bit you will find | The real world is full of objects. If you look around you and think a bit you will find | ||
- // | - // | ||
Line 10: | Line 10: | ||
We call each class of objects of the same type //entity// (from latin ens //being//). | 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. An entity can also very easily be understood and represented as a table (consisting of rows and columns). | + | 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 // |
- | == Records = Tuples = Rows = 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//. | + | Entity records have 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 //[[protogrid: |
- | == Attributes == | + | ==== Attributes |
- | An entity has attributes. Each attribute describes a part of the entity. | + | An entity has attributes. Each attribute describes a part of the entity. |
- | A house might have the following attributes: | + | As an example, a house might have the following attributes: |
* its address | * its address | ||
* the number of floors | * the number of floors | ||
Line 25: | Line 25: | ||
* the number of parking lots | * the number of parking lots | ||
* etc. | * etc. | ||
- | |||
- | Attributes are usually fields on a form. | ||
- | An attribute is a column in the table. | ||
An attribute is typically of a specific data type, like: | An attribute is typically of a specific data type, like: | ||
* text field | * text field | ||
* number field | * number field | ||
- | * list of numbers | ||
- | * list of text strings | ||
* date | * date | ||
* time | * time | ||
Line 39: | Line 34: | ||
* etc. | * etc. | ||
- | === Relations === | + | ==== Relations |
- | There are **relations** between entities: | + | Often there are **relations** between entities. |
- | | + | |
- | * child-mother-relation: | + | === The 1:n-relation (Mother-Child Relations) === |
+ | For one mother there might be many, one or no child. We call this a 1-to-many-relation and write 1:n, with //n// representing the variable number of children. //1// and //n// are often called the // | ||
Many times we see mother-child-relations: | Many times we see mother-child-relations: | ||
* an organization has members, hence | * an organization has members, hence | ||
- | * there is a 1:m-relation between the organization and its members | + | * there is a 1:n-relation between the organization and its members |
- | * the organization has (no, one or usually) many members | + | * the organization has (no, one or usually many) members |
* a member belongs to an organization | * a member belongs to an organization | ||
* an organization has different office locations | * an organization has different office locations | ||
- | * there is a 1:m-relation between the organization and its office locations | + | * there is a 1:n-relation between the organization and its office locations |
* the organization has (no, one or usually) many office locations | * the organization has (no, one or usually) many office locations | ||
* an office location belongs to exactly one organization | * an office location belongs to exactly one organization | ||
Line 57: | Line 53: | ||
* a room has doors | * a room has doors | ||
- | How do we represent these relations in software' | + | How do we represent these relations in a software' |
- | * the mother entity is form with fields | + | * the mother entity is a form with fields |
* the child entity is also a 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 mother form we have an embedded view showing |
* 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 | * 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 | + | This knowledge |
+ | |||
+ | == How to implement 1: | ||
+ | - create the [[Proto]] for the child entity | ||
+ | - create the Proto for the mother entity | ||
+ | - add a [[TableView]] to the mother Proto which relates to the child Proto | ||
+ | - add a relation [[field]] to the child Proto which relates to the mother Proto | ||
- | == How to implement 1: | + | That' |
- | - create the proto for the child entity | + | |
- | - create the proto for the mother entity | + | |
- | - add a table view to the mother proto which relates to the child proto | + | |
- | - 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: | + | There is one more important variant |
- | == The n: | + | === The n: |
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. | 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. | ||
Line 82: | Line 79: | ||
- a person might be member of several organizations and an organization has usually several members | - a person might be member of several organizations and an organization has usually several members | ||
- | Any n: | + | For the sake of simplicity any n: |
Examples: | Examples: | ||
Line 95: | Line 92: | ||
== How to create an n: | == How to create an n: | ||
- | - create the first proto (e.g. the organization) | + | - create the first [[Proto]] |
- | - create the second | + | - create the second |
- | - create the relation | + | - create the relation |
- | - go to the first proto and add a tableview | + | - go to the first Proto and add a [[TableView]] |
- | - go to the second | + | - go to the second |
- | Now you can see in the first proto in a tableview | + | In Cards of the first Proto you now can see all relations to the other Proto. |
- | And you can see in the second proto in a tableview | + | And in Cards of the second Proto you can see all relations to the first Proto, which is exactly what we need in these cases. |
=== Summary === | === Summary === | ||
- | * Entity ~ form ∼ table | + | * Entity ~ form ∼ table ~ Proto |
- | * Tuple ~ row in a table | + | * Record |
- | * Attribute ~ column in a table | + | * Attribute ~ column in a table ~ field |