[RESOLVED] 2 Tier or 3 tier architecture?

[angelica]

Heya,

I have built a windows application that is database driven. I have sometimes used datatables & datasets but when data logic manipulations where required, I switched to Stored Procedures with T-SQL on the server database.

Now I am writing up and describing the software architecture. I am sort of confused if its a 2 or 3 tier application.

Any guidelines please?

[Hack]

It sounds like a three tier to me.

Typically a three tier is comprising on the workstation portion (the front end or GUI), a LAN/WAN on which business logic is located and a database.

Does that fit your application model?

[GaryMazzone]

I thought it sounded like 2 tier. Application level (holding the GUI and the data access objects datasets and datatables) and the Database Level.

I think if the Business logic was housed in a separate DLL (not in the application) would I consider this 3 tier.

[angelica]

Im not much used to the technical terms. What is the business logic referring to exactly?


Well the design is like this:

I will have application installed on several PCs. These will have permission on a LAN/WAN to access the database through permissions on the network.

With regards to the data logic or business logic (not too sure if its the same) I will sometimes use string queries where the SQL involved one value and one table but the rest I sent the paramaters values to the Database on the server and all comparisions and logic is worked through DDL and returned to application by means of output parameters.

Tried to make it a little bit more clear. Any idea what this is exactly and how can it be representin in a diagram (or link to a good one).

I have read about the 3 tiers which involve an application server. What does this involve exactly can anyone pls explain?

Thanks

[GaryMazzone]

This still sounds like 2 tier to me. The application logic is based in the application GUI right? There is no second component that needs to be installed (out side the database server)?

[FunkyDexter]

I'd call it... sort of... erm both.

You said you were using sprocs where data logic manipulations are required - I think that will probably represent your business logic middle tier and would represent a 3 tier approch. Although you also say you use string queries (by which I assume you mean you build a sql query in the client?) where it's simple and that would represent a 2 tier aproach.

Also, I've always understood the 2-tier/3-tier concept to be a logical rather than a physical one. That's the basis on which I'd argue that having your business logic in sprocs is a 3 tier aproach - there's a logical compartmentalisation between your interface (the client program), your business logic (the sprocs) and you information store (the data tables). If you take it as a physical concept then it's definitely a 2 tier aproach because you would regard the sprocs as part of the data tier (because they're physically held as part of the DBMS).

[angelica]

Hi FunkyDexter,

I am of the same opinion and that is why I got confused but I think you have explain it correctly.

thanks

[leinad31]

IMO it's 2-tier with business logic divided into two; one near the data the rest at the client. With sprocs you are tied to the data structure, you can't perform abstraction on it and there's no wrapping of functionality like in inheritance.... etc, etc, etc.

3-tier design is more pronounced (or easier to see) in web applications since it implements a thin client and application servers in the middle... note that since client is thin (e.g. browser), you can't expect it to do heavy processing (mostly focused on presentation of data and data entry validation such as through javascript) so you can't clutter it with mid-tier logic... the front-end is really just a front-end in the true sense or 3-tier design.

The mid tier implements most of the processing & logic, e.g. struts or controller, application/session/page beans, OOP rather than procedural, can connect to more than one data source or you can change DB vendor and it does not matter much to the mid-tier.... etc etc

The back-end is the database and sprocs are included there because they are all related to data retrieval, manipulation, and maintenance. Sprocs are often tied to the data and the database and so can't be considered as truly a separate tier, except in special cases none of which come to mind right now.

[angelica]

Hi Lein,

Do you have any good link which explains this whole process of client database with sprocs manipulation which explains it in a sort of formal way that I can quote in my project documentation (dissertation).
ths

[szlamany]

We use a model here where the UI is thin - no business logic.

Our business logic is 100% in SPROCS.

Database is MS SQL.

This "resembles" 3-tier - in our opinion it surpasses 3-tier.

Our UI uses SPROCS to read "form control and report control" tables in the database so that the UI FORMS actually are built based on those control tables - reports are produced via those control tables.

With this model we can add fields to the "screens" by adding a row to a FORM_T table in the DB. We can "turn on validation for a field" by setting a field in the FORM_T table. That validation flag will in turn make the UI call a SPROC to validate the data on the UI.

Basically our UI is static - installed on 1000's of PC's. We can add or change the UI appearance through changes to the SPROCS and control tables in the DB.

