DataTables and SQL and...god knows what else

WARNING: Long and complicated question...

So here's the story so far:

I'm trying to wean the sound & lighting hire company I work for off their Excel/VBA based quoting/specification system; I'm in the process of developing an SQL Server/C# Winforms solution.

The problem I'm running into is the Specification side of it.

In the old Excel system, this was a sheet in a workbook that was b开发者_如何转开发asically the entire stock list, with editable columns for things like quantity required, discount etc. Most of these were filled with 'default' values that the user could overwrite.

One of the problems was that this stock list grew so big that it needed to be heirarchical. So in my C# solution it'll be a TreeListView ( http://objectlistview.sourceforge.net/cs/index.html ).

Where I'm running into problems is this whole concept of showing the WHOLE stock list (at the moment this is one table in the db), but only storing in the database the relevant rows (another table in the db). To complicate matters, the user will often need to insert rows for subhired items that should ONLY be stored in the database table for the spec, NOT the stock list. So I end up having to display (and let the user edit) a right old mixture of items and values from different tables.

What I want to avoid is excessive network traffic, so storing an entire stock list (with zero values for items that haven't been specced) every time the user saves isn't an option.

Complicating this is that the TreeListView expects to see a single List, and does all the updating etc of said list internally. It can be made to work with a Datatable as well.

At the mo I have the following database tables:

Item_Types - contains all stock items' properties and default values for e.g. cost, discount, weight etc

Spec_Details - contains details that 'override' the default values from Item_Types, plus any rows the user has added in for a given job. Plus (here's another spanner in the works) calculated 'autospec' values (values calculated at runtime that do not exist in Item_Types; so basically the schema for the two tables is different, ruling out a straight merge of the two).

Spec_Heirarchy - Just to complicate matters even further, basically the user will be able to add 'Systems' (e.g. PA systems etc in different locations) into the TreeListView - basically a top-level parent node that will then be populated with the ENTIRE stock list from Item_Types. This table contains the parent/child relationships for every Item_Type on a per-job basis, as obviously some jobs will have a completely different heirarchy to others according to how many systems there are and if the user has added any subhired items.

Default_Heirarchy - As above, but the 'default' spec heirarchy for stock items.

So what's the best way of going about organising and storing and accessing this mess? I've already had to rethink my whole design too many times already so am asking for some guidance here. I'm new to C# and have basically bitten off far more than I can chew, but - you guessed it - there's no budget to get anyone more experienced to take this on.

Thanks

This sounds like a standard Order Entry scenario. Usually, a design would have at least three separate objects, Pricelist, Order and Order Items. The items that you sell to one specific customer one one Order would be in the Order Items table along with their price info.

When you build the order you would select items from the PriceList table. Some items would always have their description and price overridden, such as an Installation Labor item, or a 3rd party parts item.

The Order Items table becomes a snapshot in time of the Pricelist for this one specific Order.

I'd suggest you stop thinking about the user interface.

If you get the database right, it will support just about any user interface you can imagine. The main database at the last Fortune 500 company I worked at had several hundred applications written in at least 25 different languages hitting it. You don't get flexibility like that by designing around today's application language of choice.

Where I'm running into problems is this whole concept of showing the WHOLE stock list (at the moment this is one table in the db), but only storing in the database the relevant rows (another table in the db).

That's a pretty basic requirement. Since you're using SQL Server, someone nearby might have a copy of Microsoft Access. Northwind, the example database, almost certainly has examples of what you need to do. If I remember Access-speak correctly, the whole stock list is the row source of a combo box, and the value the user chooses is bound to a column in another table.

It sounds like you have what most companies have--products, orders, and order line items. Your line items might come from your table of products, or they might come from a subcontractor. Does that sound right to you?

I think I was too vague with the question here...I now have it all sorted out in my head and the DB end of it is working properly - I was mainly concerned with the interface and how to manipulate the data from the DB into a user-friendly form; suffice to say I've rethought my whole approach on that front and know what I'm doing now.

Thanks for the help though.