Background: I'm primarily doing database, data-quality and data modelling development at my department. I'm trying to get people to integrate all their separate data into one data model, with the goal of ensuring a common high-quality data source reflecting the business reality. This to me primarily means logically separating Data-Sets into different tables and joining them via foreign-key relationships.

Problem: Lately I've been increasingly hearing groans when I say that a column needs to be referenced out to another table, and if they want it in their report, the tables will have to be joined in a view. People are also starting to be afraid of the complexity of the data model.

Option1: I can create a View, that gives my users the data they directly want to see. The view joins all tables to get the data as requested.

Option2: Users can see which data are foreign keys, and can on their own, retrieve the linked data. For example, if they have an invoice that is related to a person, they could the click on the person-name and retrieve the e-mail and telephone numbers of that person.

Question: What is the viability of Option2? I prefer Option2, since the data model is constantly developing, new elements are being added, and relationships may be redefined. This requires a constant maintenance of views. On the other hand, I wonder if navigating a relational model is too difficult (difficult as in "too much work", not as in "impossible").

Does anyone have any experience with this?

  • 3
    Can you please describe the needs of your end users? It seems likely that their mental model of the data is not as normalised as yours, whereas yours sounds like it maps very closely to your database schema. – Kit Grose Dec 6 '13 at 11:19
  • Are option1 and option2 your only options? You generally don't want to expose your data model to the users directly...they can break things, it can change etc. Are you able to create a front end for it that minimizes their "fingers in the pie"? NOTE: This is a "better or faster or cheaper; which two would you like?" comment. – FuzzyBunnySlippers Dec 6 '13 at 12:47
  • This webpage has a question, comments and answers that relate to this question, links to questions that are possibly related, etc. If I want to see more about the person who asked the question, that information is freely available on another page because it isn't relevant to the question. Could you imagine how annoying it would be if all of the answers were on a different page? Joins aren't that scary once you're used to them, just a pain in the ass to write out. – cimmanon Dec 6 '13 at 13:42
  • Thanks for the comments - the point I'm trying to get across, is that my users primarily need the integration of their separate views on data into one model - that's why I'm building the database side of things. There is no provision for a complex front end, until the data model has matured. I'm just seeing a bit of stress from the users, that the data model is complicated in their eyes(ofc. it is since it integrates the data from the whole department). So I was wondering weather I should continue trying to promote understanding of the relational model, or if that is a waste of time. – Rafael Emshoff Dec 8 '13 at 23:25
  • @cimmanon "Joins aren't that scary once you're used to them, ..." my question is, weather I should try to get my users to get used to joins, or if that too much to expect from them. The data is more of strategic nature, rather than operational, but is used at least on a monthly basis to run reports and analysis + all the little tasks inbetween that are required to improve data quality. – Rafael Emshoff Dec 8 '13 at 23:29

No, don't expose users to a relational model. The application is there to do a service to your users, not the other way round. This means that the interface should be adapted to how the users think, not that you should try to teach the users to work with the information structure necessary for a well architectured piece of software.

Of course it is more work for you to adapt the application layer to the users' needs than to give them a simple wrapper to the internal structure. But this is how good products are built, inside and outside of information technology. Would you like to drive an early 1900's car, without servos in the steering wheel, without ABS and even without a differential? Or, to give an IT example: I assume that you have used some software which rotates and pans a 3D view. CAD applications and games do it. They usually offer some combination of mouse dragging and keyboard modifiers for this. Now imagine a CAD manufacturer who felt that it was too much work to implement this dragging feature. If you want to see your work object from a different angle, you have to type in the transformation matrix coefficients you want. After all, this is how the software works internally - with a transformation matrix - and that whole mouse dragging stuff is just a convenience for the user. And an engineer who works with a CAD program should be smart enough to know what a transformation matrix is. Would you like working this way?

Well, your users feel the same way about having to work with an information processing tool which is structured according to principles they don't care about, don't need to care about and don't want to care about. Even if they are software engineers themselves and already know how to work with relational models, working directly a relational model is still painfully inefficient for a human to work with. People who are not accustomed to relational models have it even worse. And no, you shouldn't try to teach them how it works, even if they have the mental capacity to learn it. They want to do a job with the tool you give them, and to be able to concentrate on the job itself and not on the tool and its workings. Don't put your application in the way of your users, make it unobtrusive. This is how good products are created, and if you look at the tools around you, you will probably notice that you yourself like the least obtrusive ones the most.

Of course we all have to make compromises when we don't have the resources to deliver perfect products. But you must be aware that giving a relational model to the users, wrapped in a simple CRUD or not, is delivering an inferior product. If they have trouble working with it, it is your fault for giving it low usability, not the users' fault for not learning the arcane art of navigating the relational model. You can deliberately make the choice to do so - there can be good reasons for that - but you should be aware that you are building a flaw into your product.

  • Thanks for this well-balanced answer, it showed me how unaware I am of the goal of usability. Nonetheless, there are currently no resources to go into making an ideal UI. Do you think you could identify a break-line, where the net-effort for teaching the users to be more technical becomes greater than creating a UI that caters to their individual wishes? I'm trying to decide how to assign my limited time for user-interaction. – Rafael Emshoff Dec 9 '13 at 13:03
  • @rafael The general approach is to identify the tasks the user want to carry out using your software, and design the UI such that all the information needed for a single task (or a single step in a larger task) is presented together, so the user doesn't have to constantly switch. Displaying data in an endless grid, such as a database view, is far from ideal from a usability standpoint, but it is a big improvement over having one page per table. Once you have identified the data needed for each task, you will probably find out that you can reuse a view for multiple tasks. – Rumi P. Dec 9 '13 at 14:25
  • that is precisely my issue. The tasks are only partially defined(the organization is still young and developing). Most of the data usage currently comes in form of ad-hoc requests for analysis, for which I create a result set that I send back to the user. For those requests that come in more often I create a view, which is connected to a crude front-end. The users want more control, instead of asking me to gather the data for them, but I can't allow them to break the data-model just because they find it easier to read. Thats why I thought a viable solution could be a relational-db browser – Rafael Emshoff Dec 9 '13 at 15:37
  • 1
    This is a tough situation you are in. But this should not be permanent, so you writing a relational browser is way too much work for a temporary solution. You could offer people to use a read only access through a frontend with a steep learning curve (e.g. Access or Linqpad - I am not aware of alternatives outside of the Windows ecosystem), and they will sort themselves into those who prefer to learn and have all the flexibility, and those who prefer to not learn and live with the delay inherent in you writing the views for them. – Rumi P. Dec 9 '13 at 16:24
  • 1
    This way, the enthusiasts will be happy to get what they want, but will also be aware that this is a rigged solution (you are letting them play with developer tools) and not expect from you to make a nice, convenient way to browse the data (which they will expect if you write a browser especially for them). The ones who don't want to learn will be aware that they are the ones who choose to give up flexibility for the sake of convenience, and still feel in control. You will get grumblers too, but I hope not too many. As the orga matures, you will be able to write permanent convenient solutions – Rumi P. Dec 9 '13 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.