The only reason this is not "true 3-tier" is that the business layer - the sprocs - cannot be moved to a different server. In a true 3-tier setup all 3 tiers can reside on different physical platforms - basically for scaleability.

And of course all of this is can be debated - it's all opinion.

What 3-tier gives you - what you achieve - from a developers point-of-view - is a proper separation of the 3 areas - UI / BL / DL.

Since our sprocs parameters are all named "to match" the UI fields and the DB fields we in a sense have OO-like properties in those parameters.

There is no requirement that all three layers be done using a certain language. Our BL is in T-SQL - we considered that to be superior to a BL in VB. Now that MS has release LINQ - where you can write T-SQL like instructions in VB, it appears we were ahead of the curve. Using T-SQL to manage data in the BL allows you to use superior set-based logic in the BL - as opposed to inferior iterative logic.

And I only say inferior because in order to really utilize a relational database you should be set-based up until the moment of UI-presentation (in my opinion!)

[leinad31]

Add to the confusion the fact that even vendors don't agree on the 3-tier setup There is a Model-View-Controller variation from Oracle.

[angelica]

Great guys, more intrincate than I thought.

So I think I better describe it as a 2 Tier with a sort virtual business logic that acts using T-SQL in SRPOCs.

Is this right pls?

And isd the diagram I am appending be okay since I have added a business log compartment?

[szlamany]

Probably a good idea.

How far you push the fact of a virtual-BL in SPROC's is supported by your consistent use of SPROCS.

If you find you have many queries inline in the UI then your BL is not really in a single place. Of course from your description it appears those might all be display-only type datasets and such...

Which in turn is not really business-logic - as it can be debated - so then you would have a virtual-BL in your SPROCS.

Although since it's in the DB it 2-tier'd (from a hardware perspective - for sure).

[Clanguage]

Rocckford Lhotka has a wonderfull explanation here

http://www.lhotka.net/weblog/Middlet...dRemoting.aspx

[angelica]

I am not much technical in TSQL but I think you mean only select and Inserts when you say:

"you have many queries inline in the UI then your BL is not really in a single place

I think I have more SPROCs then string queries and some of the TSQLs is quite complex at least for me. I contains some IF, CASE, etc. so how do I go about this with regards to 2Tier reasoning

[szlamany]

I gave that a brief read - that's a good article - I'll have to read it further...

[szlamany]

I am not much technical in TSQL but I think you mean only select and Inserts when you say:

"you have many queries inline in the UI then your BL is not really in a single place

I think I have more SPROCs then string queries and some of the TSQLs is quite complex at least for me. I contains some IF, CASE, etc. so how do I go about this with regards to 2Tier reasoning

Read that article - that will help you with your understanding...

[riteshjain1982]

Here is one more very good short Article

[angelica]

Great! Thanks

[mendhak]

Great guys, more intrincate than I thought.

So I think I better describe it as a 2 Tier with a sort virtual business logic that acts using T-SQL in SRPOCs.

Is this right pls?

And isd the diagram I am appending be okay since I have added a business log compartment?

Do you have a separate set of classes that contain methods which in turn perform database calls or do your forms perform the database calls? Because whether you are calling SPs or using inline SQL - if it's a call made from inside your form's code, then it's 2-tier. If you've got a separate class that contains methods, and your form uses this middle class, then it's 3-tier. In your case, I'm pretty convinced that it's 2-tier, so that diagram is wrong.

[leinad31]

Moving code and logic to sprocs isn't just about adhering to an n-tier design. More importantly. It's about centralizing maintenance, and ensuring all algorithms adhere to the business process... imagine 10-30 forms/UIs accessing 15 - 25 tables (including reference and working tables) and other database objects... if there's change to one of them, e.g. additional table or column), or a change in business process, then how are you to ensure that you are able to cascade the logic to all dependencies/forms?

As to whether sprocs is a separate BL or not depends on who you ask, or the level of abstraction said person gives the design. Personally I think that more often than not it is part of the data model (more abstraction, interface to the data), since it is tied to the data, to the database technology, it provides a consistent means of accessing, updating, maintaining in a very efficient manner (tuning) the very specific table design, normalization, data typing, relational model, indexing etc at the database level. You perform tuning and address deadlocks/table locks at one place.

