我需要一些基本的指示 [英] I need some basic instructions, please

问题描述

您好，

对不起，我的问题很长，但我是初学数据库，我需要认真的帮助

我的问题是我的第一个专业人士程序，这是商店管理系统，但我在数据库方面有一些经验

我用（C＃& SQL Server 2014）构建了这个程序

我害怕Windows格式（datagridview）上的数据处理特别是在1或2年后，数据库中会有大量的记录

我看到win Forms以完美的方式处理数据，而数据库包含一些记录只是为了测试，它是完美的搜索使用SQL语句和一切，但我不知道在多年的数据插入后会出现什么情况



我尝试了什么：



我试图在一些表格中遵循数据规范化理论

这是一个简单的解释我的项目，对于有经验的人来说，告诉我我是正确的方式或者是什么：

我有产品表和一张卖单（发票）表，包括一些栏目:(发票ID \客户ID，总价，付款，遗体，日期）

另一个名为（Bill Content）的表这个表包含链接到特定账单ID的产品ID（这个表格很大，假设用户每天会插入300个账单，每个账单包含大约1-5个记录的内容表，这是我所害怕的）并使用（JOIN）检索账单内容，避免数据重复并减少数据大小

这是对的吗？这是数据规范化的概念吗？

但由于某种原因，我要在活动表中打破这个角色，即每个动作都会插入每个动作（存款，取款，卖出操作等）用nvarchar（MAX）单元来描述这个操作（卖或买或者工资）

我可以这样做或重复文本单元格会影响数据库性能吗？特别是在1或2年之后会有大量的记录。

Hello,
I’m sorry that my question is long, but I’m beginner in databases and I need serious help
My problem is that’s my first professional program, it’s shop management system, but I’ve a few experience in databases
I built this program with (C# & SQL Server 2014)
I’m afraid of the data handling on windows form (datagridview) specially after 1 or 2 years there will be a huge number of records in the database
I see win Forms dealing with data in a perfect way while database contains a few records just for testing, it's perfect in searching using sql statements and everything, but i don't know what will be the situation after years of data inserting

What I have tried:

I tried to follow the data normalization theory in some tables
This is a simple explain to my project, for anyone with experience read and tell me I’m on the right way or what:
I’ve table for products, and a table for sell bills(invoices) including some columns: (Invoice ID\ Client ID, total price, payed, remains, Date)
And another table named (Bill Content) this table contains products ID’s that linked to specific bill by bill id (this table will be huge, let’s say user will insert 300 bills per day and every bill contains about 1-5 records in the content table, this is what I’m afraid of) and using (JOIN) to retrieve bill content, to avoid data repeat and reduce data size
Is that’s right? Is that’s the concept of data normalization?
but for some reason I’ve to break this role in the activity table that every action (Deposit, withdrawal, sell operation, etc. ) every action will be inserted with nvarchar(MAX) cell to describe this operation (sell or buy or salary)
can I do that or repeated text cell will affects the database performance? specially after 1 or 2 years there will be a huge number of records.

推荐答案

由于你的问题是通用的，所以只能在通用级别上回答，但很少有点你应该考虑。



首先，你强调Forms作为一种技术，但实际上你用于用户界面的技术并没有影响数据库使用的效率。关键点是你如何连接数据库，你使用哪种技术，如何查询数据库等等。



如你所说，规范化的一个目标是防止重复数据以保持较小的尺寸，但这不是全部。另一方面是保持修改简单。由于数据仅存在于一个地方，如果数据被修改，则只需在一个地方进行修改。这可以在发生更改时节省时间和精力。同样的原则适用于计算数据。你应该尽量避免存储计算数据，而是在获取数据时进行计算。



现在要加快速度，索引是最重要的。为了使数据库高效工作，您需要正确的索引来支持所有操作。请记住，索引不仅会影响选择。它还会影响插入，更新和删除，包括好的和坏的。这就是为什么您需要仔细决定要构建哪些索引以便它们支持WHERE子句的原因。尽量避免过度索引，因为这会减慢修改。



另一个关键是如何在程序中使用数据库。始终只获取所需的数据，不再需要。这意味着您应该过滤SQL中的数据，而不是客户端。



关于设计。尝试优先选择第3范式，这样可以使数据库结构易于理解。也可以使用代理作为主键。这有助于您避免对引用的关键字段进行不必要的更新。



一个重要的是数据类型。请记住为每列使用正确的数据类型。不要将varchar用于日期等。也永远不要使用包含多个数据项的字段，而是将它们拆分为单独的列。



祝你好运：）

As your question is generic it's possible to answer only on generic level, but few points you should consider.

First of all, you emphasize Forms as a technology but actually the technology you use for the user interface is not affecting the efficiency of the database usage.The key point is how you connect to the database, which technology you use, how you query the database and so on.

As you said, one goal for normalization is to prevent repetitive data in order to keep the size smaller, but that's not all. The other aspect is to keep modifications simple. Since the data exists only in one place, if it is modified, it needs to be modified only in one place. This saves time and effort when changes occur. The same principle applies to calculated data. You should try to avoid storing calculated data and instead do the calculations when fetching the data.

Now what comes to speed itself, indexing is the main thing here. In order for the database to work efficiently, you need proper indexing to support all your operations. Remember, indexing does not only affect selecting. It also affects inserts, updates, and deletions, both in good and bad. This is why you need to carefully decide which indexes to build so that they support your WHERE clauses. Try to avoid over-indexing as this slows the modifications.

Another key thing is how you use the database in your program. Always fetch only the data you need, no more. This means that you should filter the data in the SQL, not on the client side.

About the design. Try to prefer 3rd normal form, this keeps the database structure easy to understand. Also do use surrogates as primary keys. This helps you to avoid unnecessary updates on referenced key fields.

One big thing is the data types. Remember to use correct data types for each column. Do not use varchar for dates and so on. Also never use fields that contain multiple data items, instead split them to separate columns.

Good luck :)

这篇关于我需要一些基本的指示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！