Database Principles Overview

In this chapter we will present a very brief and "unscientific" overview of what Databases, Tables, and Records are. This is a vast subject that entire books are dedicated to, and we can only hope to "scratch the surface" – enough for you to understand some basic principles behind Alventis' information handling.

To start with at least some sense of perspective, let's take a look at a Table first. Here's an example of some information about people:

The above list could be something jotted-down on a piece of paper, but there's clearly a pattern here that makes it more than just "a list". The pattern is that we keep seeing the same "bits" of information about each person listed: first name, last name, and what looks like a telephone number. This stands in stark contrast to another possible list on the other side of the same piece of paper:

What's listed above is also "a list" and it certainly contains some information. The problem with this one is that we can't make out a pattern: every line is different, seems to "talk" about different things, and so is quite disjoint from its neighbors.

The first list is a good candidate to be represented by a Table. The second list, alas, is not.

Back to the first list then, let's define what we see in a bit more detail. We see 3 lines with repeating patterns of data: first name, last name, telephone number. We can even write this on top of the list and format it nicely like this:

First name	Last name	Telephone number
Jack	Thompson	612-555-8478
John	Barclay	614-555-8114
Helen	Bolton	231-555-1421

Guess what, we have just created a Table! Well, to be a "real" Table in the pure Database sense, we'd need to put all of the above into some data file, but let's not worry about such details for the time being. The above organization of information essentially constitutes a Table, that's all that matters. It's not surprising, by the way, that the above also just happens to be a very different kind of table, one inserted in some surrounding text by a word processor, what we refer to as Memo Table in the rest of this manual. This is because the organization of data in these two kinds of tables tends to follow the same principles:

And so, it's time we called at least some of these "things" we've been talking about by their proper "official" names:

Incidentally, some people even use the words Column in place of Field and Row in place of Record.

Using our newly-acquired lexicon, we can then state, for example, that what's written in a particular cell in the Memo Table that appears in the text above, is a Field Value for the Record that cell belongs to. We can also say that the collection of all Fields of a Table constitutes its Structure.

As we have seen above, each Field is dedicated to storing a particular kind of information about the object. One Field might store first names. Another might be dedicated to recording appointment dates. Yet another – prices. Thus, each Field has a preference for particular kinds of data: text strings, dates, numbers, and so on. We could probably store all these pieces of data in textual form – much as we do on paper, but there's a more convenient way: Field Type. The Field that stores first names would be of the String Type. A Date Type Field would be suitable for appointment dates. A Currency Field – for prices. It may not be immediately obvious to you why such "restrictions" on each Field are useful. We'll give you some examples without going into them any further. Having specific Types for Fields ensures that a Field dedicated to storing prices can only contain prices. Otherwise, a price list could end up looking like this: $4.95, $3.25, "cheap", $2.05, "expensive", etc. Enforcing Types makes it easier to sort Records by values of these Fields. It makes it possible to perform some useful operations on them, such as calculating their total, maximum, minimum, and other aggregate values. Almost all Database Systems, Alventis included, offer you a broad range of Field Types, such as String, Integer, Currency, Date, Time, and so on.

Structures of Tables can vary wildly. One Table will contain the Fields we have been listing above: first name, last name, telephone number. Another might have a completely different set: film title, year, director, rating, category. There are virtually no limits to their variations. Each Table is suitable for enumerating a particular type of objects and is structured accordingly. Alventis does not limit the freedom of each Table to have whatever Fields it wants. On the other hand, Alventis does introduce a little bit of order in this chaos. This is accomplished by requiring every Table to have a few "standard" Fields, that we also refer to as System Fields. Everything else is up to the Table's designer (quite possibly you). This dichotomy allows Alventis to always know at least something about each and every Table it deals with, while still permitting the rest of the Table's Structure to vary as necessary.