If they are thought of as centralized interfaces to the database then mid-tier can focus more on adhering to the business process in a more abstract sense (which can change, e.g. due to new laws), wrapping or grouping functionality provided by sprocs, provide load balancing, network resources integration and security, reporting, and other network related features/functions since it's not cluttered with several lines of data access code that is repetitive (how you insert to a table in one form would be more or less the same way you'd insert from another form, only with different values).

The model is a guide and not a rule cast in stone. Just try to make a design that will make everyone's job easier, rather than prioritizing adherence to a paper model.

[angelica]

ok guys,

Im not too sure if I get it right. sorry not too technical on this one. Let me explain what I have ;

I have procedures that call SPROC inside each Form. Example here:

Private Sub SubmitSupply()
        Dim query As String = "FullSupplyTransaction"
        Try

            Using cmd As New SqlCommand(query, MyConn)
                With cmd
                    .CommandType = CommandType.StoredProcedure     'fullTransactionSupply

                    .CommandType = CommandType.StoredProcedure
                    .Parameters.AddWithValue("@bookFID", cboBookSu.SelectedValue)
                    .Parameters.AddWithValue("@qty", txtqtySu.Text)
                    .Parameters.AddWithValue("@transactionDate", dtpSupply.Value)
                    .Parameters.AddWithValue("@userFID", userID)
                    .Parameters.AddWithValue("@supplierFID", supplierFID)
                    .Parameters.AddWithValue("@LANo", cboLASupp.SelectedValue)
                    .Parameters.AddWithValue("@Delivery_InvNo", txtDel_InvSu.Text)
                    .Parameters.Add("@Success", SqlDbType.Int, 0, "Success_S")
                    .Parameters("@Success").Direction = ParameterDirection.Output
                    .Parameters.Add("@ReturnErrorText", SqlDbType.NVarChar, (50), "ReturnErrorText")
                    .Parameters("@ReturnErrorText").Direction = ParameterDirection.Output
                    .Parameters.Add("@ReturnText", SqlDbType.NVarChar, (50), "ReturnText")
                    .Parameters("@ReturnText").Direction = ParameterDirection.Output

                    MyConn.Open()
                    .ExecuteNonQuery()

                    If Not CStr(cmd.Parameters("@ReturnErrorText").Value) = String.Empty Then
                        MessageBox.Show(CStr(cmd.Parameters("@ReturnErrorText").Value))
                    End If

                    If Not CStr(cmd.Parameters("@ReturnText").Value) = String.Empty Then
                        MessageBox.Show(CStr(cmd.Parameters("@ReturnText").Value))
                    End If
                    Success_S = cmd.Parameters("@success").Value

                    .Parameters.Clear()

                End With
            End Using

            If Success_S = 1 Then

                MessageBox.Show("Transaction SUPPLY is SUCCESSFULLY EXECUTED.", " Success", MessageBoxButtons.OK, MessageBoxIcon.Information)

                 End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        MyConn.Close()

    End Sub

the SPROC is too long to include here and irrelevant also. It contains A Begin transaction rollback for the Errors and a Commit Transacation at the end, which is being all executed through the database and I assume is being done within the Data Layer.

So with this management how should my arguements be with regards to the Architectural design. According to Menhak the diagram is not correct so how should it be?

Please explain in simple language.

Thankyou.

[kevchadders]

Think I’m with leinad31 and medhak on Angelica’s being 2-tier app., though I can fully see why some would consider the stored procs being classed as another tier, especially in the past few years as new versions of SQL Server come out and givin you more coding options with stored procedures. (e.g. using Try Fetch Catch etc)

For my app, I just class it as an N-Tier Application

It’s a web based, which has the GUI front end with a Business Layer split into 2 parts, one which communicates with the GUI, and the other which communicates with the DB Backend. (See Solution Explorer screenshot)

I also have a number of stored procedures, which are quite robust and perform some business logic/validation. Personally I still class them as part of the Data Layer.

[kevchadders]

Angelica,

I would still stick to it being a 2-tier application, but i dont see why you could mention somthing about the data layer being split into two parts (Tables / Stored Procedures - represented in a hack of your diagram i did)

Personally i would complety remove the Businessa Logic box in your diagram and keep it to 2-tier.

[angelica]

hi Kev,

Short and sweet. I take all you guys advice and say it a 2 -tier and fix my diagram also.


P.S Hi kev. when you mentioned you would drop the Business Logic and then it shows up in your diagram.
Then your diagram incorporates the business Logic with the Data Layer. Is this Ok or am I missing something. Please give me a short confirm.

