Untitled

CREATE TABLE Item(
  Itemid INTEGER NOT NULL,
  ItemName VARCHAR,
  ItemCategory VARCHAR,
  CurrencyCode VARCHAR,
  Unit VARCHAR,
  UnitPrice FLOAT,
  ItemGroup INTEGER NOT NULL,
  PRIMARY KEY (Itemid),
);
CREATE INDEX Item_id ON Item(Itemid);


CREATE TABLE PurchaseHeader(
  Purchid INTEGER NOT NULL,
  Vendorid INTEGER NOT NULL,
  Postcode NUMERIC(5) NOT NULL,
  PRIMARY KEY (Purchid)
);
CREATE INDEX Purch_id ON PurchaseHeader(Purchid, Vendorid);

CREATE TABLE Purchaseline(
  Purchid INTEGER NOT NULL,
  Itemid INTEGER NOT NULL,
  Qty DECIMAL,
  Price DECIMAL,
  Netamount DECIMAL,
  Deliverydate DATETIME DEFAULT CURRENT_TIMESTAMP,
  discountpercentage NUMERIC,
  packingslipid VARCHAR(10),
  receivedqty FLOAT,
  remainqty FLOAT,
  PRIMARY KEY (Purchid),
  FOREIGN KEY (Purchid) REFERENCES PurchaseHeader(Purchid),
  FOREIGN KEY (Itemid) REFERENCES Item(Itemid),

);
CREATE INDEX Purch_id ON Purchaseline(Purchid);


CREATE TABLE Vendor(
  Vendorid INTEGER NOT NULL,
  Vendname VARCHAR,
  VKN INTEGER,
  Taxoffice VARCHAR,
  mail VARCHAR,
  phone VARCHAR,
  relevantperson VARCHAR,
  website VARCHAR,
  Street VARCHAR,
  PRIMARY KEY (Vendorid),
  FOREIGN KEY (Vendorid) REFERENCES PurchaseHeader(Vendorid),
);
CREATE INDEX Vendor_id ON Vendor(Vendorid);

CREATE TABLE ItemGroup(
  ItemGroup INTEGER NOT NULL,
  Ledgeraccount VARCHAR,
  PRIMARY KEY (ItemGroup, LedgerAccount),
  FOREIGN KEY (ItemGroup) REFERENCES Item(ItemGroup),
  );
CREATE INDEX Item_Group ON ItemGroup(ItemGroup);


CREATE TABLE Postalcode(
  Postcode NUMERIC(5) NOT NULL,
  Cityid NUMERIC(2) NOT NULL,
  Countyid VARCHAR,
  CityName VARCHAR,
  CountyName VARCHAR,
  PRIMARY KEY (Postcode),
  FOREIGN KEY (Postcode) REFERENCES PurchaseHeader(Postcode),
);
CREATE INDEX Post_code ON Postalcode(Postcode);