How to create a normalized database to manage inventory for a small business? I am writing a Java application to manage the inventory of a small furniture business. I know little about databases, I have a bit of experience with SQL queries and more experience with Java programming, however I have never created a program that requires a database and I do not know how to properly design the database tables for maximum efficiency and no redundancy.

The program requires tables for the items being sold and the customers they will be sold to. The main product being sold is carpets and I expect it to have its own table? Other products like vases, desks, etc. can be under one table because they don't have unique details like the carpet size.

Thus far my database is looking like this:
(Primary and foreign keys are in italics)



Customer:

-CustomerID PK,

-FirstName, 

-LastName, 

-Address, 

-Phone, 

-Email

Order:

-OrderID PK, 

-CustomerID FK >- Customer.CustomerID

OrderDetails:

-OrderDate, 

-SoldPrice, 

-OrderDescription, 

-InvoiceNumber,

-OrderID FK >- Order.OrderID, 

-ProductID FK >- product.ProductID

Product:

-ProductID PK, 

-ProductName, 

-ProductPrice, 

-ProductCode, 

-ProductTypeID FK >- ProductType.ProductTypeID

ProductType:

-ProductTypeID PK, 

-ProductTypeDesc

CarpetProduct:

-CarpetID PK, 

-CarpetName, 

-CarpetCode, 

-CarpetLength, 

-CarpetWidth, 

-CarpetDescription, 

-ProductID FK >- Product.ProductID

OtherProduct:

-OtherProductID PK, 

-OtherProductDescription, 

-OtherProductCode, 

-ProductID FK >- Product.ProductID



Please help me organize this database by adding, removing, or joining tables and fixing any issues. I know this table is far from being normalized and efficient. Any help would be greatly appreciated. Let me know if further details are required.

All the carpets follow one ID system (starting from 1 to around 4000) and all other items (that are not carpets) follow a separate ID system. So their are two separate codes used depending on whether it's a product or not.
Its a good start.


You probably want to split out the customer table: One for the businesses and one for the people.
On tblCarpets and tblOtherProducts, what about a keyword field? You could do some nifty stuff in Java with that.
Each table could benefit from a create_timestamp, modify_timestamp and, if you've got a way to track users (a tblUsers), created_by and modified_by.
Hide_ind - you might want to hide information from the front end, but keep it on the back end for reporting purposes.
You can get rid of 'tbl' in SQL. Its like saying ATM Machine - the M stands for Machine.
A discounts table, maybe? How would you track price changes?
Do they have their own trucks? How many staff? Do you want to track who sent what where, and how?


A few updates from the comments - I'd create a few tables like this:

create table items (itemID int, Type varchar(255), item_name varchar(255), keyword nvarchar(1000), starting_length int,
create_timestamp datetime, modify_timestamp datetime)

create table prices (item_id int, price float, create_timestamp datetime, modify_timestamp datetime)

create table inventory (itemID int, current_length int, create_timestamp datetime, modify_timestamp datetime)


and for a keyword search, you might (might, i don't know java) use a stored procedure like this:

create procedure fetch_items_by_keyword (@keyword varchar(255))

as

set @keyword = '%' + isnull(@keyword,'') + '%'

select item_id,item_name, type, keyword
from items
where keyword like @keyword


I'd let everything stew in your brain for a while. sleep on it and start making your changes tomorrow.