药库和药房部分数据结构，接受大家的检验，请进来讨论

snail506891

少库位

heguiqiu

原帖由 tanyiqiang 于 2009-8-23 20:48 发表
含量单位,计价单位,门诊单位,住院单位,药库单位,他们之间的换算系数.
品种与规格信息没有,做进销存可以,做临床系统不行.

同意！

zrqi

原帖由 花好月不圆 于 2009-8-23 19:30 发表
你是做个玩玩的,还是断架子给客户做啊?

做个玩玩的话，就不费这么大劲了。

有个不大的医院要上一套系统，让我们做。

zrqi

原帖由 sky_chenyu 于 2009-8-23 13:48 发表
你的药品信息就不行的，药品涉及到拆包装的！

有包装的考量了啊，在药品字典里，涉及到了包装单位、销售单位、转换数量。

zrqi

原帖由 tanyiqiang 于 2009-8-23 20:48 发表
含量单位,计价单位,门诊单位,住院单位,药库单位,他们之间的换算系数.
品种与规格信息没有,做进销存可以,做临床系统不行.

是比较小的系统，涉及到：基础信息、药库、药房、门诊挂号、门诊收费、住院管理、院长查询八个模块。

所以不像中联那样大的系统，谢谢谭总。

zrqi

原帖由 snail506891 于 2009-8-23 22:10 发表
少库位

库位？
药库管理不到库位吧？不像物流、仓库管理系统那么大的。

我也做SCM、WSM，有机会的话互相提高。

花好月不圆

原帖由 zrqi 于 2009-8-24 00:42 发表


做个玩玩的话，就不费这么大劲了。

有个不大的医院要上一套系统，让我们做。

这样啊,那劝你还是转手把,找个现成的,大约20-30万的系统,这么折腾你不划算

zrqi

没人说说数据结构本身的看法？

kenvin9

这是我做着玩的对应几个表的结构：

DROP TABLE IF EXISTS `his`.`inventory_item`;
CREATE TABLE  `his`.`inventory_item` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT \'ID\',
  `Code` varchar(20) default NULL COMMENT \'编码\',
  `name` varchar(80) default NULL COMMENT \'名称\',
  `Primary_UOM` int(10) unsigned NOT NULL COMMENT \'主单位\',
  PRIMARY KEY  (`id`),
  KEY `FK_inventory_item_1` (`Primary_UOM`),
  CONSTRAINT `FK_inventory_item_1` FOREIGN KEY (`Primary_UOM`) REFERENCES `code_units_of_measure` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT=\'库存项目\';