Thanks

[kevchadders]

Sorry yes, the dotted line around the Data Layer and Business Logic IS the Data Layer.

I should of renamed the Data Layer box to something else maybe. I suppose i was just trying to show how you could say its a 2-tier application, but at then same time showing that the data layer is split in two components. Dotted line representing some business logic being done in there, but not considered seperate from the Data Layer to class it as 3-tier.

[angelica]

So i remove the business logic from the diagram. Then should I show in some way that the logic of the SPROC is managed by the Database because it is important and if so how do I name the square instead of business logic -

data management? Or shall I drop it all together and have just the 2 layers
Presentation and Data Layers

[kevchadders]

Your choice.

Personally, I would drop it all together and just have the two boxes.

If you would like to include the stored procs in some way then you could have more detailed diagrams showing the breakdown of each layer.

e.g In link below is a more detailed 2-teir diagram for an Oracle DB.
http://youngcow.net/doc/oracle10g/ja...ier_config.gif

[angelica]

Ok Kev,

think I will drop it and do a bit of explanation in the text. Thanks

[kevchadders]

Just thought i'd do a quick re-hack of that diagram as a two-tier structure, with 3 layers.

Tiers and Layers are based on the link posted by Clanguage on Rocckford Lhotka description
http://www.lhotka.net/weblog/Middlet...dRemoting.aspx

Layer

A logical grouping of similar functionality within an application. Often layers are separate .NET assemblies, though this is not a requirement.

Tier

A physical grouping of functionality within an application. There is a cross-process or cross-network boundary between tiers, providing physical isolation and separation between them.

ps. Ange, not trying to confuse you as i still prefer the simple two box approach, but i thought i'd do the correct version of what i was trying to say in post #24.

[FunkyDexter]

I think Kev's diagram is a very good representation that covers both the llogical and the physical break down. I would disagree with him on removing the business logic element though (din't ya' jus' know that someone would ). As it stands, the dotted boxes represent the physical architecture while the blue shaded boxes represent the logical architecture. I don't see a good reason not to represent both in your diagram as long as you can do so clearly. In fact, I think the last diagram posted provides an excellent representation.

My only quandry is that you previously said this:-

I will sometimes use string queries where the SQL involved...

which sounds like you might be putting sql together in your client. If that's the case then you probably want to ammend it so that that the business layer spans both tiers (and also the server and client on the left). If all your DB communications is done via sprocs as you've shown in post 22 then I would say the diagram is spot on already.

[angelica]

Hi Kev,

I also liked the article for lhokta.lhokta mentions the Data Access and Data management referrring specifically to stored procs. In fact I had changed the diagram to something below:

Your opinion on whether it is better as the diagram with this post or your last one which I also think is good.

[angelica]

I think Kev's diagram is a very good representation that covers both the llogical and the physical break down. I would disagree with him on removing the business logic element though (din't ya' jus' know that someone would ). As it stands, the dotted boxes represent the physical architecture while the blue shaded boxes represent the logical architecture. I don't see a good reason not to represent both in your diagram as long as you can do so clearly. In fact, I think the last diagram posted provides an excellent representation.

My only quandry is that you previously said this:-

I will sometimes use string queries where the SQL involved...

which sounds like you might be putting sql together in your client. If that's the case then you probably want to ammend it so that that the business layer spans both tiers (and also the server and client on the left). If all your DB communications is done via sprocs as you've shown in post 22 then I would say the diagram is spot on already.

Hi FunkyDExter,

Yep as I said I used both string and SPROCs. And I shall specify it also.
I am thinking that when the string is used the Data Management is not used . I have posted a new diagram if you can give it a look

[leinad31]

Make a separate diagram for the physical/network architecture. Then make a logical or modular diagram that shows the modules/functions or process flow; actually these are more important compared to the tier diagram you've spent several days on. Once you've identified those, then what the overall diagram should look like or what is most appropriate in explaining the design (it might not be an n-tier diagram, rather another modeling diagram) would come more naturally.

Like chicken or egg first dilemma... if your stuck at the chicken then try to work on the egg first.

[kevchadders]

Your changed diagram looks fine ange.

Go with whatever you feel most comfortable with for your project.

[si_the_geek]

I've just moved post #33 here - it was accidentally posted elsewhere (sorry about the delay!).