topic Re: On in Data Warehouse

On

Tamiru — Mon, 25 Aug 2025 20:49:55 GMT

-- 1. Warehouse Table

CREATE TABLE Warehouse (

Warehouse_ID INT PRIMARY KEY,

Name VARCHAR(100) NOT NULL,

Location VARCHAR(150),

Capacity INT

);

-- 2. Item Table

CREATE TABLE Item (

Item_ID INT PRIMARY KEY,

Name VARCHAR(100) NOT NULL,

Description TEXT,

Unit VARCHAR(50),

Category VARCHAR(100),

Reorder_Level INT

);

-- 3. Supplier Table

CREATE TABLE Supplier (

Supplier_ID INT PRIMARY KEY,

Name VARCHAR(100) NOT NULL,

Contact VARCHAR(100),

Address VARCHAR(200)

);

-- 4. Customer Table

CREATE TABLE Customer (

Customer_ID INT PRIMARY KEY,

Name VARCHAR(100) NOT NULL,

Contact VARCHAR(100),

Address VARCHAR(200)

);

-- 5. Stock Table

CREATE TABLE Stock (

Stock_ID INT PRIMARY KEY,

Warehouse_ID INT,

Item_ID INT,

Quantity INT NOT NULL,

Last_Updated DATE,

FOREIGN KEY (Warehouse_ID) REFERENCES Warehouse(Warehouse_ID),

FOREIGN KEY (Item_ID) REFERENCES Item(Item_ID)

);

-- 6. Purchase Order Tables

CREATE TABLE PurchaseOrder (

PO_ID INT PRIMARY KEY,

Supplier_ID INT,

Date DATE,

Status VARCHAR(50),

FOREIGN KEY (Supplier_ID) REFERENCES Supplier(Supplier_ID)

);

CREATE TABLE PurchaseOrderDetail (

PO_ID INT,

Item_ID INT,

Quantity INT,

Price DECIMAL(10,2),

PRIMARY KEY (PO_ID, Item_ID),

FOREIGN KEY (PO_ID) REFERENCES PurchaseOrder(PO_ID),

FOREIGN KEY (Item_ID) REFERENCES Item(Item_ID)

);

-- 7. Sales Order Tables

CREATE TABLE SalesOrder (

SO_ID INT PRIMARY KEY,

Customer_ID INT,

Date DATE,

Status VARCHAR(50),

FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID)

);

CREATE TABLE SalesOrderDetail (

SO_ID INT,

Item_ID INT,

Quantity INT,

Price DECIMAL(10,2),

PRIMARY KEY (SO_ID, Item_ID),

FOREIGN KEY (SO_ID) REFERENCES SalesOrder(SO_ID),

FOREIGN KEY (Item_ID) REFERENCES Item(Item_ID)

);

-- 8. Stock Movement Table

CREATE TABLE StockMovement (

Movement_ID INT PRIMARY KEY,

Item_ID INT,

Warehouse_ID INT,

Date DATE,

Movement_Type VARCHAR(10) CHECK (Movement_Type IN ('IN','OUT')),

Quantity INT,

Ref_Order_ID INT,

FOREIGN KEY (Item_ID) REFERENCES Item(Item_ID),

FOREIGN KEY (Warehouse_ID) REFERENCES Warehouse(Warehouse_ID)

);

Re: On

Vinodh247 — Tue, 26 Aug 2025 01:10:12 GMT

Can you add more details? the question seems to be unclear.

Re: On

Anonymous — Tue, 26 Aug 2025 09:27:42 GMT

Hi Tamiru ,

Thanks for posting your question. To ensure you get the best solution, could you provide a bit more detail? Specifically:

What is your exact goal or the output you’re looking for?
If possible, share a small sample of your data and the desired result.

With this information, the community will be able to offer more targeted and effective guidance.

Best Regards,
Tejaswi.
Community Support

Re: On

AntoineW — Thu, 28 Aug 2025 08:16:00 GMT

Hello Tamiru,

The provided schema is an excellent starting point for an inventory management database. I found some recommendations to make it more robust, flexible, and better suited to future business needs :

Database Schema Recommendations for Inventory Management

Enhance Inventory Granularity
- Recommendation: The current schema manages stock at the warehouse and item level. To enable more granular management and traceability of items within a warehouse, it is recommended to introduce an additional level of detail.
- Action: Create a Location table (Location_ID, Warehouse_ID, Aisle, Shelf) and link the Stock table to this new table. This will allow for tracking the precise location of each item within a warehouse.
Order Traceability over Time
- Recommendation: The detail tables for orders (PurchaseOrderDetail and SalesOrderDetail) currently lack a timestamp field. If an order is delivered or sold in multiple parts, it is crucial to be able to record these events.
- Action: Add a Date or Last_Updated column to the PurchaseOrderDetail and SalesOrderDetail tables. This will allow you to track partial deliveries or specific fulfillment dates for each item in an order.
Improve Code Readability
- Recommendation: For better clarity and easier maintenance, it's a good practice to standardize naming conventions for identifiers. Using full names instead of abbreviations makes the code more readable for the entire team.
- Action: It is often preferable to use full names like PurchaseOrder_ID and SalesOrder_ID instead of PO_ID and SO_ID.

Hope it can help you and add more details if you needs !

Best regards,

Antoine

Re: On

Anonymous — Fri, 29 Aug 2025 10:33:02 GMT

Hi Tamiru ,

Thank you for your question. To ensure we provide the best guidance, could you clarify what specific outcome you’re aiming for?

What is the exact result you want to achieve?
If possible, please provide a small data sample along with your expected output.

This information will help the community deliver precise and effective solutions.

Thank you,

Tejaswi.

Re: On

Anonymous — Mon, 01 Sep 2025 09:40:49 GMT

Hi Tamiru ,

Thanks for your question! Could you share your goal and a small sample of your data with the expected result? This will help the community give a more accurate solution.

Thank you,

Tejaswi.

Re: On

Anonymous — Mon, 08 Sep 2025 05:37:42 GMT

Hi Tamiru ,

To ensure you get the most effective guidance, please provide a few more details:

What specific outcome are you aiming for?
Could you share a small sample of your data along with the desired result?

With this information, the community will be able to quickly understand your scenario and deliver precise solutions.

Thank you,

Tejaswi.