有一个结构问题，我不知道如何准确解决。
我正在做一个库存系统 - 应该支持多个商店，每个商店都有自己的商品数据库，前100个商店有50.000个商品号。
itemnumber数据库是全局的（所有商店都是相同的itemnumbers） - 但由于我需要跟踪每个商店的库存数量，我假设我需要为每个商店创建一个本地副本，那就是很多表和很多冗余数据。
但令我头疼的是，对于每个库存（他们从整个库存计算完整的商店） - 我需要添加新的库存数量pr项目编号（和一些结果字段，更正库存与原始库存等） - 这是应该添加的东西每次他们选择完成补货/重新计票。
假设itemdatabase是：
itemno，description，现在总库存
然后对于新的库存，当它完成时我需要将这3个值添加到记录中 - 如果他们选择做ex。 200个库存计数我需要在每个项目中添加200个这样的集合：
库存积分，总计数，差异（总计数 - 总计数）
因为我必须能够为每个新的重新进货/重新计数（库存会话）执行此操作，并将每个结果保存为事件历史记录，所以我不能（我认为）在SQL中将动态字段添加到当前表结构中。
如果我有某种链表，我可以让它在我的脑海里工作 - 其中itemrecord上的链表代表了库存会话的字段集 - 所以不是将50.000条记录的完整副本复制到另一个表中对于每个库存会话，我将能够通过包含2的链表扩展表。
另一个选择是某些关系的东西，我想有几个表，但这变得复杂，我担心它需要的所有查找。
是否有可能以正确的方式在SQL数据库中以某种方式使用链表结构或此类问题？ 或者你会建议我使用另一个数据库而不是SQL？
Have a structual question i dont know how to solve exactly.
Im doing an inventory system - where there should be support for multiple stores, each with their own item database, ex 100 stores with 50.000 itemnumbers.
The itemnumber database is global ( same itemnumbers for all stores ) - but since i need to keep track on stock count in each store, i assume i would need to create a local copy per shop, thats alot of tables and alot of redundant data.
But my headache is that for each inventory ( they count the complete shop from scrath ) - i need to add the new stockcount pr itemnumber ( and some result fields, corrected stock vs original stock etc. ) - and this is something that should be added each time they choose to do a complete restock/recount.
lets say the itemdatabase is :
itemno, description, total stock now
Then for a new inventory when its completed i need to add these 3 values to the record - if they chose to do ex. 200 inventory counts i would need 200 of these sets to be added per itemnumber :
inventorysession,total count, difference (total count - total count)
Since i have to be able to do this for each new restock/recount (inventory session), and keep the results for each as a history of events, i cannot ( i think ) in SQL add dynamic fields to the current table structure.
I could get it to work in my mind if i had some kind of linked list - where the linked list on the itemrecord would represent the sets of fields pr inventory-sessions - so instead of making a complete copy of the 50.000 records into another table for each inventory session, i would be able to extend the table throuh a linked list which would contain the 2.
Another option would be some kind of relational thing with several tables i guess, but that gets complicated and i worry about all the lookup's it would require.
Is it possible in a correct way to somehow use a linked list structure or this kinda of problem in a SQL database ? or would you advice me to use another database instead of SQL ?
最满意答案
您可以考虑以下中央数据库架构：
stores
id
code
name
address
...
items
id
code
name
description
barcode
retail_price
...
stocks
store_id
item_id
stock_count
inventories
id
inventory_date
store_id
inventory_items
inventory_id
item_id
original_stock_count
corrected_stock_count
因此，股票表将引用持有股票的商店和商品参考。 请注意，项目的详细信息位于项目表中，该项目独立于商店。
但是在库存表中，您会有一个项目多次出现，每次都有不同的商店。
库存表具有每个库存会话的记录，每个商店是唯一的。 这并不妨碍同时对所有商店进行库存，这会产生尽可能多的记录，但也只能为一个商店做一个灵活性。
然后inventory_items表添加库存结果：每个项目记录计数。
You could think of the following central database schema:
stores
id
code
name
address
...
items
id
code
name
description
barcode
retail_price
...
stocks
store_id
item_id
stock_count
inventories
id
inventory_date
store_id
inventory_items
inventory_id
item_id
original_stock_count
corrected_stock_count
So the stocks table would have references to the store where the stock is held and the item reference. Note that the details of the item are in the items table, which is independent from a store.
But in the stocks table you would have an item occurring multiple times, each time for a different store.
The inventories table has a record per inventory session, which is unique per store. This does not prevent from inventories to be taken for all stores at the same time, which would result in as many records, but leaves the flexibility to do one for one store only as well.
Then the inventory_items table adds the results of the inventory: per item the counts are logged.
更多推荐
库存,inventory,SQL,电脑培训,计算机培训,IT培训"/> <meta name="description&
发布评论