Are you looking for an effective way to represent your business data? Database views are the perfect solution! A database view is a select statement that is run on tables to fetch meaningful data for some application. With views, you can easily represent meaningful business concepts. But which business concepts should be represented by tables, and which by views?

A SELECT statement is used to retrieve data from the tables, ensuring that only relevant and meaningful information is retrieved for the application. This means that the SELECT statement will not simply select all data from a single table, but rather select only the data that is meaningful to the business. But first we need to figure out which business concepts exist in the first place. The only way to find that out, is to talk to the business to get to know their information needs.

Facts

A great way to communicate with domain experts in the business side of information, is to verbalize facts in natural language and concrete examples.

Employee City of Residence
"Employee 465 lives in New York."
"Employee 987 lives in Boston."

Employee Date of Birth
"Employee 465 is born on 01-01-2000."

Employee Office City
"Employee 465 lives in New York."

Patient City of Residence
"Patient 564432 lives in Washington."
"Patient 587669 lives in New York."

Patient Date of Birth
"Patient 587669 is born on 01-01-2000."

Person Firstname
"Employee 465 is called Mary."
"Employee 987 is called Jack."
"Patient 564432 is called Tom."
"Patient 587669 is called Leonard."

Person Surname
"Employee 465 has family name Smith."
"Employee 987 has family name Jones."
"Patient 564432 has family name Harvey."
"Patient 587669 has family name Reed."

Logical Diagram

Using the facts above, we can derive a set of tables to contain the facts, and provide views to represent Days, Persons, and Cities. As depicted all stated facts can now be stored in the proper table. But how did we decide what to be a table or a view?

The way of working

The information grammar of the facts is chosen deliberately for the purpose of this article. Let's take a look at some of them:

The conceptual model

When the facts are modeled according to the information grammar above, the following model will arise. It contains three distinct area's that are relevant to the business and should result in a database view.

1. Both Employee Date of Birth, and Patient Date of Birth, are considered to be a relevant Day. Since the business is managing calendars, Days are apparently very relevant to them.
2. The City is an Object Type and is used in various Fact Types to communicate City of Residence for both Employee and Patient, and to locate the Employee Office. We do not list any cities that are not in either three fact types.
3. The Person in this case contains facts for Firstname and Surname, and the Person can be either an Employee or a Patient.

Conclusion

With the release 13.1, CaseTalk adds support to use containers (Day) to add semantics to our model. Additionally, CaseTalk adds the support to de-generalize Object Types such as Person. It allows for greater flexibility in modeling facts to be applied to various other Object Types. When transforming the model, the Person Firstname will be applied to both Employee and Patient. Similar with the Person Surname. This adds a great, powerful new feature to model once, and apply in multiple locations in the model.

To get back to the answer of Tables and Views, once can see the fact oriented model serves the communication by being able to model facts. And the mere grammar that underlies these facts will drive the creation of a normalized set of tables. And to make sure the business did not introduce concepts in vain, these are added as views automatically to preserve as much business knowledge as possible.

CaseTalk will take away your concerns and allowing you to focus on meaning, and not technology, or the lack of business meaning in the technological solution.