帮助创建用于帐户管理的表格 [英] help in creating tables for account managment

问题描述

我必须创建一个用于简单帐户管理的数据库，其中保留了供应商交易记录..
我对帐户领域一无所知...所以我被卡在制作用于管理记录的表格中..
我需要保留供应商详细信息的记录，例如(vendor_id，vendor_name，vendor_address，vendor_phone，vendor_email等
)
再次，我需要存储有关供应商带到商店的物品的信息....以及付款状态....要给供应商的剩余总金额是多少...以及付款收据.

但是我只是不知道怎么做桌子..


我尝试制作2张桌子

1. tbl_vendor
属性
一个. vendor_id
b. vendor_name
C. vendor_address
d. vendor_phone
e. vendor_email

2.tbl_vendorAccount
属性
a.vendor_id(外键tbl_vendor)
b.item_id
c.item_cost
d.vendor_paymentStatus

谁能帮助我....对不起，如果我不能正确解释...

i have to create a database for a simple account managment in which a record of the vendor transation is kept..
i have no knowledge in account field...so i am stuck making the tables for managing the records..
i need to keep the record of the the vendor details like( vendor_id,vendor_name,vendor_address,vendor_phone, vendor_email,etc
)
again i need to store the infromation about which item the vendor brought in to the shop....and what is the payment status....how much is the total amount remaining to give to the vendor...and transation of the payment for the repciept..

but i just can''t figure how to make the tables..


i tried with making 2 tables

1. tbl_vendor
attributes
a. vendor_id
b. vendor_name
c. vendor_address
d. vendor_phone
e. vendor_email

2.tbl_vendorAccount
attributes
a.vendor_id (foreign key tbl_vendor)
b.item_id
c.item_cost
d.vendor_paymentStatus

can anyone help me....sorry if i can''t explain it properly...

推荐答案

首先，我建议您寻找一个在着手编写自己的开源，现成解决方案之前.我认为有很多免费系统，没有必要重新发明轮子.

但是，如果您想这样做，希望对您有所帮助...

通常，在这种类型的解决方案中，您可能希望将数据分成尽可能小的块，并创建代表封装(或独立)数据的表.然后，使用事务表以所需的方式映射数据.

例如，供应商表将仅包含有关供应商帐户的信息.一个VendorID，将在其他地方使用，包括供应商名称，地址，付款条件等.

第二个表将是您的物料主数据，并且仅包含有关供应商提供的物料的信息.同样，一个ItemID将与物品名称，描述，售价等一起在任何地方使用.此表将没有付款信息，库存或其他任何信息.记录应该是通用的，并且要说明单个项目的属性.

然后将建立一个交易表，将两者联系起来.它将具有一个TransactionID以及一个购买ID和一个ItemID的条目.它还应包含该特定商品的数量和价格.

您还需要一个采购表，该采购表将来自该采购表.它还具有诸如订单日期/时间，供应商，下订单的人等信息.

当卖方下订单或填写订单时，您将生成一个采购ID，然后为该采购中的每个ItemID添加一行到交易表中，以将采购ID设置为适当的值.最后，如果您从供应商那里收到20个项目，那么您在采购表中将有1行与供应商相关，在交易表中将有20行将这些项目链接到采购(并扩展为小贩).通过查询这些表(使用一些JOIN)，您可以查看特定供应商的所有订单，特定项目的所有订单，或特定供应商或项目所花费的总金额.

另一个表仅用于付款，并且包含付款ID，供应商ID和美元值.如果您想将其追溯到交易中，则还应包含purchaseID，以便您可以将付款与订单进行核对.查询总计交易并以给定供应商的付款总额为准的查询将向您显示未清余额.

诀窍是确保将所有逻辑部分包装到它们自己的表中，然后与ID号链接.您希望避免出现以下情况:开始在系统中重复数据，或者必须返回并编辑记录以使其保持最新状态.在上方的第二张表格中，您包含了付款状态列.这是不明智的，因为您将不得不不断编辑和更新该记录.如果您遇到系统需要扩展且可能需要多个人进行更新的情况，那么事情可能会开始迅速崩溃.最好始终将数据作为独立的实体插入，这样您才有所有记录，并且一些简单的LINQ语句的SUM查询可以实时生成所需的结果.

您可能会选择在某个时候保留总计，但将其放在另一个表中，并确保您正在根据某个条件触发或调用的存储过程来计算总计.这将有助于提高海量数据库的性能，但是您正在迁移到批处理类型的记帐系统中，最好在此时购买真实的系统.

First of all, I would advise you to look for an Open-Source, off-the-shelf solution before you dive into writing your own. There are lots of free systems out there and there is no need, in my opinion, to re-invent the wheel.

However, if you want to do this I hope this helps...

Typically, in this type of a solution, you would want to break you data into the smallest blocks possible and create tables that would represent encapsulated (or independent) data. Then, use transaction tables to map the data in the manner you need to see it.

For example, the vendor table would contain only information about the vendor account. A VendorID which will be used everywhere else, the vendor name, address, payment terms, etc.

A second table would be your item master and would contain only information about the items the vendors provide. Again, an ItemID that would be used everywhere along with an item name, description, sell price, etc... This table would NOT have payment info, inventory, or anything else. Records should be generic and speak to the properties of a single item.

A transaction table would then be setup which would link the two. It would have a TransactionID along with an entry for a purchase ID and ONE ItemID. It should also contain the quantity and the price for that specific item.

You would also need a purchase table where the purchase ID would come from. It would also have information such as the order date/time, the vendor, who placed the order, etc.

When an order is placed or filled by the vendor, you would generate a purchase ID then add a row for each ItemID in that purchase into the transaction table setting the purchase ID to the proper value. At the end of this, if you received 20 items from the vendor, you would have 1 row in the purchase table that relates back to the vendor and 20 rows in the transaction table which link the items to the purchase (and by extension, the vendor). By querying these tables, (using some JOIN''s) you can see all orders for a specific vendor, all orders for a specific item, or the total dollar spent with a specific vendor or item.

Another table would be only for payments and would contain a payment ID, vendor ID, and a dollar value. If you wanted to track it back to transactions, you would also include the purchaseID so you can reconcile the payments against the orders. A query that totals the transactions and subjects the sum of the payments for a given vendor will show you your outstanding balance.

The trick is to make sure that you are wrapping all the logical parts into their own tables then linking with the ID numbers. You want to avoid a situation where you start repeating data in the system or have to go back and edit records to keep them up to date. In your second table up above you included a column for payment status. This is not wise because you would have to continually edit and update that record. If you got into a situation where the system needed to scale and multiple people could be updating that, things can start to break real fast. It is better to always insert the data as independent entities then you have a record of everything and some simple SUM queries of LINQ statements can generate the results you need in real time.

You may choose, at some point, to keep running totals but put them in a different table and make sure you are calculating the totals from a stored procedure triggered or called based on some criteria. This would help with performance on a massive database but you are moving into a batch-type accounting system and you would be better advised to buy a real system at that point.

这篇关于帮助创建用于帐户管理的表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！