dannythomas13

This is my first post so could i first say hi to everyone!

Using Visual Studio 2005 and Sql Server 2005 I was going to make an online ordering asp site which we have done a few of before. But unlike other projects this one needs the ability to work offline if someone comes into the shop or orders over the phone.

So basically, I want to create a windows app which the staff can take orders and place the order information into my db, but on top of this i need to build an asp site which will take online orders.

Now I am new to this so i don't know what the best way to go about it is, whether to have a copy of the db both on the local drive and on the server and then synch them somehow if the comm link ever goes down. Or to have only an offline db which will take offline orders and when an order is placed or products are browsed online, the information is passed from the local offline db.

The problem is though, as well as trying to find the best solution, i also dont know if i should use message passing, remote objects and also hwo these work.

I realise that some tutorial links may now be put up as replies but that is fine as long as i understand why the tutorial mentioned is the best solution for my problem. I knwo this is nothing new though as many companies allow you to order online and the number of products left will have to match up to another db somewhere.

The whole problem stems from the fact that this company cannot stop taking orders simply because this company will take orders from people in person, on the phone and online.

Thanks in advance and I hope i made the problem clear enough.

Danny



Re: Architecture General Online app using offline sql database - advice needed

Martin Platt

Danny,

I think it depends a lot on the number of users who will be accessing this system.

Consideration must be given to concurrency.

A DataSet may help you out, for an easy to get up and running solution.

What does the application need to do, is it going to do anything to work out order delivery, or automatic stock reordering, because being offline would obviously create problems in this instance.

You could also use messages to place orders, that would probably be a more scalable and manageable solution, certainly that would be the case if it is a post only solution, and is only for placing the order, and getting confirmation that the order is received.

I guess if the stock is checked when the application is online, and feedback connected, that's likely to be a good enough solution. If you need a quick response, you connect and submit immediately

Anyway, I'm making a few assumptions already, so your feedback would be appreciated to fully answer your question.

Cheers,

Martin.






Re: Architecture General Online app using offline sql database - advice needed

Jason Fransella

What causes the requirement of offline input Since you say the staff members are in the store, I question why they can't have access to the web application too, or alternatively a windows app which works online (meaning it's connecting to the database, not working with an offline local data store.).

As soon as you start working with offline data you have do deal with changes to the database that occur while you are disconnected. While there are definitely models to deal with this, I'd say that you're adding a lot of complexity to your application if the business doesn't require it. This leads to a higher chance of failure and more cost to maintain the solution.

Even if the store is for some reason unable to stay connected online currently, you should evaluate the cost of getting them a permanent connection against the additional cost of development and maintenance.

Also, if you want to take advantage of the richer capabilities of a smart client application -- a windows app that can work offline -- you might consider creating a common set of services that both the web application and the smart application can call to talk with the data tier. That way you can build iteratively, give the functionality of one application first, and reuse a lot of the logic in the other application.





Re: Architecture General Online app using offline sql database - advice needed

dannythomas13

Thanks for the reply.

The app is for a local hotel. We need to build a system which can allow the staff to input the bookings if people walk in, or book over the phone, hence the need for a system which may never be down. On top of this we need the ability to take online bookings, this is why i thought a database on a local machine within the hotel rather than on our server would be better, in case their net is ever down.

The company is very small and i would be surprised if they ever took more than 5 bookings a day. When the customer chooses dates, he/she needs to be shown only available rooms for that time and as bookings can be made in many ways, this information needs to be current. So all i ever really need to know is the information within my tables, which is past bookings and customer information, room information etc etc.





Re: Architecture General Online app using offline sql database - advice needed

dannythomas13

I will need offline data to ensure that the hotel is never in a situation where they cannot take bookings from people who walk in won't I I thought if everything was hosted on the server that we have rather than within the hotel, then if either of our machines lose internet connection, the system within the hotel, minus online bookings will still work

Is this not right

Thanks

Danny





Re: Architecture General Online app using offline sql database - advice needed

Ollie Riches

I think the problem needs clarifying, it sounds as though you using the terms 'online' and offline' in a manner that will confuse readers, what I believe you mean by 'online' is via the internet - as in the customer can book a room in the hotel using a PC (standard e-commerce) and 'offline' means a more traditional room booking method by like 'over the phone' or face to face', but this is the important thing they are still going to require the ability to enter the booking into the system.

If I am correct in my asumption the 'offline' versions of your booking system are still going to require some kind of user interface into the system, you have to remember the 'online' and 'offline' version are just presentation layers of the same system (database). Now if you are developing any kind of booking (ordering) system you are going to require to develop at least 2 different UI systems even if it is effectively the same application you are going to require a 'user' mode and an 'adminstrator' mode to allow not only for a 'user' to book a room (create an order) but you need to provide an adminstration functionality so they can modify the booking if required (extend the customers stay in the hotel, etc).

