2022年SQL数据结构 .pdf

数据结构一、基本信息数据结构1、商品基本资料数据结构barcodePKbarcode_idI1p_idI1barcodeproductsPK,I5product_idI5,I4class_idI3,I7parent_idI7,I6child_numberchild_countserial_numberI1namealiasstandardmodalpermitcodetrademarkmakeareaunit1_idunit2_idunit3_idunit4_idrate2rate3rate4validmonthvaliddaycommentdeletedI2pinyinfirstcheckvalidcheckotcflaggspflagmedtypecostmethodEngNameChemNameLatinNamemedtypePKmt_idmt_codemt_namedeletedunitPKunit_idnamedeletedpricePKprice_idI1p_idI1u_idretailpricerecpriceprice1price2price3price4gppriceglpricespecialpriceunittypelowprice说明：Products 商品信息表Barcode 一品多码表Unit 商品单位表Price 价格表MedType 剂型表名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 18 页 - - - - - - - - - 2、往来单位基本信息clientsPKclient_idI4class_idparent_idchild_numberchild_countI1serial_numberI2namealiasregion_idphone_numberaddresszipcodecontact_personaltax_numberacount_numbercredit_totalI3pinyinpricemodesklimitartotalartotal_iniaptotalaptotal_inipre_artotalpre_artotal_inipre_aptotalpre_aptotal_inifirstcheckcommentcsflagdeletedProtocolDateRegionPKregion_idI1serial_numbernamecommentdeleted说明：Clients 往来单位信息库Region 片区信息3、仓库基本信息名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 18 页 - - - - - - - - - storagesPKstorage_idI3class_idparent_idchild_numberchild_countnamealiasI1serial_numberCommentflagI2PinYindeletedlocationPKloc_ids_idloc_codeloc_nameloc_commentdeleted说明：Storages 仓库信息库Location 货位信息库4、职员信息库employeesPKemp_idI2class_idparent_idchild_numberchild_countnamealiasserial_numberdep_idphoneaddresscommentaplimitarlimitaptotalartotaldiscountlimitlsthI1pinyindeletedLowPriceHighPriceSNCountGSPSNCountdepartmentPKdepartmentIdserial_numbernamecommentdeleted说明：Employees 职员信息库Department 部门信息库5、科目信息库名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 18 页 - - - - - - - - - accountPKaccount_idI1class_idparent_idchild_numberchild_countnamealiasI2serial_numberCommentcur_totalini_totalsysflagsysrowdeletedI3PinYintotal_01total_02total_03total_04total_05total_06total_07total_08total_09total_10total_11total_12bqtotalsumtotalcashauditdirectionAccountCommentPKCommIDAccountCommentCode说明：Account 科目信息库AccountComment 科目备注信息库6、会员卡信息名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 18 页 - - - - - - - - - cardtypePKCTypeIdTypeNameDiscountCommentAutoDiscountcardtotalPKIDcardidtotalcarddiscountPKcd_idAutoIDbegintotalendtotaldiscountcommentmembercardPKCardIDI1CardNoNameTelAddressCommentCardTypeDiscountBulidDateValidityDateLoseStopUseBuyTotalBuyTotalPerDayAutoDiscountIDCardIntegralBrithdayDiscountType说明：Cardtype 会员卡类型表Membercard 会员卡表CardDiscount 会员卡折扣类型CardTotal 会员卡消费金额7、门店信息表ShopPosIDPosCodePosNameS_IDC_IDRemotePathCommentdeleted二、单据类数据结构1、销售类单据名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 18 页 - - - - - - - - - billdraftidxPKbillidbilldatebillnumberbilltypea_idc_ide_idsout_idsin_idauditmaninputmanysmoneyssmoneyquantitytaxrateperiodbillstatesorder_iddepartment_idposidregion_idauditdateskdatejsyejsflagnotesummaryinvoicetranscountlasttranstimesalemanagebilldrfPKsmb_idbill_idp_idbatchnoquantitycostpricesalepricediscountdiscountpricetotalmoneytaxpricetaxtotaltaxmoneyretailpriceretailtotalmakedatevaliddatequalitystatusprice_idss_idsd_idlocation_idsupplier_idcommissionflagcommentunitidtaxrateorder_idtotaliotagbillidxPK,I1billidI1billdatebillnumberI1billtypea_idI1c_idI1e_idsout_idsin_idauditmaninputmanysmoneyssmoneyquantitytaxrateperiodbillstatesorder_iddepartment_idposidregion_idauditdateskdatejsyejsflagnotesummaryinvoicetranscountlasttranstimesalemanagebillPKsmb_idbill_idp_idbatchnoquantitycostpricesalepricediscountdiscountpricetotalmoneytaxpricetaxtotaltaxmoneyretailpriceretailtotalmakedatevaliddatequalitystatusprice_idss_idsd_idlocation_idsupplier_idcommissionflagcommentunitidtaxrateorder_idtotaliotag说明：Billdraftidx 单据草稿主表SaleManagebilldrf 销售单据草稿表Billidex 单据主表SaleManageBilldrf 销售单据库2、采购类单据数据结构名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 18 页 - - - - - - - - - billdraftidxPKbillidbilldatebillnumberbilltypea_idc_ide_idsout_idsin_idauditmaninputmanysmoneyssmoneyquantitytaxrateperiodbillstatesorder_iddepartment_idposidregion_idauditdateskdatejsyejsflagnotesummaryinvoicetranscountlasttranstimebuymanagebilldrfPKsmb_idbill_idp_idbatchnoquantitycostpricebuypricediscountdiscountpricetotalmoneytaxpricetaxtotaltaxmoneyretailpriceretailtotalmakedatevaliddatequalitystatusprice_idss_idsd_idlocation_idsupplier_idcommissionflagcommentunitidtaxrateorder_idtotaliotagbillidxPK,I1billidI1billdatebillnumberI1billtypea_idI1c_idI1e_idsout_idsin_idauditmaninputmanysmoneyssmoneyquantitytaxrateperiodbillstatesorder_iddepartment_idposidregion_idauditdateskdatejsyejsflagnotesummaryinvoicetranscountlasttranstimebuymanagebillPKsmb_idbill_idp_idbatchnoquantitycostpricebuypricediscountdiscountpricetotalmoneytaxpricetaxtotaltaxmoneyretailpriceretailtotalmakedatevaliddatequalitystatusprice_idss_idsd_idlocation_idsupplier_idcommissionflagcommentunitidtaxrateorder_idtotaliotag说明：Billdraftidx 单据草稿主表BuyManagebilldrf 采购单据草稿表Billidex 单据主表BuyManageBilldrf 采购单据表3、库存类单据数据结构名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 18 页 - - - - - - - - - billidxPK,I1billidI1billdatebillnumberI1billtypea_idI1c_idI1e_idsout_idsin_idauditmaninputmanysmoneyssmoneyquantitytaxrateperiodbillstatesorder_iddepartment_idposidregion_idauditdateskdatejsyejsflagnotesummaryinvoicetranscountlasttranstimebilldraftidxPKbillidbilldatebillnumberbilltypea_idc_ide_idsout_idsin_idauditmaninputmanysmoneyssmoneyquantitytaxrateperiodbillstatesorder_iddepartment_idposidregion_idauditdateskdatejsyejsflagnotesummaryinvoicetranscountlasttranstimestoremanagebilldrfPKsmb_idbill_idp_idbatchnoquantitypricetotalmoneycostpricecosttotalretailpriceretailmoneymakedatevaliddatequalitystatusprice_idss_idsd_idlocation_idsupplier_idcommissionflagcommentunitidlocation_id2iotagtotalstoremanagebillPKsmb_idbill_idp_idbatchnoquantitypricetotalmoneycostpricecosttotalretailpriceretailmoneymakedatevaliddatequalitystatusprice_idss_idsd_idlocation_idsupplier_idcommissionflagcommentunitidlocation_id2iotagtotal说明：Billdraftidx 单据草稿主表StoreManagebilldrf 库存单据草稿表Billidex 单据主表storeManageBilldrf 库存单据表4、库存盘点单名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 18 页 - - - - - - - - - billdraftidxPKbillidbilldatebillnumberbilltypea_idc_ide_idsout_idsin_idauditmaninputmanysmoneyssmoneyquantitytaxrateperiodbillstatesorder_iddepartment_idposidregion_idauditdateskdatejsyejsflagnotesummaryinvoicetranscountlasttranstimebillidxPK,I1billidI1billdatebillnumberI1billtypea_idI1c_idI1e_idsout_idsin_idauditmaninputmanysmoneyssmoneyquantitytaxrateperiodbillstatesorder_iddepartment_idposidregion_idauditdateskdatejsyejsflagnotesummaryinvoicetranscountlasttranstimeGoodsCheckBillPKsmb_idbill_idp_idbatchnoquantitycostpricebuypricediscountdiscountpricetotalmoneytaxpricetaxtotaltaxmoneyretailpriceretailtotalmakedatevaliddatequalitystatusprice_idss_idsd_idlocation_idsupplier_idcommissionflagcommentunitidtaxrateorder_idtotalGoodsCheckBillDrfPKsmb_idbill_idp_idbatchnoquantitycostpricebuypricediscountdiscountpricetotalmoneytaxpricetaxtotaltaxmoneyretailpriceretailtotalmakedatevaliddatequalitystatusprice_idss_idsd_idlocation_idsupplier_idcommissionflagcommentunitidtaxrateorder_idtotal说明：Billdraftidx 单据草稿主表GoodsCheckbilldrf 库存盘点草稿表Billidex 单据主表GoodsCheckBill 库存盘点表5、零售类单据结构名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 18 页 - - - - - - - - - retailbillidxPK,I1billidI1billdatebillnumberI1billtypea_idI1c_idI1e_idsout_idsin_idauditmaninputmanysmoneyssmoneyquantitytaxrateperiodbillstatesorder_iddepartment_idposidregion_idauditdateskdatejsyejsflagnotesummaryinvoicetranscountRetailBillPKsmb_idbill_idp_idbatchnoquantitycostpricesalepricediscountdiscountpricetotalmoneytaxpricetaxtotaltaxmoneyretailpriceretailtotalmakedatevaliddatequalitystatusprice_idss_idsd_idlocation_idsupplier_idcommissionflagcommentunitidtaxratetotaljkdetailPKjkidjkdatejke_idjkimputmanjkmoneycomment说明：Retailbillidx 零售单主表retailBill 零售明细表Jkdetail 交款明细6、订单表名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 18 页 - - - - - - - - - orderidxPK,I1billidI1billdatebillnumberI1billtypea_idI1c_idI1e_idsout_idsin_idauditmaninputmanysmoneyssmoneyquantitytaxrateperiodbillstatesorder_iddepartment_idposidregion_idauditdateskdatejsyejsflagnotesummaryinvoiceOrderBillPKsmb_idbill_idp_idbatchnoquantitycostpricesalepricediscountdiscountpricetotalmoneytaxpricetaxtotaltaxmoneyretailpriceretailtotalmakedatevaliddatequalitystatusprice_idss_idsd_idlocation_idsupplier_idcommissionflagcommentunitidtaxrateComeDateComeQtytotal说明：Orderidx 订单主表orderBill 订单明细表7、财务表名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 18 页 - - - - - - - - - jsbdetailPKjsidc_idskd_bidxsd_bidbilltypejstotalremaintotalflagjspdetailPKjsidc_idskd_bidxsd_bidbilltypequantityjstotaldetail_idyj_quantityflagfinancebillPKsmb_idbill_ida_idc_idjftotaldftotalcomment1comment2comment3financebilldrfPKsmb_idbill_ida_idc_idjftotaldftotalcomment1comment2comment3说明：Finacebilldrf 财务单据草稿表Finacebill 财务类单据表jsbDetail 按单结算表Jspdetail 按行结算表三、明细帐名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 18 页 - - - - - - - - - accountdetailPKsmb_idI1billidI1a_idI1c_idjdmoneyjdflagproductdetailPKpd_idI2,I1billidI2,I1p_idI2,I1s_idI2,I1quantitypriceI1totalcostpriceI2,I1costtotaltaxpriceI1taxtotalbatchnomakedatevaliddatecommissionflagsupplier_idlocation_idI2storetypeprice_idorder_idunitidsmb_id说明：Productdetail 库存明细帐表AccountDetail 科目明细帐表四、库存余量1、库存余量storehousePKstorehouse_idlocation_idI3,I1s_idI3,I2,I1p_idsupplier_idI3,I2,I1quantitycostpriceI2,I1costtotalbatchnomakedateinstoretimevaliddatecommissionflagstopsaleflaginorderyhdatestorehouseiniPKstorehouseini_idlocation_idI3,I1s_idI3,I2,I1p_idsupplier_idI3,I2,I1quantitycostpriceI2,I1costtotalbatchnomakedateinstoretimevaliddatecommissionflagstopsaleflaginorder说明：StoreHouse 库存余量表Storehouseini 期初库存余量表名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 18 页 - - - - - - - - - 2、代销库存余量storedxPKstoredx_idlocation_idc_idp_idsupplier_idquantitycostpricecosttotalbatchnomakedateinstoretimevaliddatecommissionflagstopsaleflaginorderstoredxiniPKstoredxini_idlocation_idc_idp_idsupplier_idquantitycostpricecosttotalbatchnomakedateinstoretimevaliddatecommissionflagstopsaleflaginorder说明：StoreDX 代销库存余量表Storedxini 期初代销库存余量表五、GSP类表gspauditPKts_idGSP_AuditLimite_idenameGspFlowControlPKFlowIDSourceGspTypeDestGspTypeCommentSysFlaggspPropertGSPSNStylePKIDBillTypeStyleSNCountGspTablePKGspIDGspTypeBillCodeBillEBillDateGspTextcommentCheckStatebatchnoSignGSPVerGspVchTypePKGspidGspTypeNameFormStyleCommentBillToGspIDBillTypeGSPType说明：GSPSNStyle GSP编号GspAudit GSP签字表GSPVchType GSP单据类型表名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 14 页，共 18 页 - - - - - - - - - BillToGsp 单据至 GSP流程定义GSPTable GSP报表库表六、其它1、跟踪类表buypricehisPKc_idPKp_idPKunit_idBuyPricesalepricehisPKc_idPKp_idPKunit_idSalePriceLocationTracePKlt_idU1p_idU1s_idU1l_idrectime说明：Locationtrace 货位跟踪SalePriceHis 售价跟踪BuyPriceHis 进价跟踪2、库存上下限StoreLimitPKSLIDI1P_idI1S_idUpperLimitLowLimitAdjQty说明StoreLimit 库存上下限表3、门店物价管理表PosPricePKprice_idpos_idp_idu_idretailpricerecpriceprice1price2price3price4gppriceglpricespecialpriceunittypelowprice4、权限类表名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 15 页，共 18 页 - - - - - - - - - EmpMsgLimitIDEIDBillTypeMSGLIMITTagusersPKuser_ide_idloginpasspermissionuserauthorizePKut_ide_idtypepsc_idparentflagReplicationAuthorizeIDGUIDMemoTag说明ReplicationAuthorize 复制授权表empMsglimit 职员消息权限users 登录用户权限表UserAuthorize 用户授权限表5、用友接口dwdzdw_idc_idserialnumbertypekmdzkm_ida_idserialnumbertypepzoutbilldatepzlbpznumberpzfjpzcommentkmcodejfmoneydfmoneygysnumberccode说明dwdz 单位对照表kmdz 科目对照表pzOut 凭证导出表6、消息与日志SystemLogPKIDPKEIDActionTagVchIDActionTypeComputerActionDateCommentMsgHistoryIDComputerSenderEIDActionStrTextReceiveDate说明systemlog 日志表MsgHistory 消息历史记录表7、系统配置名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 16 页，共 18 页 - - - - - - - - - sysconfigPKsys_idI1sysnamesysvaluecomment说明：值备注OpenAccount 1 启用账套标志CostMethod 3 默认成本核算法AccountPeriod 1 会计期TaxRate 0 默认税率BillCount 80 默认单据行数DBVersion 2003-07-32 版本号RecpriceAsCost 0 使用最近进价为成本价=0 false =1 true NegativeStock 0 负库存开关=0 false =1 true UseSameCostMethod 1 使用统一成本核算法 =0 false =1 true UseBuyTrace 0 使用进价跟踪UseSaleTrace 1 使用售价跟踪BillSNLex1 1 单据编号规则 1 BillSNLex2 0 单据编号规则 2 BillSNLex3 0 单据编号规则 3 BillSNCount 单据编号记数GSPSNLex1 GSP单据编号规则 1 GSPSNLex2 GSP单据编号规则 2 GSPSNLex3 GSP单据编号规则 3 GSPSNCount GSP单据编号记数CompanyName 公司信息GspFlowControl GSP流程控制BillFlowControl 单据流程控制ShowLastBuyPrice 显示最近进价ShowArAp 显示往来单位应收应付UseClientCredit 客户信用额度UseEmpCredit 职员信用额度AutoComment 自动备注FLOATFMT 浮点数长度TAXPRICEFMT 税后单价小数点保留位数TOTALFMT 合计小数点保留位数SLFMT 数量小数点保留位数DJFMT 单价小数点保留位数XPMenu 是否使用 XP 风格BillDialog 单据是否采用对话框名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 17 页，共 18 页 - - - - - - - - - AutoSaveTime 自动存盘时间LoginDate 最后登录日期Posid 门店 ID PosName 门店名称PosCid 门店对应单位PosSid 门店对应仓库SQLVersion 数据库版本ProductType GUID 帐套 ID AutoMessage 自动短消息CountQty PrintSave 单据打印后保存BillSNLex4 单据编号规则Jkdetail 营业款历史记录名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 18 页，共 18 页 - - - - - - - - -