How do I bridge the gap between my database design and the user interface design?

Question

I know how that question looks, but I'm quite serious. I am trying to create an application that will help me in learning databases (sql, queries, proper database design, etc). I'm using a pet project at work so I have something to focus on with actual requirements. The original project was written ("Frankensteined together", as the original author said) via MS Access. I'd like to learn how to do it better via SQLite, but don't know how to recreate the other functionality Access provided.

Using this site as a way to interact with programmers and developers (I don't work with any), I've thus far read all of Database Design for Mere Mortals as recommended in this question. So, I've got a nice little database design that I plan to implement using SQLite.

I also checked out how to design a user interface for the app via using Balsamiq's Mockups, and submitted some ideas to my potential user base (my peers on my team) to have them give feedback.

            Database --> <insert code here> --> User Interface

However, the part that falls down for me is how to I bridge the gap between the two designs? I realize that's obviously where coding comes in, but to date I haven't made anything with a GUI. Searching around, I didn't seem to find anything as definitive to assist me (a book, a website, even a process to follow) in trying to actually write the app.

I know Perl to some degree, but have only used it for command-line apps; I could use the Win32::GUI module, but I don't really understand the differences between GUI programming and command-line programming, other than to just know that they are different.

Is there a model or a guide to follow regarding GUI development? Are there specific resources for tying an application to a database?

Answer 1

The general pattern that is followed nowadays is:

Database -> DAL -> BLL -> Controller -> View Model -> UI

Where

DAL == Data Access Layer (aka ORM, Object-Relational mapper)
BLL == Business Logic Layer

Googling each of these terms should give you a fairly good idea of where to get started. Note that you don't always need every layer. The BLL and View Model, for example, can be optional if the app is small enough.

See also Model View Controller (MVC) for web development, and Model View Presenter (MVP) or Model View ViewModel (MVVM) for desktop development.

Although the NerdDinner tutorial is Microsoft/Web specific, it contains all of these concepts in one place.

Answer 2

Middleware is another term that you may see thrown around for what you are describing.

The database itself can be a combination of a few different points:

1. Stored procedures - This would be used instead of directly accessing tables and provides a layer of abstraction.
2. Tables or views - Directly accessing column names that can be useful if you are building a lightweight app.
3. Combination of the two. Some tables could be directly accessed while other database activity is done through stored procedures.

The UI can be just a presentation layer or can have a couple of other layers tied to it as one could use a combination of ASP.Net's layers including C#, HTML, and JavaScript for creating web applications.