Make sure the Windows Service is loosely coupled to the instance of the DB server. This will allow you to move to a N:1 ratio of Windows Service : DB server. There's a whole host of techniques that can be used to make your DB server more robust, but that's not really what you're getting at in your Q.
Isolate the following information:
- What data is required for the Windows Service to function
- How would the Windows Service act if it didn't have an immediate feed of that information
- How would the Windows Service share that information with other instances of itself
Identify what is dependent upon the Windows Service and why. How would those elements react if a load balancer was inserted between them and the Windows Service? What needs to change in order for those elements to play nicely with a load balancer?
Start analyzing what might happen to existing chats and incoming chat requests should the Windows Service go down. Ideally, all you would lose on an existing chat is the logging information. Incoming chats would be routed to a different Windows Service instance.
Ultimately, the answer to your question is identifying the assumptions and requirements that are binding your layers together. Loosen those requirements / make the layers more independent and you'll be on your way to scaling and distributing your application.
I'm assuming for your mainline path that only the Windows Service interacts directly with the DB server. If that's not true, you'll need to consider if you want to continue with that model or change it.
The standard approach is to mark the product as inactive, discontinued or otherwise not visible. Deleting an entry from the database has an entire cascade of events that can be problematic.
This could be something as simple as a boolean, or a MySQL-esque enum, or its own table to join in (if you want to distinguish the difference between inactive (not yet available and not visible) and discontinued (previously available and not visible).
Consider a schema that is something like:
+---------------+ +----------------+
| Product | | Product_state |
|---------------| |----------------|
| id (PK) | +--+ id (PK) |
| status (FK) +--+ | visibile |
| sku | | desc |
| vendor (FK) | +----------------+
| desc |
| ... |
+---------------+
Make sure you have all the appropriate indexes on the tables (see Bitmap vs B-tree in Oracle and bitmap index at wikipedia). This, combined with the proper view will allow you to simplify the query logic.
Rarely do products exist on their own. As you mention:
The issue I have is that this product could be referenced in a customers basket, to be paid orders, pending orders, deliveries, past orders etc...
Removing the product from the database would either leave dangling references to things that don't exist. Even more 'fun' is if the database was constructed with foreign keys that cascade, that cascade of events becomes even more "fun" with a cascade of deletes that wipes out data, or sets the values to null (depending on how the cascade is set up - see Foreign key at Wikipedia). Note that you can use this approach in some RDBMs to prevent people from messing up the master table by using the restrict option on foreign keys.
You wouldn't just use this for 'in a cart' as a thing, but as you mentioned, paid orders, pending, deliveries, warranties, etc... Restricting the foreign key delete will give you a database error if you try to delete where it shouldn't be and it will be enforced by the database itself, rather than trying to implement checks in code.
Using the foreign key constraints will also make sure that your database maintains referential integrity - preventing people from accidentally linking an order to something that doesn't exist (which is just as bad as deleting). It can also improve performance of reads (see Does Foreign Key improve query performance?)
For oracle, you can read about the constraints on foreign keys: 13.1.17.2 Using Foreign Key Constraints
For mysql, you can read about 14.2.7.6 InnoDB and foreign key constraints
Best Answer
Sounds like you want to use the two phase commit protocol. A lot of hits come back from a google search, so you'll find plenty to research with.
Here's a quick description from the Wikipedia site.
In a "normal execution" of any single distributed transaction, i.e., when no failure occurs, which is typically the most frequent situation, the protocol comprises two phases: The commit-request phase (or voting phase), in which a coordinator process attempts to prepare all the transaction's participating processes (named participants, cohorts, or workers) to take the necessary steps for either committing or aborting the transaction and to vote, either "Yes": commit (if the transaction participant's local portion execution has ended properly), or "No": abort (if a problem has been detected with the local portion), and The commit phase, in which, based on voting of the cohorts, the coordinator decides whether to commit (only if all have voted "Yes") or abort the transaction (otherwise), and notifies the result to all the cohorts. The cohorts then follow with the needed actions (commit or abort) with their local transactional resources (also called recoverable resources; e.g., database data) and their respective portions in the transaction's other output (if applicable).