DROP TABLE IF EXISTS `his`.`inventory_item_drug`;
CREATE TABLE  `his`.`inventory_item_drug` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `Name` varchar(80) NOT NULL COMMENT \'药品名称\',
  `Category` int(10) unsigned default NULL COMMENT \'药品类别\',
  `Classification_Code` int(10) unsigned default NULL COMMENT \'药品分类编码\',
  `Identification_Code` varchar(30) default NULL COMMENT \'药品标识代码\',
  `Medical_Insurance_Code` varchar(50) default NULL COMMENT \'药品医疗保险代码\',
  `manufacturer` int(10) unsigned default NULL COMMENT \'生产厂家\',
  `Classification_of_Medical_Insurance_Code` int(10) unsigned default NULL COMMENT \'药品医疗保险代码类别\',
  `Drug_Spec` varchar(20) default NULL COMMENT \'药品规格\',
  `Dosage_Form` int(10) unsigned default NULL COMMENT \'药品剂型\',
  `Content` double(8,4) default NULL COMMENT \'药品含量\',
  `Content_Unit` int(10) unsigned default NULL COMMENT \'药品含量单位\',
  `Weight` double(8,4) default NULL COMMENT \'药品重量\',
  `Weight_Unit` int(10) unsigned default NULL COMMENT \'药品重量单位\',
  `Volume` double(8,4) default NULL COMMENT \'药品体积\',
  `Volume_Unit` int(10) unsigned default NULL COMMENT \'药品体积单位\',
  `Concentration` double(8,4) default NULL COMMENT \'药品浓度\',
  `Concentration_Unit` int(10) unsigned default NULL COMMENT \'药品浓度单位\',
  `Packaging_Amount` int(6) default NULL COMMENT \'药品包装量\',
  `Packaging_Unit` varchar(8) default NULL COMMENT \'药品包装单位\',
  `Special_Management_Mark` tinyint(1) unsigned default \'0\' COMMENT \'特殊管理药品\',
  `payment_flag` int(10) unsigned default NULL COMMENT \'药品医保属性\',
  `Hospital_Preparation_Mark` tinyint(1) unsigned default \'0\' COMMENT \'院内制剂\',
  `Producing_Area_Mark` tinyint(1) unsigned default \'0\' COMMENT \'进口药\',
  `Prescription_Drug_Mark` tinyint(1) unsigned default \'0\' COMMENT \'处方药\',
  `Stop_Using_Mark` tinyint(1) unsigned default \'0\' COMMENT \'已停用\',
  `Reagent_Specification` varchar(2) default NULL COMMENT \'试剂规格\',
  `Half_Life` datetime default NULL COMMENT \'半衰期\',
  `Retail_Price` double(10,4) default NULL COMMENT \'药品零售价\',
  `Wholesale_Price` double(10,4) default NULL COMMENT \'药品批发价\',
  `Buy_Price` double(10,4) default NULL COMMENT \'药品购入价\',
  `item` int(10) unsigned default NULL COMMENT \'库存项目\',
  PRIMARY KEY  (`id`),
  KEY `fk_inventory_item_drug_1` (`Category`),
  KEY `fk_inventory_item_drug_3` (`Classification_Code`),
  KEY `fk_inventory_item_drug_8` (`Classification_of_Medical_Insurance_Code`),
  KEY `fk_inventory_item_drug_10` (`Dosage_Form`),
  KEY `fk_inventory_item_drug_13` (`Content_Unit`),
  KEY `fk_inventory_item_drug_15` (`Weight_Unit`),
  KEY `fk_inventory_item_drug_17` (`Volume_Unit`),
  KEY `fk_inventory_item_drug_19` (`Concentration_Unit`),
  KEY `fk_inventory_item_drug_24` (`payment_flag`),
  KEY `fk_inventory_item_drug_26` (`Producing_Area_Mark`),
  KEY `FK_inventory_item_drug_12` (`manufacturer`),
  KEY `FK_inventory_item_drug_2` (`item`),
  CONSTRAINT `fk_inventory_item_drug_1` FOREIGN KEY (`Category`) REFERENCES `code_category` (`id`),
  CONSTRAINT `fk_inventory_item_drug_10` FOREIGN KEY (`Dosage_Form`) REFERENCES `code_dosage_form` (`id`),
  CONSTRAINT `FK_inventory_item_drug_12` FOREIGN KEY (`manufacturer`) REFERENCES `code_manufacturer` (`id`),
  CONSTRAINT `fk_inventory_item_drug_13` FOREIGN KEY (`Content_Unit`) REFERENCES `code_content_unit` (`id`),
  CONSTRAINT `fk_inventory_item_drug_15` FOREIGN KEY (`Weight_Unit`) REFERENCES `code_weight_unit` (`id`),
  CONSTRAINT `fk_inventory_item_drug_17` FOREIGN KEY (`Volume_Unit`) REFERENCES `code_volume_unit` (`id`),
  CONSTRAINT `fk_inventory_item_drug_19` FOREIGN KEY (`Concentration_Unit`) REFERENCES `code_concentration_unit` (`id`),
  CONSTRAINT `FK_inventory_item_drug_2` FOREIGN KEY (`item`) REFERENCES `inventory_item` (`id`),
  CONSTRAINT `fk_inventory_item_drug_24` FOREIGN KEY (`payment_flag`) REFERENCES `code_payment_flag` (`id`),
  CONSTRAINT `fk_inventory_item_drug_3` FOREIGN KEY (`Classification_Code`) REFERENCES `code_classification_code` (`id`),
  CONSTRAINT `fk_inventory_item_drug_8` FOREIGN KEY (`Classification_of_Medical_Insurance_Code`) REFERENCES `code_classification_of_medical_insurance_code` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1514 DEFAULT CHARSET=utf8 COMMENT=\'药品\';

DROP TABLE IF EXISTS `his`.`inventory_item_uom_conversion`;
CREATE TABLE  `his`.`inventory_item_uom_conversion` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT \'ID\',
  `item` int(10) unsigned NOT NULL COMMENT \'库存项目\',
  `from_uom` int(10) unsigned NOT NULL COMMENT \'原单位\',
  `to_uom` int(10) unsigned NOT NULL COMMENT \'目的单位\',
  `conversion_rate` double(20,10) default \'0.0000000000\' COMMENT \'换算比率\',
  PRIMARY KEY  (`id`),
  KEY `FK_inventory_item_uom_conversion_1` (`from_uom`),
  KEY `FK_inventory_item_uom_conversion_2` (`to_uom`),
  KEY `FK_inventory_item_uom_conversion_3` (`item`),
  CONSTRAINT `FK_inventory_item_uom_conversion_1` FOREIGN KEY (`from_uom`) REFERENCES `code_units_of_measure` (`id`),
  CONSTRAINT `FK_inventory_item_uom_conversion_2` FOREIGN KEY (`to_uom`) REFERENCES `code_units_of_measure` (`id`),
  CONSTRAINT `FK_inventory_item_uom_conversion_3` FOREIGN KEY (`item`) REFERENCES `inventory_item` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT=\'库存项目单位换算\';