IMO I would consider developing a single web based system that can be accessed over the internet, it would provide a standard booking entry system for rooms and detailed administator functionality (room amendments, cancellations, user searches, discounts, rates etc) via restricted access (username\password). This can then be accessed by staff in the hotel who are taking bookings 'over the phone' or 'face to face'.

HTH

Ollie Riches






Re: Architecture General Online app using offline sql database - advice needed

dannythomas13

your assumptions are correct, sorry i wasn't so clear, however if the staff are adding bookings through the administrator UI, through the internet, what happens if their net connection goes down, they would not be able to access any of the data would they not

We have made online apps before which allow bookings online and have admin and user access, I was going to use a windows app for the staff and an asp page for online customer orders, but the whole issue surrounding this is what if the net connection is down, nothing will be available will it not

Thanks

Danny





Re: Architecture General Online app using offline sql database - advice needed

Ollie Riches

you said:

'what happens if their net connection goes down, they would not be able to access any of the data would they not '

In answer to the question I would say:

'use a pen & paper and write down the customer details and explain to them the system is currently down and you will get back to them ASAP when it comes back online'

I would also ask where will the solution be hosted, if not on the client site then how will any system work if their internet connection goes down unless they have a completely seperate system that synchronises with the main system periodically - this approach starts to sound as over-engineering if this is only for a relatively small hotel.

If a 24/7 availability is required then you are going to have to start thinking about two seperate systems that use some kind of replication mechanism to keep the system in a known valid state.

IMO you can build what ever front end you like desktop or web - you need to establish what kind of availability is required before designing a solution IMO.

HTH

Ollie Riches






Re: Architecture General Online app using offline sql database - advice needed

dannythomas13

That was my thoughts exactly, i was simply thinking that an ideal solution would be to have the two databases synchronised whenever the connection is lost and re-established. We will have to build the two front ends to the hosted db now as time is short but i would like to know what the ideal solution is to the 24/7 available system.



Re: Architecture General Online app using offline sql database - advice needed

Jason Fransella

I'd agree with Ollie. The simplest system, and therefore the cheapest and easiest to maintain would be to have one web based system hitting one back end datastore. What you need to do is focus on the right infrastructure to keep the system online as much as possible. Decent network gear, a good internet provider, etc.

Also, it sounds like you're focusing on the online (internet) system as the primary datastore, and the local system as secondary. I would suggest you consider putting the server locally at the hotel. That way if the internet connection goes down the hotel can still function and only the online booking component is unavailable. I would think it's more important to have the booking information available at your fingertips when a customer comes to check in. What really drives this is business requirements. What do the stakeholders find more important.

One other question that's begging to be asked. Why are you building vs. buying There have to be a ton of applications that have already been written to handle booking and reservations. This is hardly a new problem domain. It's almost always cheaper to buy vs. build.





Re: Architecture General Online app using offline sql database - advice needed

dannythomas13

thanks for the reply but we dont want the network traffic all go to their server and things like google rankings will be lower as they have less bandwidth than our server etc.

if anyone can all i need to know is either the best way to synch up the two db's whether sql anywhere ( i think its called ) or something can do the job and if not, the best way for the asp.net site to access the data local to the hotel through remoting/message passing etc. As stated though this cant be the first time this has been done so there must be a really nice solution to this.

thanks for all the replies guys

Danny





Re: Architecture General Online app using offline sql database - advice needed

Jason Fransella

If all you are trying to synchronize between two sql instances, I'd look at the built in functionality of SQL Server.

If you're simply trying to publish information from one server to another, you can look at Replication. The technology is a mature technology that has been around a long time.

You might also look at the newer SQL technology, Mirroring.

Also, you could consider SQL Server 2005 Service Broker. It provides asynchronous message queuing and reliable messaging. Both of these features are very useful when you're talking about communicating across an unreliable connection. It also provides built in encryption, which is useful across public networks. Also, you don't have to consume these services with sql on the other end. Your application can handle them directly.

Depending on your needs you might even look at Integration Services.

These are all SQL Server specific technologies of which you can take advantage. You could also move further forward in your architecture, and front end everything with service interfaces on the outside of SQL Server using a technology like WPF. Again, you'd probably want to make this setup asynchronous and use a reliable messaging system.

With the little information that you can provide in a forum type posting scenario, I don't think it's reasonable to tell you there is a "best way" you can do this. There are a lot of requirements around business needs that need to be understood, as well as the amount of resources they're willing to commit to, and the amount of time they are willing to wait for a viable solution.

