Differences
This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
protogrid:database_design_fundamentals [2017-11-15 19:09] – created gaw | protogrid:database_design_fundamentals [2018-03-26 07:49] (current) – [Database Design Fundamentals] 77.58.53.50 | ||
---|---|---|---|
Line 1: | Line 1: | ||
====== Database Design Fundamentals ====== | ====== Database Design Fundamentals ====== | ||
- | The real world is full of objects: | + | 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 to be able to design the most simple database design possible. The key to success |
- | * physical and tangible objects like houses, dogs, cars etc. | + | |
- | * virtual objects like orders, requests, code libraries etc. | + | |
- | We call each class of objects | + | ==== Entities ==== |
+ | The real world is full of objects. If you look around you and think a bit you will find | ||
+ | - // | ||
+ | - //virtual// objects like orders, requests, code libraries, international organizations, | ||
- | There are relations between entities: | + | We call each class of objects of the same type //entity// (from latin ens // |
- | * mother-child-relations: | + | |
- | * child-mother-relation: | + | 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 // |
- | * woman-mother-relation: each woman might be a mother or not, we call this a 1-to-c-relation and write 1:c | + | |
+ | ==== Records = Tuples = Rows = Data Cards ==== | ||
+ | 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 | ||
+ | |||
+ | ==== Attributes = Fields = Columns ==== | ||
+ | An entity has attributes. Each attribute describes a part of the entity. In table-oriented databases, an attribute is represented as a table column. In Protogrid, attributes are represented as // | ||
+ | |||
+ | 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 ==== | ||
+ | Often there are **relations** between entities. | ||
+ | |||
+ | === The 1:n-relation (Mother-Child Relations) === | ||
+ | For one mother | ||
+ | |||
+ | Many times we see mother-child-relations: | ||
+ | | ||
+ | * there is a 1: | ||
+ | * 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: | ||
+ | * 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 a software' | ||
+ | * the mother entity is a form with fields | ||
+ | * the child entity is also a form with fields | ||
+ | * on the mother form we have an embedded view showing 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 | ||
+ | |||
+ | This knowledge is already enough to build many different types of applications. | ||
+ | |||
+ | == How to implement 1:n-relations in Protogrid == | ||
+ | - create the [[Proto]] for the child entity | ||
+ | - create the Proto for the mother | ||
+ | | ||
+ | - add a relation | ||
+ | |||
+ | That's all folks! It really is that simple in Protogrid. This is where Protogrid truly shows its Rapid Application Development capabilities. | ||
+ | |||
+ | There is one more important variant of relations: | ||
+ | |||
+ | === 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. | ||
+ | |||
+ | Examples: | ||
+ | - an order might include several articles and an article might be part of several orders | ||
+ | - a person might be member of several organizations and an organization has usually several members | ||
+ | |||
+ | For the sake of simplicity any n:m-relation might be split into two 1:n-relations | ||
+ | |||
+ | Examples: | ||
+ | - there are the three entities order, article | ||
+ | * the order has as attributes: order number, order date | ||
+ | * the article has as attributes: article number, article description, | ||
+ | * the article-in-order has as attributes: the order number, the article number | ||
+ | - there are the three entities organization, | ||
+ | * 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 == | ||
+ | - create the first [[Proto]] (e.g. the organization) | ||
+ | - create the second Proto (e.g. the person) | ||
+ | | ||
+ | - go to the first Proto and add a [[TableView]] with the relation Proto | ||
+ | - go to the second Proto and add a TableView with the relation Proto | ||
+ | |||
+ | In Cards of the first Proto you now can see all relations to the other Proto. | ||
+ | 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 ==== | ||
+ | * Entity ~ form ∼ table ~ Proto | ||
+ | * Record ~ row in a table ~ Data Card | ||
+ | * Attribute ~ column in a table ~ field |