DataTables 和 SQL 以及……天知道还有什么
警告：又长又复杂的问题......
所以这是到目前为止的故事：
我正在尝试戒掉声音和声音。我工作的照明租赁公司基于 Excel/VBA 的报价/规格系统；我正在开发 SQL Server/C# Winforms 解决方案。
我遇到的问题是它的规范方面。
在旧的 Excel 系统中，这是工作簿中的一个工作表，基本上是整个库存列表，其中包含所需数量、折扣等可编辑的列。其中大多数都填充了用户可以覆盖的“默认”值。
问题之一是这个股票清单变得如此之大，以至于需要分层。所以在我的 C# 解决方案中，它将是一个 TreeListView ( http://objectlistview.sourceforge.net/cs /index.html ）。
我遇到问题的地方是显示整个库存列表（目前这是数据库中的一个表）的整个概念，但仅在数据库中存储相关行（数据库中的另一个表）。让事情变得复杂的是，用户通常需要插入子租用商品的行，这些行只能存储在规格的数据库表中，而不是库存列表中。因此，我最终不得不显示（并让用户编辑）来自不同表的项目和值的正确旧组合。
我想避免的是过多的网络流量，因此每次用户保存时都存储整个库存列表（未指定的项目的值为零）不是一种选择。
使事情变得复杂的是 TreeListView 期望看到单个列表，并在内部执行所述列表的所有更新等。它也可以与数据表一起使用。
目前，我有以下数据库表：
Item_Types - 包含所有库存商品的属性和默认值，例如成本、折扣、重量等
Spec_Details - 包含“覆盖”Item_Types 中默认值的详细信息，以及用户添加的任何行从事某项工作。另外（这是另一个正在开发中的扳手）计算的“autospec”值（在运行时计算的值在 Item_Types 中不存在；因此基本上两个表的模式是不同的，排除了两个表的直接合并）。
Spec_Heirarchy - 只是让事情变得更加复杂，基本上用户将能够将“系统”（例如不同位置的 PA 系统等）添加到 TreeListView 中 - 基本上是一个顶级父节点，然后将填充整个股票列表来自 Item_Types。该表包含每个作业的每个 Item_Type 的父/子关系，因为显然，根据系统的数量以及用户是否添加了任何子雇用的项目，某些作业将具有与其他作业完全不同的层次结构。
Default_Heirarchy - 如上所述，但库存物品的“默认”规格层次结构。
那么组织、存储和访问这些混乱的最佳方法是什么？我已经不得不多次重新思考我的整个设计，所以我在这里寻求一些指导。我是 C# 的新手，基本上已经吃不消了，但是 - 你猜对了 - 没有预算让任何更有经验的人来承担这个任务。
谢谢
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 basically 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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这听起来像是一个标准的订单输入场景。通常，设计至少具有三个独立的对象：价格表、订单和订单项目。您通过一个订单向一位特定客户销售的商品及其价格信息将显示在“订单商品”表中。
当您建立订单时，您可以从 PriceList 表中选择商品。有些物品的描述和价格始终会被覆盖，例如安装人工物品或第 3 方零件物品。
订单项目表成为该特定订单的价目表的及时快照。
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.
我建议您停止考虑用户界面。
如果数据库正确，它将支持您能想象到的几乎任何用户界面。我工作的上一家财富 500 强公司的主数据库中有数百个用至少 25 种不同语言编写的应用程序。通过围绕当今选择的应用程序语言进行设计，您无法获得这样的灵活性。
这是一个非常基本的要求。由于您使用的是 SQL Server，因此附近的人可能拥有 Microsoft Access 的副本。 Northwind（示例数据库）几乎肯定有您需要执行的操作的示例。如果我没记错的话，整个股票列表是组合框的行源，用户选择的值绑定到另一个表中的列。
听起来您拥有大多数公司拥有的东西——产品、订单和订单行项目。您的订单项可能来自您的产品表，也可能来自分包商。这对你来说合适吗？
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.
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.