ID	Auto-Incrementing Integer	This is an integer number that uniquely identifies each Record in a Table. No two Records will ever have the same ID value. This number is automatically assigned to each Record at the moment when the Record is created, and it simply keeps incrementing for each new Record. We intentionally avoid the subject of indexes (indices for purists, but the most widely used spelling is the incorrect one, so we'll stick to that) but for "data-aware readers" among you: the ID is the primary index or key of each Table.
CreationDate	Date/Time	This Field holds the date and time the Record was created. This "timestamp" is recorded with millisecond precision. It could be useful to know when a certain Record first came into existence. It may be even more useful to be able to sort Records by this Field thus being able to list Records in their order of "maturity".
LastModificationDate	Date/Time	Another Field of the same Type. This one records the moment of the last time the Record was saved. Note that Alventis uses the timestamp of the local computer on which it is running, not that of the remote Server (in cases of Remote Databases). It is therefore important to ensure that all computers running Alventis have properly-set clocks.
Author	Integer	This Field records the ID of the user who created the Record. Yes, users are also listed in some Table and they too have their unique ID values. It may be useful to simply know who created a certain Record (to thank him/her, obviously). Luckily, under the most common circumstances, Alventis will display the Username of the user in place of his ID. We won't discuss the details here, but Alventis may also use the Author value for other purposes, such as giving access to a particular Record only to its Author. You can learn all about it in the chapter on Alventis Security, if you're fearless enough.
Subject	String	This is a general-purpose String Field that you can use in whatever way you find convenient.
Category	String	Another general-purpose Field – similar to Subject in all respects.

You can record whatever textual information you want in these last two Fields. They default to a maximum Length of 50 characters, but they may have other Lengths in some Tables. If you have Alventis Designer, you can set their Length to whatever you want in the range of 1 to 512 characters. Only String Fields have the Length property which specifies how much text the Field can hold. Each Record will allocate the same amount of space for a String Field regardless of how much text each Record actually ends up containing. Setting the Length to a value that is too high will therefore waste space and lead to a larger Table file on your disk. This is why it makes sense to set String Field's Length to some reasonable value based on what the Field is supposed to hold. If it's a first name Field, these are not likely to exceed, say, 24 characters, so a value in this ballpark would make sense. But we digress. There's one special thing about the Subject and Category Fields that differentiates them from any other String Field you may create or encounter: these two appear in the Search Results grid. As a matter of fact, all System Fields appear there because they are common to all Alventis data Tables. Such being the case, you may want to use the Subject and Category Fields for some of the most recognizable textual information in whatever Table Structures you might create. While it is true that these two fields will always be identified as "Subject" and "Category" in the Search Results grid, you can give them whatever Captions you want on the InfoView forms based on them. For example, a Table of information about cars would likely need such Fields as make and model. Assuming for the moment that these are String Fields, it could be convenient to store the make in the Subject Field and the model name in Category. This way, the Search Results will display the make and model directly in the grid.

Relational Databases. Imagine you have two Tables. The first table is called Countries and – by pure coincidence – holds Records of Countries of the world. Its Structure and some data may look like this:

CountryID	Country	Capital	Population
1	France	Paris	60,000,000
2	Italy	Rome	58,000,000
3	Spain	Madrid	40,000,000

This Table may have many more interesting Fields and will most certainly have many more Records for other countries, but the above is sufficient for our little example.

The second Table is called Contacts and it lists Records of people. We've already encountered this Table in earlier examples, but we will now want to record what country each person is from, so it will look as follows:

PersonID	FName	LName	FromCountry
1	Edith	Piaf	France
2	Antonio	Banderas	Spain
3	Federico	Fellini	Italy
4	Jean	Rochefort	France
5	Luchino	Visconti	Italy

As you can see, each Record contains information about a person, including the country he/she is from. Given the fact that we have not one but two Tables, we could, for example take a person's Record, find out from it that person's country, and then go to the other Table and examine that country in more detail. In a certain sense, we have established a relationship between these two Tables. Conceptually, this is exactly how Relational Databases work. One table lists one kind of objects plus a certain value that uniquely identifies some Record from another table. In the above example, this value was the name of the country.

The above two Tables could actually form a correct Relational Database exactly as we have presented them. The presented setup does have a drawback though. Every time we have to enter a new person into the Contacts Table, we have to type his country into the FromCountry String Field. The above was not too bad, but what if you have a long list of Swiss people to enter? The other problem stems from the previous one: what if you misspell a country? Switzerland is a long word, you know. And you can only hope you don't know anybody from Liechtenstein, Tajikistan, and Guinea-Bissau. From the purely spelling perspective, obviously. If you were to misspell one, it would no longer be found in the Countries Table, so the Relational link would be broken. Sure, there are ways to ensure you don't misspell anything, and even let you simply pick an existing country and record its correct name for you, but there's an even simpler way: not record the country name at all. Instead of recording the country name, we can record something else that would let us uniquely identify the country in the Countries Table. Luckily, that Table has just what we're looking for: a unique integer number called CountryID. Our new improved version of the Contacts Table then becomes:

PersonID	FName	LName	FromCountry
1	Edith	Piaf	1
2	Antonio	Banderas	3
3	Federico	Fellini	2
4	Jean	Rochefort	1
5	Luchino	Visconti	2

Note that we can no longer say in all honesty that the last Field holds the "country": "1" is not a country. What we can say is that it points to a Record in the Countries Table, and it is that Record that does in fact represent the country. Alventis refers to such Fields as Pointer Type Fields. Internally, they are just integers of course, but Alventis treats them as a special Field Type. Since such Fields are responsible for establishing and maintaining a relationship between two Tables, they certainly deserve some special treatment.

The last version of the Contacts Table certainly has one advantage over its predecessor: you can't misspell a number, so the CountryPtr Field is more reliable. Incidentally, it is also faster and easier to store for the Table. The bad news is that it's just a number, and you may not remember which one to enter for Zimbabwe. The good news is: you don't have to remember anything. Alventis will let you simply select the country you want from a combo box. You may never even find out what the corresponding Country ID value is. Let's take a closer look at that combo box now.

Lookup Combo Box. Its name is its function. It lets you look-up the country you wish in the combo box's dropdown list. When the dropdown is closed, the combo box still quietly performs one very important task: it takes the CountryPtr value recorded in the current Contacts Record, finds the corresponding country in the Countries Table, and displays the country's name.

When we activate/open the dropdown, it lists all countries available in the Countries Table. The main purpose of this is for us to be able to pick the country we want and assign it (well, its ID value) to the current Record's CountryPtr Field. We can do so by either double-clicking the country in the dropdown list or hitting Enter when it is focused.

The above is the most essential "Relational" functionality of the Lookup Combo Box, but it can do a few more useful things. For starters, the dropdown list is not just any list but a whole grid displaying as much (or as little) of the Countries Table as the creator of the InfoView thought necessary. It can theoretically display all Fields from the Countries Table – right in the little dropdown (which is, by the way, resizable). You may be excited to know that this dropdown grid allows editing, so you can even edit the Countries Table directly from it. It may not be the most convenient way, but it can be useful if, say, you notice a small misspelling and want to quickly fix it. If you'd rather edit one of the countries from the dropdown in the convenience of the Countries own InfoView, it's easy to get there. Alt-double-clicking a Record from the dropdown grid will open that Record in its own InfoView. Hitting Alt-Enter will do the same for the focused Record. When the dropdown grid is closed, you can either use one of the above methods to open the Record currently displayed by the combo box, or simply click the little right-pointing arrow at the right of the combo box.

One last aspect of the Lookup Combo Box you should be aware of is that you may encounter more than one level of them. Using the above example, what if the Countries Table also had a Pointer Field, say, pointing to the Continents Table? If this field appeared in the CountryPtr Lookup Combo Box, each country's Record in the dropdown grid would have a similar ContinentPtr Lookup Combo Box right in the grid, so you could drop-down that one too, and so on. There's no limit to how many relational "levels" Tables can have, and Alventis allows all these levels to be implemented in as many cascading Lookup Combo Boxes as necessary. Even if we admit that just 3 levels may get intimidating.

An InfoView form could present the relationship between the Contacts and Countries Tables a little differently. The Lookup "point of view" described above sees Contacts as the "main" Table that accesses the Countries Tables as necessary. This is perhaps the most frequently seen view of their relationship, but not the only possible one. The other possible "point of view" looks at the Countries Tables as the "main" Table. When looking at a Countries Record, we could ask the question: what are the Contacts Records that point to this country? The chapter on Relational Databases Design explains what you must do in Designer to create an InfoView that answers this question. Here, we'll leap straight to the conclusion: the InfoView will look essentially like a Countries Table's InfoView with the addition of a grid of Records from the Contacts Table. As we explore the Countries Table, this "Reverse Lookup" grid will always display just the Contacts Records that point to the current country. It should be noted that the core relationship between the Contacts and Countries Tables has not changed a bit: it's only our "point of view" on that relationship that experienced a kind of "reversal".

Referential Integrity. Remember our neat little Contacts Table that points to Countries? What do you think would happen to all the Contacts Records from France if we deleted this country from the Countries Table? This would break the Referential Integrity of the link between the Contacts and Countries Tables. Some Contacts Records would suddenly start pointing to no-longer-existing Countries Records. Not good at all. There do exist methods of circumventing this problem by various means. Alventis takes a simple approach: it does not allow you to delete a Record pointed to by some other Record of some relationally-linked Table. Instead, Alventis will inform you of all such Records it finds. If you really absolutely want to delete something (France in the above example), it is your responsibility to "find a new home" for all the French, i.e., re-point each Contacts Record currently pointing to France to some other country (or delete them all – since you are already clearly on the path of destruction). Once this is done, you can delete the Record you want since there are no longer any other Records pointing to it.

In closing, here are some assorted notes on some relevant issues, mostly of interest to readers with some Database background.

We have intentionally not used the master-detail terms since we don't find them particularly intuitive. Same goes for one-to-many and many-to-one relationships.

We haven't said a word about many-to-many relationships and for a simple reason: they don't exist. Period. They are always the result of a premature impression that fails to see that the many-to-many link is actually 2 one-to-many links, with some connecting table in-between, which has simply been overlooked.

The ID field of each table is of course the primary key, and what Alventis refers to as Pointer fields are foreign keys.

An InfoView may only "implement" the relationship between two tables in either the "Lookup" or the "Reverse Lookup" fashion, but not both at the same time, which wouldn't make any sense anyway.