DROP TABLE IF EXISTS `his`.`inventory_transaction`;
CREATE TABLE  `his`.`inventory_transaction` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT \'ID\',
  `transaction_type` int(10) unsigned NOT NULL COMMENT \'事务类型\',
  `transaction_date` datetime NOT NULL COMMENT \'事务日期\',
  `status` int(10) unsigned NOT NULL COMMENT \'事务状态\',
  `transaction_reference` varchar(40) default NULL COMMENT \'事务参考号\',
  `reason` varchar(50) default NULL COMMENT \'事务原因\',
  `request_department` int(10) unsigned default NULL COMMENT \'请求部门\',
  `request_handler` int(10) unsigned default NULL COMMENT \'请求经手人\',
  `response_department` int(10) unsigned default NULL COMMENT \'响应部门\',
  `response_handler` int(10) unsigned default NULL COMMENT \'响应经手人\',
  `warehouse_handler` int(10) unsigned default NULL COMMENT \'出入库操作人\',
  PRIMARY KEY  (`id`),
  KEY `FK_inventory_transaction_1` (`transaction_type`),
  KEY `FK_inventory_transaction_2` (`status`),
  KEY `FK_inventory_transaction_3` (`request_department`),
  KEY `FK_inventory_transaction_4` (`response_department`),
  CONSTRAINT `FK_inventory_transaction_1` FOREIGN KEY (`transaction_type`) REFERENCES `inventory_transaction_type` (`id`),
  CONSTRAINT `FK_inventory_transaction_2` FOREIGN KEY (`status`) REFERENCES `inventory_transaction_status` (`id`),
  CONSTRAINT `FK_inventory_transaction_3` FOREIGN KEY (`request_department`) REFERENCES `hr_department` (`id`),
  CONSTRAINT `FK_inventory_transaction_4` FOREIGN KEY (`response_department`) REFERENCES `hr_department` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8 COMMENT=\'出入库事务\';

DROP TABLE IF EXISTS `his`.`inventory_transaction_line`;
CREATE TABLE  `his`.`inventory_transaction_line` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT \'ID\',
  `item` int(10) unsigned NOT NULL COMMENT \'项目\',
  `lot` varchar(10) default NULL COMMENT \'批号\',
  `vendor_lot_number` varchar(30) default NULL COMMENT \'供应商批号\',
  `validity` datetime default NULL COMMENT \'有效日期\',
  `transaction_quantity` double(20,10) NOT NULL default \'0.0000000000\' COMMENT \'事务数量\',
  `transaction_uom` int(10) unsigned NOT NULL default \'0\' COMMENT \'事务单位\',
  `conversion_rate` double(20,10) NOT NULL default \'1.0000000000\' COMMENT \'换算比率\',
  `onhand_quantity` double(20,10) NOT NULL default \'0.0000000000\' COMMENT \'库存数量\',
  `available_quantity` double(20,10) NOT NULL default \'0.0000000000\' COMMENT \'可用数量\',
  `price` double(20,10) default \'0.0000000000\' COMMENT \'单位价格\',
  `cost` double(20,10) default \'0.0000000000\' COMMENT \'单位成本\',
  `source_code` int(10) unsigned default NULL COMMENT \'来源\',
  `source_line` int(10) unsigned default NULL COMMENT \'来源行号\',
  `transaction` int(10) unsigned NOT NULL COMMENT \'事务\',
  `inventory` int(10) unsigned NOT NULL COMMENT \'子库\',
  `locator` int(10) unsigned default NULL COMMENT \'货位\',
  PRIMARY KEY  (`id`),
  KEY `FK_inventory_transaction_line_2` (`inventory`),
  KEY `FK_inventory_transaction_line_3` (`locator`),
  KEY `FK_inventory_transaction_line_4` (`transaction`),
  KEY `FK_inventory_transaction_line_6` (`transaction_uom`),
  KEY `FK_inventory_transaction_line_1` USING BTREE (`item`),
  CONSTRAINT `FK_inventory_transaction_line_1` FOREIGN KEY (`item`) REFERENCES `inventory_item` (`id`),
  CONSTRAINT `FK_inventory_transaction_line_2` FOREIGN KEY (`inventory`) REFERENCES `inventory_inventory` (`id`),
  CONSTRAINT `FK_inventory_transaction_line_3` FOREIGN KEY (`locator`) REFERENCES `inventory_locator` (`id`),
  CONSTRAINT `FK_inventory_transaction_line_4` FOREIGN KEY (`transaction`) REFERENCES `inventory_transaction` (`id`),
  CONSTRAINT `FK_inventory_transaction_line_5` FOREIGN KEY (`transaction_uom`) REFERENCES `code_units_of_measure` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=utf8 COMMENT=\'出入库事务明细\';

loveglove

表的数量来说是可以了。但表的字段还需要完善。
1、你的药品信息不是很全，以后查询是使用关联查询方式实现吗？
2、等我想到了再问你。