Sql script - Githubissues

mustifk commented 3 years ago

CREATE DATABASE OWISDB GO

USE OWISDB GO

CREATE TABLE Users( /admin tarafından elle eklenilecek - CRUD/ userID INT IDENTITY PRIMARY KEY NOT NULL, userName varchar(20) NOT NULL, userPassword varchar(20) NOT NULL, firstName varchar(20), lastName varchar(20), userImage varchar(MAX), userType char(1), /selectbox - 0 usual user/ sadece görüntüleme - 1 staff/ ekle kaldır - 2 admin/ tüm yetki/ userAddress varchar(50), phoneNumber varchar(15), email varchar(30) );

CREATE TABLE Staff( /- elle doldurulacak - Usera bağlı - type 1 ise staff doldurulacak - 2 ise admin için otomatik dolacak/ staffID INT IDENTITY PRIMARY KEY NOT NULL, userID INT NOT NULL, warehouseID INT NOT NULL, /selectbox/ title varchar(15), FOREIGN KEY(userID) REFERENCES Users(userID), FOREIGN KEY(warehouseID) REFERENCES Warehouse(warehouseID) );

CREATE TABLE Product( /ürünler - elle doldurulacak - CRUD/ productID INT IDENTITY PRIMARY KEY NOT NULL, productName varchar(30), productDescription varchar(30), productImage nvarchar(MAX), manufacturer varchar(20), productWeight INT, inStock bit, totalQuantity INT );

CREATE TABLE Orders( /gelen paketler/ orderID INT IDENTITY PRIMARY KEY NOT NULL, staffID INT NOT NULL, warehouseID INT NOT NULL, /selectbox - nereye geleceği/ orderDescription nvarchar(100), orderDate DATETIME, FOREIGN KEY(staffID) REFERENCES Staff(staffID), FOREIGN KEY(warehouseID) REFERENCES Warehouse(warehouseID) );

CREATE TABLE Shipments( /giden paketler/ shipmentID INT IDENTITY PRIMARY KEY NOT NULL, staffID INT NOT NULL, warehouseID INT NOT NULL, /selectbox - nereden çıktığı/ orderDescription nvarchar(100), orderDate DATETIME, FOREIGN KEY(staffID) REFERENCES Staff(staffID), FOREIGN KEY(warehouseID) REFERENCES Warehouse(warehouseID) );

CREATE TABLE Payment( /gelen paket ödemesi - karşıya/ orderID INT NOT NULL, invoiceNumber varchar(10),/fatura numarası/ paymentType varchar(10), /selectbox/ amount INT, operationDate DATETIME, FOREIGN KEY(orderID) REFERENCES Orders(orderID) );

CREATE TABLE Bills( /giden paketlerin ödemesi - bize/ shipmentID INT NOT NULL, invoiceNumber varchar(10), /fatura numarası/ paymentType varchar(10), /selectbox/ amount INT, operationDate DATETIME, FOREIGN KEY(shipmentID) REFERENCES Shipments(shipmentID) );

CREATE TABLE Supplier( / tedarikçiler - elle doldurulacak - CRUD/ supplierID INT IDENTITY PRIMARY KEY NOT NULL, supplierName varchar(20), supplierAddress varchar(50), phoneNumber varchar(15), email varchar(30) );

CREATE TABLE Shipping( /kargolama süreci/ packageID INT NOT NULL, shippingDate DATETIME, estimatedDeliveryDate DATETIME, /warehouseAddress varchar(50), /selectbox/ destinationAdress varchar(50),/ currentLocation varchar(20), currentStatus varchar(20), shippingNote varchar(100), FOREIGN KEY(packageID) REFERENCES Package(packageID) );

CREATE TABLE Package( / kargo detayı - paket detayı - supplier seçilecek/ eğer biz isek otomatik dolacak/ packageID INT IDENTITY PRIMARY KEY NOT NULL, orderID INT, shipmentID INT, packageType bit NOT NULL, / 0 gelen, 1 giden - buna göre order veya shipment id gelecek/ / CHECK EKLE/ supplierID INT NOT NULL, /selectbox - tedarikçi seçilecek eğer bizden gidiyorsa otomatik dolacak/ createdTime DATETIME, packageStatus varchar(20), notes varchar(100), FOREIGN KEY(orderID) REFERENCES Orders(orderID), FOREIGN KEY(shipmentID) REFERENCES Shipments(shipmentID), FOREIGN KEY(supplierID) REFERENCES Supplier(supplierID), );

CREATE TABLE PackageContent( / paket içeriği - ürünler ve sayıları burada belirtilecek/ packageID INT NOT NULL, productID INT NOT NULL, productQuantity INT, productName varchar(20), FOREIGN KEY(productID) REFERENCES Product(productID), FOREIGN KEY(packageID) REFERENCES Package(packageID), );

CREATE TABLE Inventory( / depolardaki toplam içerik burada belirtilecek/ productID INT NOT NULL, totalQuantity INT, updateDate DATETIME, FOREIGN KEY(productID) REFERENCES Product(productID) );

CREATE TABLE Warehouse( / Depolar - elle doldurulacak - CRUD/ warehouseID INT IDENTITY PRIMARY KEY NOT NULL, warehouseName varchar(30), country varchar(20), city varchar(20), warehouseAddress varchar(50), ZIP varchar(5), warehouseStatus varchar(20), phoneNumber varchar(15), email varchar(30), maxCapacity varchar(10) );

CREATE TABLE WarehouseContent( / deponun içeriği - elle doldurulabilir - CRUD/ warehouseID INT IDENTITY NOT NULL, productID INT NOT NULL, quantity INT, FOREIGN KEY(warehouseID) REFERENCES Warehouse(warehouseID) );

GO