Click here to Skip to main content
15,868,292 members
Articles / Programming Languages / Visual Basic

Relational Datamodel for Beginners

Rate me:
Please Sign up or sign in to vote.
4.65/5 (16 votes)
22 Sep 2015CPOL14 min read 27.2K   662   19   7
The basic principle easy to understand. Then step forward to a concrete sample.

Two continuation-articles of this one:

  • Databinding for Beginners
    For those, who don't know it, Databinding is a new world of programming. But all Databinding requires a right understanding of what a DataModel is.
  • Programming against typed Dataset
    continues unfolding the subject so far, that you can develop databinding-driven Applications with several Forms, with avoidance of nasty code-smells.

Table of Contents

  1. The hierarchical Model
  2. The relational Model
  3. Technical Terms
  4. Set up a relational Datamodel
  5. Coding against a typed Dataset
  6. "DatasetOnly" - Data-Application without Database
  7. For Beginners: Learn Databinding before Database-Access
  8. The Sample-Application
  9. Summary

The hierarchical Model

As object-oriented Programmer we always deal with hierarchical structured Objects, that means: An Object contains other objects, either single Objects or even lists of other objects, for example a Form  has a ControlCollection, containing many Controls, eg. Textboxes, and each Textbox has a (single) vertical Scrollbar, and of course it has a Text, which has some chars.

This is the hierarchical model, where each object is either a (single) Attribute/Property of another object, or it is an element of a collection, which is property of the Owner-Object. And of course an object better should be element of only one collection - otherwise things become difficult or even inconsistent.

Hierarchical models - no matter how complex - can be visualized as tree-views - for instance watch the document-outline of a (not completely simple) WinForms-Form:

1030969/FormDesign.png

You see: The Form contains a TabControl, the TabControl contains TabPages, TabPages contain SplitContainers, SplitContainers contain SplitterPanels, SplitterPanels contain Groupboxes, Groupboxes contain DatagridViews, DatagridViews contains DatagridViewColumns...
Everything has its proper defined place within that hierarchical model.

Not sufficiant

But the thinking of an OOP-Programmer itself must change, when he comes in contact with relational modelling. Because unless reality often fits into a hierarchical model, it often does not fit ;P

See this little Xml:

XML
<?xml version="1.0" standalone="yes"?>
<PersonProfessionDts>
  <Profession Name="Police-Man">
    <Person Name="Gustav"/>
    <Person Name="Simone"/>
    <Person Name="Klaus"/>
  </Profession>
  <Profession Name="Punk">
    <Person Name="Sigi"/>
    <Person Name="Flo"/>
  </Profession>
</PersonProfessionDts>

It is about Persons and their Profession - each Person has one Profession, and a Profession has many Persons. The datamodel is structurated hierarchical and everything seems fine.

But how would you model, if Gustav has more Professions than one - in the evening he may style his hair and go clubbing (or however a punk does his job)?

XML
<?xml version="1.0" standalone="yes"?>
<PersonProfessionDts>
  <Profession Name="Police-Man">
    <Person Name="Gustav"/>
    <Person Name="Simone"/>
    <Person Name="Klaus"/>
  </Profession>
  <Profession Name="Punk">
    <Person Name="Gustav"/>
    <Person Name="Sigi"/>
    <Person Name="Flo"/>
  </Profession>
</PersonProfessionDts>

Now we have two Gustavs, and the data is inconsistent, corrupted, bullsh*** - name it as you like. The model is wrong, when in reality it is one Gustav, and in your model it is two.

Long story short: hierarchical models are not sufficiant. Even more un-modellable would be a Gustav with no profession at all :wtf:

The relational Model

Since hierarchical structures are irreparable unusable to model reality, the nesting of objects is banned completely from databasing. There was found something better: one can emulate nesting.

A relational Model only contains simple, flat lists of objects (named "table"). And instead of nesting records into (Parent-)records, a child-record only gets a Reference to its Parent.

(By the way, in Xml a table is represented simply by several Xml-Elements of same Tag-Name):

