一個進(jìn)銷存數(shù)據(jù)庫設(shè)計的例子Word版

1、傳播優(yōu)秀Word版文檔 ，希望對您有幫助，可雙擊去除！一個進(jìn)銷存數(shù)據(jù)庫設(shè)計的例子分類：SQL SERVER2009-11-26 15:476004人閱讀評論(3)收藏舉報數(shù)據(jù)庫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

2、,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_eJCICvarcha

3、r(1)NULL19. )20. CREATETABLESupplier/*供應(yīng)商表*/21. (22. Supplier_IDintIDENTITY(1,1)NOTNULL,/*供應(yīng)商編號,主鍵*/23. Namevarchar(250)NOTNULL,/*供應(yīng)商名稱*/24. Addressvarchar(250)NOTNULL,/*地址*/25. Phonevarchar(25)NULL,/*電話*/26. Faxvarchar(25)NULL,/*傳真*/27. PostalCodevarchar(10)NULL,/*郵編*/28. ConstactPersonvarchar(20)N

4、ULL/*聯(lián)系人*/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(2

5、0)NULL/*聯(lián)系人*/傳播優(yōu)秀Word版文檔 ，希望對您有幫助，可雙擊去除！40. )41. 42. CREATETABLEDept/*部門表*/43. (44. Dept_IDintIDENTITY(1,1)NOTNULL,/*部門編號,主鍵*/45. Namevarchar(30)NOTNULL,/*名稱*/46. Remarkvarchar(250)NOTNULL/*描述,備注*/47. )48. 49. CREATETABLEDept_Supplier/*部門-供應(yīng)商表*/50. (51. Dept_IDintNOTNULL,/*部門編號,主鍵,外鍵(參照DEPT表)*/52. S

6、upplier_IDintNOTNULL/*供應(yīng)商編號,主鍵,外鍵(參照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. A

7、ddressvarchar(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. Em

8、ployee_IDINTNOTNULL,/*建分類人,外鍵(參照EMPLOYEE表)*/75. CreateDatedatetimeNULL,/*建分類時間*/76. Remarkvarchar(250)NULL,/*描述,備注*/77. )78. 79. CREATETABLEProductList/*商品細(xì)分類表*/80. (81. ProductClass_IDINTNOTNULL,/*商品總分類編號,外鍵(參照PRODUCTCLASS表)*/82. ProductList_IDintIDENTITY(1,1)NOTNULL,/*商品細(xì)分類編號,主鍵*/傳播優(yōu)秀Word版文檔 ，希望對您

9、有幫助，可雙擊去除！83. Namevarchar(30)NOTNULL,/*商品名稱*/84. Employee_IDINTNOTNULL,/*建分類人,外鍵(參照EMPLOYEE表)*/85. CreateDatedatetimeNULL,/*建分類時間*/86. Remarkvarchar(250)NULL,/*描述,備注*/87. )88. 89. CREATETABLEProductSpec/*商品規(guī)格表*/90. (91. ProductSpec_IDINTIDENTITY(1,1)NOTNULL,/*商品規(guī)格編號,主鍵*/92. Namevarchar(30)NOTNULL,/*

10、商品規(guī)格名稱*/93. Employee_IDINTNOTNULL,/*操作員,外鍵(參照EMPLOYEE表)*/94. CreateDatedatetimeNULL,/*創(chuàng)建時間*/95. Remarkvarchar(250)NULL/*描述,備注*/96. )97. CREATETABLEProductUnit/*商品計量單位表*/98. (99. ProductUnit_IDINTIDENTITY(1,1)NOTNULL,/*計量單位編號,主鍵*/100. Namevarchar(30)NOTNULL,/*計量單位名稱*/101. Employee_IDINTNOTNULL,/*操作員,

11、外鍵(參照EMPLOYEE表)*/102. CreateDatedatetimeNULL,/*創(chuàng)建時間*/103. Remarkvarchar(250)NULL/*描述,備注*/104. )105. 106. CREATETABLEProduct/*商品目錄表*/107. (108. ProductList_IDintNOTNULL,/*商品細(xì)分類編號,外鍵(參照PRODUCTLIST表)*/109. Product_IDINTIDENTITY(1,1)NOTNULL,/*商品名稱編號,主鍵*/110. Namevarchar(30)NOTNULL,/*商品名稱*/111. ProductSp

12、ec_IDINTNOTNULL,/*商品規(guī)格,外鍵(參照PRODUCTSPEC表)*/112. ProductUnit_IDINTNOTNULL,/*計量單位,外鍵(參照PRODUCTUNIT表)*/113. PriceMONEYNULL,/*參考價格*/114. Employee_IDINTNOTNULL,/*操作員,外鍵(參照EMPLOYEE表)*/115. CreateDatedatetimeNULL,/*創(chuàng)建時間*/116. Remarkvarchar(250)NULL/*描述,備注*/117. )118. 119. CREATETABLEProduct_Supplier/*商品-供應(yīng)

13、商表*/120. (121. Product_IDINTNOTNULL,/*商品名稱編號,主鍵,外鍵(參照PRODUCT表)*/122. Supplier_IDINTNOTNULL/*供應(yīng)商編號,主鍵,外鍵(參照SUPPLIER表)*/123. )傳播優(yōu)秀Word版文檔 ，希望對您有幫助，可雙擊去除！124. 125. CREATETABLEEmployee/*員工表*/126. (127. Employee_IDINTIDENTITY(1,1)NOTNULL,/*員工編號*/128. Dept_IDINTNOTNULL,/*所屬部門編號*/129. Namevarchar(30)NOTNUL

14、L,/*姓名*/130. Dutyvarchar(20)NOTNULL,/*職務(wù)*/131. Gendervarchar(6)NOTNULL,/*性別*/132. BirthDatedatetimeNOTNULL,/*出生日期*/133. HireDatedatetimeNULL,/*合同簽訂日期*/134. MatureDatedatetimeNULL,/*合同到期日*/135. IdentityCardvarchar(20)NULL,/*身份證號*/136. Addressvarchar(250)NULL,/*住址*/137. Phonevarchar(25)NULL,/*電話*/138.

15、 Emailvarchar(30)NULL/*E_MAIL*/139. )140. 141. 142. /*-/-*/143. 144. CREATETABLEBuyOrder/*進(jìn)貨合同*/145. (146. BuyOrder_IDINTIDENTITY(1,1)NOTNULL,/*進(jìn)貨合同編號,主鍵*/147. WriteDatedatetimeNOTNULL,/*合同簽訂日期*/148. InsureDatedatetimeNOTNULL,/*合同生效日期*/149. EndDatedatetimeNOTNULL,/*合同到期日期*/150. Dept_IDINTNOTNULL,/*簽

16、訂部門,外鍵(參照DEPT表)*/151. Supplier_IDINTNOTNULL,/*供應(yīng)商,外鍵(參照SUPPLIER表)*/152. Employee_IDINTNOTNULL/*合同主要負(fù)責(zé)人,外鍵(參照EMPLOYEE表)*/153. )154. CREATETABLEBuyOrder_Detail/*進(jìn)貨合同明細(xì)表*/155. (156. BuyOrder_IDINTNOTNULL,/*進(jìn)貨合同編號,主鍵,外鍵(參照BUYORDER表)*/157. Product_IDINTNOTNULL,/*所進(jìn)商品編號,主鍵,外鍵(參照PRODUCT表)*/158. QuantityINT

17、NOTNULL,/*商品數(shù)量*/159. PricemoneyNOTNULL/*商品進(jìn)價*/160. )161. 162. 163. CREATETABLEEnterStock/*入庫單表*/164. (165. EnterStock_IDINTIDENTITY(1,1)NOTNULL,/*入庫單編號,主鍵*/166. EnterDatedatetimeNOTNULL,/*入庫時間*/傳播優(yōu)秀Word版文檔 ，希望對您有幫助，可雙擊去除！167. Dept_IDINTNOTNULL,/*入庫部門,外鍵(參照DEPT表)*/168. StoreHouse_IDINTNOTNULL,/*所入倉庫,

18、外鍵(參照STOREHOUSE表)*/169. Employee_IDINTNOTNULL/*入庫人,外鍵(參照EMPLOYEE表)*/170. /*需添加倉庫保管員如何來驗證入庫單?*/171. )172. 173. CREATETABLEEnterStock_Detail/*入庫單明細(xì)*/174. (175. EnterStock_IDINTNOTNULL,/*入庫單編號,主鍵,外鍵(參照ENTERSTOCK表)*/176. Product_IDINTNOTNULL,/*此種商品編號,主鍵,外鍵(參照PRODUCT表)*/177. QuantityintNOTNULL,/*此種商品數(shù)量*/

19、178. PricemoneyNULL,/*此種商品參考價格*/179. HaveInvoicebitnotnull,/*此種商品有沒有開發(fā)票(缺省為0,有沒有開票)*/180. InvoiceNumvarchar(30)NULL/*發(fā)票號*/181. )182. 183. 184. CREATETABLEBackStock/*退庫單表*/185. (186. BackStock_IDINTIDENTITY(1,1)NOTNULL,/*退庫單編號,主鍵*/187. BackDatedatetimeNOTNULL,/*退庫時間*/188. Dept_IDINTNOTNULL,/*退庫部門,外鍵(

20、參照DEPT表)*/189. StoreHouse_IDINTNOTNULL,/*所退入倉庫,外鍵(參照STOREHOUSE表)*/190. Employee_IDINTNOTNULL,/*退庫人,外鍵(參照EMPLOYEE表)*/191. Remarkvarchar(250)NULL/*退庫原因*/192. 193. )194. CREATETABLEBackStock_Detail/*退庫單明細(xì)表*/195. (196. BackStock_IDINTNOTNULL,/*退庫單編號,主鍵,外鍵(參照BACKSTOCK表)*/197. Product_IDINTNOTNULL,/*所退商品編

21、號,主鍵,外鍵(參照PRODUCT表)*/198. QuantityintNOTNULL,/*退入數(shù)量*/199. PricemoneyNULL/*參考價格*/200. 201. )202. 203. CREATETABLELeaveStock/*出庫單表*/204. (205. LeaveStock_IDINTIDENTITY(1,1)NOTNULL,/*出庫單編號,主鍵,外鍵(參照LEAVESTOCK表)*/206. LeaveDatedatetimeNOTNULL,/*出庫時間*/傳播優(yōu)秀Word版文檔 ，希望對您有幫助，可雙擊去除！207. Dept_IDINTNOTNULL,/*出庫

22、部門,外鍵(參照DEPT表)*/208. StoreHouse_IDINTNOTNULL,/*所出倉庫,外鍵(參照STOREHOUSE表)*/209. ToStoreHouse_IDINTNOTNULL,/*所入倉庫,外鍵(參照STOREHOUSE表)*/210. Employee_IDINTNOTNULL/*出庫人,外鍵(參照EMPLOYEE表)*/211. /*倉庫保管員如何來驗證出庫單?*/212. )213. 214. CREATETABLELeaveStock_Detail/*出庫單明細(xì)表*/215. (216. LeaveStock_IDINTNOTNULL,/*出庫單編號,主鍵,

23、外鍵(參照BACKSTOCK表)*/217. Product_IDINTNOTNULL,/*所出商品編號,主鍵,外鍵(參照PRODUCT表)*/218. QuantityintNOTNULL,/*出庫數(shù)量*/219. PricemoneyNULL/*出庫價格*/220. )221. 222. CREATETABLEBackSale/*退貨單表*/223. (224. BackSale_IDINTIDENTITY(1,1)NOTNULL,/*退貨單編號,主鍵*/225. BackDatedatetimeNOTNULL,/*退貨日期*/226. Dept_IDINTNOTNULL,/*退貨部門,外

24、鍵(參照DEPT表)*/227. StoreHouse_IDINTNOTNULL,/*退入倉庫,外鍵(參照STOREHOUSE表)*/228. Employee_IDINTNOTNULL,/*退貨人,外鍵(參照EMPLOYEE表)*/229. Remarkvarchar(250)NULL/*退貨原因*/230. 231. )232. CREATETABLEBackSale_Detail/*退貨單明細(xì)表*/233. (234. BackSale_IDINTNOTNULL,/*退貨單編號,主鍵,外鍵(參照BACKSTOCK表)*/235. Product_IDINTNOTNULL,/*所退商品編號

25、,主鍵,外鍵(參照PRODUCT表)*/236. QuantityintNOTNULL,/*退貨數(shù)量*/237. PricemoneyNULL/*價格*/238. 239. )240. 241. 242. CREATETABLESaleOrder/*銷售合同*/243. (244. SaleOrder_IDINTIDENTITY(1,1)NOTNULL,/*合同編號,主鍵*/245. WriteDatedatetimeNOTNULL,/*合同簽訂日期*/246. InsureDatedatetimeNOTNULL,/*合同生效日期*/247. EndDatedatetimeNOTNULL,/*

26、合同到期日期*/傳播優(yōu)秀Word版文檔 ，希望對您有幫助，可雙擊去除！248. Dept_IDINTNOTNULL,/*簽訂部門,外鍵(參照DEPT表)*/249. Customer_IDINTNOTNULL,/*客戶編號,外鍵(參照CUSTOMER表)*/250. Employee_IDINTNOTNULL/*合同主要負(fù)責(zé)人,外鍵(參照EMPLOYEE表)*/251. )252. CREATETABLESaleOrder_Detail/*銷售合同明細(xì)表*/253. (254. SaleOrder_IDINTNOTNULL,/*銷售合同編號,主鍵,外鍵(參照BUYORDER表)*/255. P

27、roduct_IDINTNOTNULL,/*銷售商品編號,主鍵,外鍵(參照PRODUCT表)*/256. Quantityintnotnull,/*商品數(shù)量*/257. Pricemoneynull/*商品進(jìn)價*/258. )259. 260. 261. CREATETABLEBuy/*進(jìn)貨表(驗貨表)*/262. (263. Buy_IDINTIDENTITY(1,1)NOTNULL,/*進(jìn)貨編號,主鍵*/264. ComeDatedatetimeNOTNULL,/*進(jìn)貨日期*/265. Dept_IDINTNOTNULL,/*進(jìn)貨部門,外鍵(參照DEPT表)*/266. Employee_

28、IDINTNOTNULL/*驗貨人,外鍵(參照EMPLOYEE表)*/267. )268. 269. CREATETABLEBuy_Detail/*進(jìn)貨表明細(xì)(驗貨表)*/270. (271. Buy_IDINTNOTNULL,/*進(jìn)貨編號,主鍵,外鍵(參照BUY表)*/272. Product_IDINTNOTNULL,/*商品編號,主鍵,外鍵(參照PRODUCT表)*/273. BuyOrder_IDINTNULL,/*采購合同,外鍵(參照BUYORDER表)*/274. Quantityintnotnull,/*數(shù)量*/275. Pricemoneynull/*價格*/276. 277. /*BUYORDER_ID為NULL時,為現(xiàn)金進(jìn)貨*/278. )279. 280. CREATETABLESale/*銷售表*/281. (282. Sale_IDINTIDENTITY(1,1)NOTNULL,/*銷售編號*/283. SaleDatedatetime