Also, when I said that this isn't a new problem domain and must have been tackled many times before, I wasn't just referring to working in a disconnected state from a data store, I was specifically talking about software for handling reservations for the hospitality industry. I did a quick search for "reservation software" and came up with a number of reasonable solutions. I saw solutions that included an online booking module for under $1000 dollars. There appear to be literally dozens of open source reservation systems out there. Probably the most important rule I've learned in this industry over time is that you only build when you can't find an available solution to fill your requirements. Even then, it's often easier to modify an existing solution than build one from scratch. know in the past I've made the mistake of building something because I wanted to build it, not because it needed to be built.

Given that, there are viable reasons to build, and I won't harp on the subject any longer





Re: Architecture General Online app using offline sql database - advice needed

Udi Dahan The Software Simplist

If you really need to go to an occasionally connected systems architecture, then take a look at this post where I describe a simple and effective solution: Occasionally Connected Systems Architecture [http://udidahan.weblogs.us/2007/04/04/occasionally-connected-systems-architecture/].

That said, I believe that there are easier solutions to the problem you mentioned. If the system you are building is for only one hotel - and will continue to stay that way, then host the DB in the hotel. Have your website work against that database for orders. Keep all the information you need for users to just browse on a different database hosted with your website. If the hotel's connection fails, your web site will be unable to accept orders - so give the users the phone number to the hotel for making reservations.

The advantages that this solution brings is that the orders database is always consistent - regardless of failed connections. This simplifies concurrency issues so that you don't have to worry about selling the same room twice and what to do to compensate for that. It also means that the hotel can always take orders and see the availability of their rooms. Also, the load of browsing information is kept on your servers, which appears to be more significant than taking the 5 orders a day.

Also, I would suggest developing on a single UI technology, in your case ASP.NET so that you can reuse pieces between the admin and regular user UI. Host the admin UI on the same box as the DB in the hotel.

Finally, I would suggest staying away from selling specific rooms and model the domain in terms of "pools" of rooms with the same characteristics - plain rooms, rooms with a view, suites, suites with a view, etc. Then, just sell out of those pools. Leave the hotel the ability to choose which rooms to actually put the visitors in; there may have been a serious problem in room 207 requiring the carpets to be replaced so if room 208 is available and has the same characteristics, who cares if you sold them room 207 Check out the Reservation pattern which explains these issues.

Hope that helps.






Re: Architecture General Online app using offline sql database - advice needed

Jason Fransella

Udi Dahan The Software Simplist wrote:

That said, I believe that there are easier solutions to the problem you mentioned. If the system you are building is for only one hotel - and will continue to stay that way, then host the DB in the hotel. Have your website work against that database for orders. Keep all the information you need for users to just browse on a different database hosted with your website. If the hotel's connection fails, your web site will be unable to accept orders - so give the users the phone number to the hotel for making reservations.

This was the model I was getting at. Keep your data close to where it's most critical. The hotel staff HAS to be able to look at the reservations. You can have your web application gracefully handle those times when you lose connection to your data store by providing the web customer with a phone number to call to make a reservation. If you don't have to deal with concurrency between your systems, your solution will be a lot less complex, and therefore a lot cheaper to write and maintain.

Also, I'd suggest that if you do go down the road of the Occasionally Connected Systems Architecture, put it off until a later iteration of development. It sounds like you're building this application from scratch, so I would focus on the core requirements first, and get a working system up as soon as possible. This provides your customer, the Hotel, with value sooner. It also allows them to review the solution sooner and request the changes they need.

It is a common practice to put off software architecture decisions until "the last responsible moment". To quote Jeff Atwood, "Early in a project, you should make as few binding decisions as you can get away with", and "Early decisions often result in work that has to be thrown away". For instance, you might find that some of the core components in the reservation system need to be refactored. If you've already set up your occasionally connected system, you may have to refactor it too, adding time and effort to the project without adding any value.





Re: Architecture General Online app using offline sql database - advice needed

dannythomas13

I have now come back to this problem and now that I have a little more knowledge I can appreciate that all of the replies were actually very correct...so thanks.

Now that I have looked into it more I can see that the information HAS to be accessible locally/client side at all times. Now if someone can tell me whether this is wrong or not would be great but I shall proceed anyway....

I host the availabilities, allocations and rates, i.e. the db on the hotel's iis, and then if they lose a net connection they can still access the data. Then my asp.net site will access this information through web services, datasets returned from the hotel's iis. Should this connection, i.e. the internet go down then the hotel can access the data and the web site can offer a phone number.

Is all of this correct, that the local machines within the hotel can access this information hosted on their server even with no net connection I do feel a little silly for asking but I just wanted to make 100% sure.

Thanks in advance

Danny