XML
<?xml version="1.0" standalone="yes"?>
<PersonProfessionDts>
  <Profession Name="Police-Man" ID="1"/>
  <Profession Name="Punk" ID="2"/>
  <Person Name="Gustav" ID="1" ProfessionId="1" />
  <Person Name="Simone" ID="2" ProfessionId="1" />
  <Person Name="Klaus" ID="3" ProfessionId="1" />
  <Person Name="Sigi" ID="4" ProfessionId="2"/>
  <Person Name="Flo" ID="5" ProfessionId="2"/>
</PersonProfessionDts>

This is the relational version of the very first Xml-Listing: You see two tables Profession and Person, and each Record got an additional Property, the ID, which is the "PrimaryKey". A PrimaryKey is unique in that table. You see two times ID="1" - this is valid, since theese doubles occure in different tables. (Now if i wanted, i could add a second Gustav, with another ID, and i still could distinguish them.)

Moreover records of the Person-Table now have the promised Reference to the Parent-Record - the "ForeignKey", here named ProfessionId. You see, the ForeignKey is not unique, three persons are Police-Men, and two Persons are Punk.

Ok, but how we achieve, that Gustav can have two professions, and how we can introduce "Ansgar", a gui without a Profession at all?

By changing the model:

XML
<?xml version="1.0" standalone="yes"?>
<PersonProfessionDts>
  <Profession Name="Police-Man" ID="1"/>
  <Profession Name="Punk" ID="2"/>
  <Person Name="Gustav" ID="1"/>
  <Person Name="Simone" ID="2"/>
  <Person Name="Klaus" ID="3"/>
  <Person Name="Sigi" ID="4"/>
  <Person Name="Flo" ID="5"/>
  <Person Name="Ansgar" ID="6"/>
  <PersonProfession PersonID="1" ProfessionID="1"/>
  <PersonProfession PersonID="1" ProfessionID="2"/>
  <PersonProfession PersonID="2" ProfessionID="1"/>
  <PersonProfession PersonID="3" ProfessionID="1"/>
  <PersonProfession PersonID="4" ProfessionID="2"/>
  <PersonProfession PersonID="5" ProfessionID="2"/>
</PersonProfessionDts>

Now we have the third table, PersonProfession, whiches records associate a person with a Profession. As promised  Gustav is associated with two Professions, and there is also the new Gui, Ansgar, without Profession.

Alternatively see the same data as tables:

Name ID
Person
Gustav 1
Simone 2
Klaus 3
Sigi 4
Flo 5
Ansgar 6
 ==> 
PersonID ProfessionID
PersonProfession
1 1
1 2
2 1
3 1
4 2
5 2
 <== 
ID Name
Profession
1 Police-Man
2 Punk

Technical Terms

DataRecord
an Object with several Values. You can also imagine a Record as Row of a table, although that may be not 100% correct.
Our Datamodel contains overall 14 Data-Records/-Rows

Entity / Table
the abstract of a Record. An Entity tells, out of what a Record consists. In a database Entities are represented by tables, and it is easy to imagine an Entity as table, but in general an Entity is a kind of "thinking-unit".
Because as well as an Entity is the abstract of a Record, it can bee seen as the abstract of a peace of reality. Maybe "Entity" can be defined as that, what Datamodel and Reality have in common? Sorry - i have a kind of feeling, what an Entity is, but i have troubles to define it.
Nevertheless - our Datamodel contains 3 Entities, two of them (Person, Profession) quite concret, the third - PersonProfession - is more abstract, but also is an Entity: When you associate Persons with Professions, these Associations are real, means: part of the reality, and our Model models that.

PrimaryKey / ID / PK
the property of a Record, or if you want the column of a table, which identifies the record. For that, PrimaryKeys must be unique.
In our Datamodel Person and Profession have Primarykeys, but PersonProfession has not (yet). Mostly it is recommended to equip each entity with a Primarykey, but it's not absolutely necessary.

ForeignKey / FK
the property of a Record, which refers to its Parent-Record. Foreignkeys don't need to be unique.
In our Datamodel PersonProfession has two ForeignKeys, one refers to Person, and the other to Profession.

