一个进销存数据库设计的例子.doc

学习资料收集于网络，仅供参考一个进销存数据库设计的例子分类：SQL SERVER2009-11-26 15:476004人阅读评论(3)收藏举报数据库nulltableuser电话datec-sharpview plaincopy1. CREATETABLEuser(2. User_Idvarchar(6),3. User_Pwdvarchar(8)NULL,4. Again_Pwdvarchar(8)NULL,5. Bel_Groupvarchar(3)NULL,6. Div_Typevarchar(1)NULL,7. User_Authvarchar(1)NULL,8. Auth_Typevarchar(1)NULL,9. User_Statusvarchar(1)NULL,10. Create_Uservarchar(6)NULL,11. Create_Datevarchar(7)NULL,12. Create_Timevarchar(6)NULL,13. Appr_Uservarchar(6)NULL,14. Appr_Datevarchar(7)NULL,15. Appr_Timevarchar(6)NULL,16. Pwd_Datevarchar(7)NULL,17. Err_CountfloatNULL,18. Use_eJCICvarchar(1)NULL19. )20. CREATETABLESupplier/*供应商表*/21. (22. Supplier_IDintIDENTITY(1,1)NOTNULL,/*供应商编号,主键*/23. Namevarchar(250)NOTNULL,/*供应商名称*/24. Addressvarchar(250)NOTNULL,/*地址*/25. Phonevarchar(25)NULL,/*电话*/26. Faxvarchar(25)NULL,/*传真*/27. PostalCodevarchar(10)NULL,/*邮编*/28. ConstactPersonvarchar(20)NULL/*联系人*/29. )30. 31. CREATETABLECustomer/*客户表*/32. (33. Customer_IDintIDENTITY(1,1)NOTNULL,/*客户编号,主键*/34. Namevarchar(250)NOTNULL,/*客户名称*/35. Addressvarchar(250)NOTNULL,/*地址*/36. Phonevarchar(25)NULL,/*电话*/37. Faxvarchar(25)NULL,/*传真*/38. PostalCodevarchar(10)NULL,/*邮编*/39. ConstactPersonvarchar(20)NULL/*联系人*/40. )41. 42. CREATETABLEDept/*部门表*/43. (44. Dept_IDintIDENTITY(1,1)NOTNULL,/*部门编号,主键*/45. Namevarchar(30)NOTNULL,/*名称*/46. Remarkvarchar(250)NOTNULL/*描述,备注*/47. )48. 49. CREATETABLEDept_Supplier/*部门-供应商表*/50. (51. Dept_IDintNOTNULL,/*部门编号,主键,外键(参照DEPT表)*/52. Supplier_IDintNOTNULL/*供应商编号,主键,外键(参照SUPPLIER表)*/53. )54. 55. CREATETABLEDept_Customer/*部门-客户表*/56. (57. Dept_IDintNOTNULL,/*部门编号,主键,外键(参照DEPT表)*/58. Customer_IDintNOTNULL/*客户编号,主键,外键(参照SUPPLIER表)*/59. )60. 61. CREATETABLEStoreHouse/*仓库表*/62. (63. StoreHouse_IDintIDENTITY(1,1)NOTNULL,/*仓库编号,主键*/64. Addressvarchar(250)NOTNULL,/*地址*/65. Phonevarchar(25)NULL,/*电话*/66. Employee_IDINTNOTNULL,/*仓库保管,外键(参照EMPLOYEE表)*/67. CreateDatedatetimeNULL/*仓库成立时间*/68. )69. 70. CREATETABLEProductClass/*商品总分类表*/71. (72. ProductClass_IDintIDENTITY(1,1)NOTNULL,/*商品总分类编号,主键*/73. Namevarchar(30)NOTNULL,/*商品分类名称*/74. Employee_IDINTNOTNULL,/*建分类人,外键(参照EMPLOYEE表)*/75. CreateDatedatetimeNULL,/*建分类时间*/76. Remarkvarchar(250)NULL,/*描述,备注*/77. )78. 79. CREATETABLEProductList/*商品细分类表*/80. (81. ProductClass_IDINTNOTNULL,/*商品总分类编号,外键(参照PRODUCTCLASS表)*/82. ProductList_IDintIDENTITY(1,1)NOTNULL,/*商品细分类编号,主键*/83. Namevarchar(30)NOTNULL,/*商品名称*/84. Employee_IDINTNOTNULL,/*建分类人,外键(参照EMPLOYEE表)*/85. CreateDatedatetimeNULL,/*建分类时间*/86. Remarkvarchar(250)NULL,/*描述,备注*/87. )88. 89. CREATETABLEProductSpec/*商品规格表*/90. (91. ProductSpec_IDINTIDENTITY(1,1)NOTNULL,/*商品规格编号,主键*/92. Namevarchar(30)NOTNULL,/*商品规格名称*/93. Employee_IDINTNOTNULL,/*操作员,外键(参照EMPLOYEE表)*/94. CreateDatedatetimeNULL,/*创建时间*/95. Remarkvarchar(250)NULL/*描述,备注*/96. )97. CREATETABLEProductUnit/*商品计量单位表*/98. (99. ProductUnit_IDINTIDENTITY(1,1)NOTNULL,/*计量单位编号,主键*/100. Namevarchar(30)NOTNULL,/*计量单位名称*/101. Employee_IDINTNOTNULL,/*操作员,外键(参照EMPLOYEE表)*/102. CreateDatedatetimeNULL,/*创建时间*/103. Remarkvarchar(250)NULL/*描述,备注*/104. )105. 106. CREATETABLEProduct/*商品目录表*/107. (108. ProductList_IDintNOTNULL,/*商品细分类编号,外键(参照PRODUCTLIST表)*/109. Product_IDINTIDENTITY(1,1)NOTNULL,/*商品名称编号,主键*/110. Namevarchar(30)NOTNULL,/*商品名称*/111. ProductSpec_IDINTNOTNULL,/*商品规格,外键(参照PRODUCTSPEC表)*/112. ProductUnit_IDINTNOTNULL,/*计量单位,外键(参照PRODUCTUNIT表)*/113. PriceMONEYNULL,/*参考价格*/114. Employee_IDINTNOTNULL,/*操作员,外键(参照EMPLOYEE表)*/115. CreateDatedatetimeNULL,/*创建时间*/116. Remarkvarchar(250)NULL/*描述,备注*/117. )118. 119. CREATETABLEProduct_Supplier/*商品-供应商表*/120. (121. Product_IDINTNOTNULL,/*商品名称编号,主键,外键(参照PRODUCT表)*/122. Supplier_IDINTNOTNULL/*供应商编号,主键,外键(参照SUPPLIER表)*/123. )124. 125. CREATETABLEEmployee/*员工表*/126. (127. Employee_IDINTIDENTITY(1,1)NOTNULL,/*员工编号*/128. Dept_IDINTNOTNULL,/*所属部门编号*/129. Namevarchar(30)NOTNULL,/*姓名*/130. Dutyvarchar(20)NOTNULL,/*职务*/131. Gendervarchar(6)NOTNULL,/*性别*/132. BirthDatedatetimeNOTNULL,/*出生日期*/133. HireDatedatetimeNULL,/*合同签订日期*/134. MatureDatedatetimeNULL,/*合同到期日*/135. IdentityCardvarchar(20)NULL,/*身份证号*/136. Addressvarchar(250)NULL,/*住址*/137. Phonevarchar(25)NULL,/*电话*/138. Emailvarchar(30)NULL/*E_MAIL*/139. )140. 141. 142. /*-/-*/143. 144. CREATETABLEBuyOrder/*进货合同*/145. (146. BuyOrder_IDINTIDENTITY(1,1)NOTNULL,/*进货合同编号,主键*/147. WriteDatedatetimeNOTNULL,/*合同签订日期*/148. InsureDatedatetimeNOTNULL,/*合同生效日期*/149. EndDatedatetimeNOTNULL,/*合同到期日期*/150. Dept_IDINTNOTNULL,/*签订部门,外键(参照DEPT表)*/151. Supplier_IDINTNOTNULL,/*供应商,外键(参照SUPPLIER表)*/152. Employee_IDINTNOTNULL/*合同主要负责人,外键(参照EMPLOYEE表)*/153. )154. CREATETABLEBuyOrder_Detail/*进货合同明细表*/155. (156. BuyOrder_IDINTNOTNULL,/*进货合同编号,主键,外键(参照BUYORDER表)*/157. Product_IDINTNOTNULL,/*所进商品编号,主键,外键(参照PRODUCT表)*/158. QuantityINTNOTNULL,/*商品数量*/159. PricemoneyNOTNULL/*商品进价*/160. )161. 162. 163. CREATETABLEEnterStock/*入库单表*/164. (165. EnterStock_IDINTIDENTITY(1,1)NOTNULL,/*入库单编号,主键*/166. EnterDatedatetimeNOTNULL,/*入库时间*/167. Dept_IDINTNOTNULL,/*入库部门,外键(参照DEPT表)*/168. StoreHouse_IDINTNOTNULL,/*所入仓库,外键(参照STOREHOUSE表)*/169. Employee_IDINTNOTNULL/*入库人,外键(参照EMPLOYEE表)*/170. /*需添加仓库保管员如何来验证入库单?*/171. )172. 173. CREATETABLEEnterStock_Detail/*入库单明细*/174. (175. EnterStock_IDINTNOTNULL,/*入库单编号,主键,外键(参照ENTERSTOCK表)*/176. Product_IDINTNOTNULL,/*此种商品编号,主键,外键(参照PRODUCT表)*/177. QuantityintNOTNULL,/*此种商品数量*/178. PricemoneyNULL,/*此种商品参考价格*/179. HaveInvoicebitnotnull,/*此种商品有没有开发票(缺省为0,有没有开票)*/180. InvoiceNumvarchar(30)NULL/*发票号*/181. )182. 183. 184. CREATETABLEBackStock/*退库单表*/185. (186. BackStock_IDINTIDENTITY(1,1)NOTNULL,/*退库单编号,主键*/187. BackDatedatetimeNOTNULL,/*退库时间*/188. Dept_IDINTNOTNULL,/*退库部门,外键(参照DEPT表)*/189. StoreHouse_IDINTNOTNULL,/*所退入仓库,外键(参照STOREHOUSE表)*/190. Employee_IDINTNOTNULL,/*退库人,外键(参照EMPLOYEE表)*/191. Remarkvarchar(250)NULL/*退库原因*/192. 193. )194. CREATETABLEBackStock_Detail/*退库单明细表*/195. (196. BackStock_IDINTNOTNULL,/*退库单编号,主键,外键(参照BACKSTOCK表)*/197. Product_IDINTNOTNULL,/*所退商品编号,主键,外键(参照PRODUCT表)*/198. QuantityintNOTNULL,/*退入数量*/199. PricemoneyNULL/*参考价格*/200. 201. )202. 203. CREATETABLELeaveStock/*出库单表*/204. (205. LeaveStock_IDINTIDENTITY(1,1)NOTNULL,/*出库单编号,主键,外键(参照LEAVESTOCK表)*/206. LeaveDatedatetimeNOTNULL,/*出库时间*/207. Dept_IDINTNOTNULL,/*出库部门,外键(参照DEPT表)*/208. StoreHouse_IDINTNOTNULL,/*所出仓库,外键(参照STOREHOUSE表)*/209. ToStoreHouse_IDINTNOTNULL,/*所入仓库,外键(参照STOREHOUSE表)*/210. Employee_IDINTNOTNULL/*出库人,外键(参照EMPLOYEE表)*/211. /*仓库保管员如何来验证出库单?*/212. )213. 214. CREATETABLELeaveStock_Detail/*出库单明细表*/215. (216. LeaveStock_IDINTNOTNULL,/*出库单编号,主键,外键(参照BACKSTOCK表)*/217. Product_IDINTNOTNULL,/*所出商品编号,主键,外键(参照PRODUCT表)*/218. QuantityintNOTNULL,/*出库数量*/219. PricemoneyNULL/*出库价格*/220. )221. 222. CREATETABLEBackSale/*退货单表*/223. (224. BackSale_IDINTIDENTITY(1,1)NOTNULL,/*退货单编号,主键*/225. BackDatedatetimeNOTNULL,/*退货日期*/226. Dept_IDINTNOTNULL,/*退货部门,外键(参照DEPT表)*/227. StoreHouse_IDINTNOTNULL,/*退入仓库,外键(参照STOREHOUSE表)*/228. Employee_IDINTNOTNULL,/*退货人,外键(参照EMPLOYEE表)*/229. Remarkvarchar(250)NULL/*退货原因*/230. 231. )232. CREATETABLEBackSale_Detail/*退货单明细表*/233. (234. BackSale_IDINTNOTNULL,/*退货单编号,主键,外键(参照BACKSTOCK表)*/235. Product_IDINTNOTNULL,/*所退商品编号,主键,外键(参照PRODUCT表)*/236. QuantityintNOTNULL,/*退货数量*/237. PricemoneyNULL/*价格*/238. 239. )240. 241. 242. CREATETABLESaleOrder/*销售合同*/243. (244. SaleOrder_IDINTIDENTITY(1,1)NOTNULL,/*合同编号,主键*/245. WriteDatedatetimeNOTNULL,/*合同签订日期*/246. InsureDatedatetimeNOTNULL,/*合同生效日期*/247. EndDatedatetimeNOTNULL,/*合同到期日期*/248. Dept_IDINTNOTNULL,/*签订部门,外键(参照DEPT表)*/249. Customer_IDINTNOTNULL,/*客户编号,外键(参照CUSTOMER表)*/250. Employee_IDINTNOTNULL/*合同主要负责人,外键(参照EMPLOYEE表)*/251. )252. CREATETABLESaleOrder_Detail/*销售合同明细表*/253. (254. SaleOrder_IDINTNOTNULL,/*销售合同编号,主键,外键(参照BUYORDER表)*/255. Product_IDINTNOTNULL,/*销售商品编号,主键,外键(参照PRODUCT表)*/256. Quantityintnotnull,/*商品数量*/257. Pricemoneynull/*商品进价*/258. )259. 260. 261. CREATETABLEBuy/*进货表(验货表)*/262. (263. Buy_IDINTIDENTITY(1,1)NOTNULL,/*进货编号,主键*/264. ComeDatedatetimeNOTNULL,/*进货日期*/265. Dept_IDINTNOTNULL,/*进货部门,外键(参照DEPT表)*/266. Employee_IDINTNOTNULL/*验货人,外键(参照EMPLOYEE表)*/267. )268. 269. CREATETABLEBuy_Detail/*进货表明细(验货表)*/270. (271. Buy_IDINTNOTNULL,/*进货编号,主键,外键(参照BUY表)*/272. Product_IDINTNOTNULL,/*商品编号,主键,外键(参照PRODUCT表)*/273. BuyOrder_IDINTNULL,/*采购合同,外键(参照BUYORDER表)*/274. Quantityintnotnull,/*数量*/275. Pricemoneynull/*价格*/276. 277. /*BUYORDER_ID为NULL时,为现金进货*/278. )279. 280. CREATETABLESale/*销售表*/281. (282. Sale_IDINTIDENTITY(1,1)NOTNULL,/