Parent-/Child-(Entity/Table/Record/Row)
As seen in relational Datamodels the nesting of Records into each other is replaced by the Referencing-System. A Child-Record refers via its ForeignKey to a Parent-Record of another Entity. The principle is simple: The child-records ForeignKey targets that parent-record, whiches Primarykey equals the Foreignkey.
Since the Primkey is unique, but the Foreignkey is not, a ParentRecord can have many ChildRecords. But a ChildRecord has only one ParentRecord (per Foreignkey).
(Note: If there is no matching PrimKey in the ParentTable, then the ChildRow is invalid.)
In our Model the Profession "Police-Man", has three ChildRows as well as the Profession "Punk" has.
From the other side only "Gustav" has two Childrows, the other Persons have each one Child, and "Ansgar" has none.

Relation / 1:n-Relation/ Parent-Child-Relation / One-to-many-Relation
a Relation exists between two tables, if a ForeignKey of one refers to the PrimaryKey of the other. This is how the relational model "emulates" the hierarchical model, where a Parent-Object may contain several Child-Objects within a collection.

Many-to-many-Relation / m:n-Relation
basically there is only one kind of relation: one-to-many. The term "m:n-Relation" is stricktly speaking just a shortcut for two 1:n-Relations, connected in the way which is shown in our Datemodel.
Our Datamodel shows the m:n-relation between Person and Profession.
I took especially this construction to point out, how the relational model exceeds the capabilities of a hierarchical model: In a relational model a Child-Record can be subordered to more than one ParentTables.

Mediator-Table
the Mediator-Table is needed to model m:n-Relations - it is the table, which is subordered to both Parent-Tables. Each mediator-record associates a record from the one parent-table with a record from the other.
In our Datamodel PersonProfession is the Mediator-Table.
Very often Mediator-Tables contain more Informations than only the ForeignKeys. For instance the Mediator of Appointments will associate the Appointment-Members, and will add an Information about the DateTime, when the Appointment shall happen.

Set up a relational Datamodel

The most simple way ever to set up a Datamodel is to use the Designer for typed Datasets. Click Menu: "Project-Add_New_Item" - choose "Dataset", enter a meaningful Name for it, then you will come up here:

empty_Dataset

With ContextMenu add a DataTable:

Dts_-_Add-DataTable

Give it a meaningful Name, and - again with Contexmenu - add Columns to it:

Dataset_Add-Column

Configure the Columns in the Property-Grid. Then (Contextmenu) set the intended Column as PrimaryKey:

Dataset_Primkey-Menu

Note the Configuration in the Property-Grid: DataType=Int32, AllowDBNull=False, AutoIncrement=True.
There are many options to configure DataColumns, and to PrimKeys that is a usual Cofiguration.

In same manner create all Tables you need. Remember: PersonProfession has no PrimaryKey, but two ForeignKeys - they are normal Columns, of course of Typ Int32, since a ForeignKey-Value only can equal a PrimKey-Value, if they are of the same DataType.
Then use the mouse to drag the PrimaryKey on the ForeignKey:

Image 6

This will open the DataRelation-Configuration-Dialogue:

Image 7

The configuration as shown is the most usual. Especial DeleteRule.Cascade is valuable, because its effect is, when you delete a Person, all his ChildRows will be also deleted. Otherwise they would become invalid, because their Foreign-Keys would "hang", and that would cause errors.

In the end it should look like this:

Image 8

(note, that i set the column Name.AllowDbNull=False, since a Person with no name makes no sense.)

This is a typed Dataset, and at the same time it is the Entity-Relationship-Diagram (follow the link, if you don't know the term) of our Datamodel. Watch the Relation-Lines: The Primary-Key-end shows a very small Key, and the ForeignKey-End shows a very small Infinite-Symbol. These Symbols represent the direction of the one-to-many-Relation.

And in Background the Designer generates lots of typed classes for comfortably usage of it.

Coding against a typed Dataset

For instance you can loop now all persons, and output their Professions:

VB.NET
Dim OutputLines As New List(Of String)()
For Each rwPerson As PersonRow In PersonProfessionDts.Person
   OutputLines.Add(rwPerson.Name & ": ")
   For Each rwPersonProfession As PersonProfessionRow In rwPerson.GetPersonProfessionRows()
      'indent Professions-Namees
      OutputLines.Add("     " & rwPersonProfession.ProfessionRow.Name)
   Next
Next
MessageBox.Show(String.Join(Environment.NewLine, OutputLines), "Professions of Persons")

Output:

Professions of Persons
---------------------------
Gustav:
     Police-Man
     Punk
Sigi:
     Police-Man
Flo:
     Punk
Simone:
     Punk
Klaus:
     Police-Man
Ansgar:

(note Gustav with two, and Ansgar without Profession)

And the other way round: loop professions and output "their" persons:

VB.NET
Dim OutputLines As New List(Of String)()
For Each rwProfession As ProfessionRow In PersonProfessionDts.Profession
   OutputLines.Add(rwProfession.Name & ": ")
   For Each rwPersonProfession As PersonProfessionRow In rwProfession.GetPersonProfessionRows()
      'indent Person-Names
      OutputLines.Add("     " & rwPersonProfession.PersonRow.Name)
   Next
Next
MessageBox.Show(String.Join(Environment.NewLine, OutputLines), "Persons of Professions")

Output:

Persons of Professions
---------------------------
Police-Man:
     Gustav
     Klaus
     Sigi
Punk:
     Gustav
     Simone
     Flo

You see: Very trivial OOP-Code. A nested loop throught strongly typed Collections. That simplicity is intended: It's the generated classes of the typed Dataset, which provide to navigate such simple and OOP-like through the relational Datamodel.

Load and save of the Datamodel

Don't worry, the complicated stuff is already done. Loading and Saving is very easy - when you do without a database

VB.NET
Public Class frmPersonProfession

   Private _DataFile As New FileInfo("..\..\PersonProfessionDts.Xml")

   Public Sub New()
      InitializeComponent()
      PersonProfessionDts.ReadXml(_DataFile.FullName)
   End Sub

   Private Sub Form1_FormClosed(sender As Object, e As FormClosedEventArgs) Handles MyBase.FormClosed
      PersonProfessionDts.WriteXml(_DataFile.FullName)
   End Sub

End Class

That's it: a typed Dataset can directly read and write from Xml-Files - no database is required at all! :wtf:
The shown code above is complete to provide full CRUD (follow the link, if you don't know the term) -Support to the Relational Data-Application:
You can add, change, delete any DataRecord you want:

Image 9

"DatasetOnly" - Data-Application without Database

You see: We have a relational Data-Application, but not a single Sql-Command to call. And although most programmers see typed Dataset as outdated, the pattern, to avoid Sql-Usage in managed Code is absolutely modern. Entity-Framework hides the Sql-Commands as well, and so does every other OR-Mapper.

Unfortunately there is no OR-Mapper, who can read and write a complete Datamodel directly from Disk. For that in the area of easy-to-use, flexibility and portability typed Dataset still has significant advantages over all modern OR-Mappers:

  • no additional Library to include
  • no Database-Provider to install
  • Data-Xml-Files can simply be zipped and shipped. So complete application-sources incl. Data can be shipped within a single zip-file. This can be worthy to discuss problems or prototypes.
  • The DataModel is easy to change with Visual-Studio On-Bord-Tools

For local Data-Applications the approach - i name it: "DatasetOnly" is a serious alternative to embedded Databases. Loading about 10000 or 20000 Records into memory is shurely no problem - nowadays a simple bitmap often takes more memory.
Moreover the option to switch the Data-Backend from an Xml-File to a real Database stays always available. The Dataset does not care about how it is filled - whether via DataAdapter from a Database or directly from an Xml-File.
Nothing of the Bindings or Code must be changed for such migration - the Dataset stays working, only the way it is filled and saved becomes more powerful (and more complicated).

For Beginners: Learn Databinding before Database-Access

One point is to understand: A seriously Data-Application bases on two very different Instances of the same abstract Datamodel: One Instance is modeled in the Database, and the other - and that is the model, our code deals with - is the local, typed model in the Client-Application. The latter is either designed as typed Dataset or as Entity-Model-Classes of the EntityFramework (or of other OR-Mappers).

If you start first with Database-Access you most likely run into hopeless confusion, using DbCommands directly and propably using them wrong.
The main-disadvantage of direct DbCommand-Usage is, they get data even if there is no structurated typed model, where the data is to put in. It's like going to the river to fetch water - and forget the bucket :wtf: .

And if you continue to do so you can never learn the concepts of Databinding at all. Because the Databinding-concepts include the concept, to configure Bindings in Designers - eg in the Form-Designer - but the Designer can only support design Bindings, if a typed Datamodel is present.
So you will never see the capabilities of Databinding, you will never miss it, you will work very very hard, but your applications - from a state-of-art-point-of-view: ähm - rubbish, sorry :-( .

But when you learn to design Databindings, in Designers, you know the value of a typed model, and you will organize the Database-Access in a way that respects that (namely either by DataAdapters or by the OR-Mapper-Stuff).

This article shows the entry to development of Data-Applications via old-fashioned typed Dataset - and that is the most easy approach, since a database is not needed yet.
Later you can switch to Entity-Framework, without loss of the most important principles, you learnd, namly how to design a datamodel and develope with designer-designed Databindings. The principles are same, although some Designers may look different, or - eg with "code-first" - you need not necessarily an Entity-Relationship-Designer (is it an advantage or a disadvantage?).

One important note is, that developing Datamodels is a question of experience. No-one will immediately know the most appropriate model to every given Problem, when he just did understand the principles as explained here.
The way of thinking in entities and relations must be practiced for a while to become good in that.
The skills to create Datamodels is also one of the values, which will not loss, when you later may migrate away from "DatasetOnly".

The Sample-Application

is litteraly the application of the given theory: You will find there the Datamodel, designed in the Dataset-Designer.
And there are two Tabpages, which give a small glimpse on what design of Databinding means:
The first Tabpage simply shows all Tables as they are - in DatagridViews (see image above).
The other Tabpage shows a more complex view: On the left you can select a profession, and on the right are the Persons of that profession displayed.
Moreover via ComboboxColumn you can change the Person, which is refered by the DataRecord.
Below that the same is done once again, but now on the left there are the Persons to select, and you get displayed eaches Professions (and you can add, change, delete them):

Image 10

All that is done in Designers, it works reliable, full Crud-supported, and behaves consistant - without a single line of code.

In my next article i will give a deeper introduction into designing Databindings on WinForms.

Summary

We saw, how the relational Datamodel exceeds a cruicial restriction of the usual hierarchical Datamodel, with which every object-oriented programmer is very familiar: A relational entity can be subordered under several Parent-Entities - without occurance of redundancy.

We've got some definitions of some common technical terms.

We saw, how to design a relational Datamodel by using an Entity-RelationShip-Diagram - in concret: by using the Dataset-Designer.

We got a glimpse of how to use the classes, which the Dataset-Designer generates for us. Means: We saw how to access typed data from our typed Datamodel - eg to output all Persons grouped by their Professions as well as all Professions grouped by the Persons, who executes them.

We got a glimpse of what can be accomplished by designed DataBindings in Gui (but without deeper explanations, how to configure especially the advanced functionality. As said this will be subject of another article.).

We learned, that complete relational Data-Applications can be developed without a database at all - and how easy that is. This may be interesting for Learners as well as for the development of prototypes. And in some cases a simple Xml-File may replace an embedded database.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Germany Germany
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionVery interesting and very useful Pin
danie_lidstrom16-Mar-22 4:37
danie_lidstrom16-Mar-22 4:37 
AnswerRe: Very interesting and very useful Pin
Mr.PoorEnglish7-Apr-22 2:06
Mr.PoorEnglish7-Apr-22 2:06 
PraiseRe: Very interesting and very useful Pin
danie_lidstrom12-Apr-22 1:20
danie_lidstrom12-Apr-22 1:20 
QuestionThanks Pin
neeruroy18-Apr-18 2:01
neeruroy18-Apr-18 2:01 
GeneralMy vote of 5 Pin
cjb11026-Jul-16 20:45
cjb11026-Jul-16 20:45 
GeneralMy vote of 5 Pin
gicalle7529-Sep-15 3:28
professionalgicalle7529-Sep-15 3:28 
QuestionNice Pin
NewPast22-Sep-15 21:31
NewPast22-Sep-15 21:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.