2

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.

closed as too broad by McNets, LowlyDBA, Erik Darling, RDFozz, mustaccio Dec 28 '17 at 14:07

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Asking us is great, but have you asked the furniture company? Who has been tracking this for them in the past? How? Can you take a look at their spreadsheets? Do the carpets already have a unique ID system? What other properties of the carpets need to be tracked (thickness, material etc.). – James Dec 27 '17 at 17:42
  • @James It is a family business that is expanding. Such a system has never existed before, prior to this assignment all data was on paper. The idea is to make all that data digitally available to improve stock taking. The carpets do have a unique ID system, it is the code as listed in the tblCarpets and it is an integer that increases for every new item brought in. Only the carpet size is to be tracked. – badProgrammer Dec 27 '17 at 17:48
  • Cool. Try and get a look at the papers, and go through their old methodology with that person, if you can. It might be really useful from a development stand point, and at the very least, it will get other people invested in your project. – James Dec 27 '17 at 17:59
  • @James I have tried to do it with them, and they have provided as much help as possible but they don't know anything regarding databases. All they used to do was ID their inventory and keep a record of the customer details. I've gotten all the details that I can, not sure what else I can do. – badProgrammer Dec 27 '17 at 18:03
0

Its a good start.

  1. You probably want to split out the customer table: One for the businesses and one for the people.

  2. On tblCarpets and tblOtherProducts, what about a keyword field? You could do some nifty stuff in Java with that.

  3. 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.

  4. Hide_ind - you might want to hide information from the front end, but keep it on the back end for reporting purposes.

  5. You can get rid of 'tbl' in SQL. Its like saying ATM Machine - the M stands for Machine.

  6. A discounts table, maybe? How would you track price changes?

  7. 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.

  • 1. This entire program is only for the employer/manager of the business, so no customers or employees will be using it. 2. How would that work? Not sure I quite understand. 6. Would a completely separate table be necessary for discounts? And would it be silly to create a table just for prices and create a discount field in there? Thanks for all the help and excuse my many questions... – badProgrammer Dec 27 '17 at 18:14
  • 7. For the time being, the program is simply for inventory. It won't manage staff, vehicles, or transport. – badProgrammer Dec 27 '17 at 18:17
  • Np, lol. It's fun to think about this stuff and it's a slow day at work. 1. yea, but employees of the business you are sending the carpets to may change over time, or they might have more than one. 2. think of an array like [red, polyester, thick]. 6. Maybe. It depends on how you want to handle it in the front end. I think you'll definitely want a separate table for prices, though. They might change a lot, and you might want to keep track of it. – James Dec 27 '17 at 18:20
  • Okay I see. I would then add in a Price table but not too sure how. And for the keyword field I will use the carpet colour. Then I can do specific searches based on colour, size, and type of carpet. Those are all the details necessary. However, I still don't know how to properly link and fix the tables. If it isn't too much work, do you think you could show me or explain to me how to make these changes? – badProgrammer Dec 27 '17 at 18:28
  • Thank you for the update. That helps quite a bit. I will make the updates. However, I am not familiar with the timestamps, what is it for? It is not required to enter any dates and times. – badProgrammer Dec 27 '17 at 22:28
0

I don't understand SaleID in the Customers table. I would split out the sale as you may end up with more than one sale per customer. And remember some day you may need to scale up or improve on it. So plan ahead. CustomerID should be in one table, customer info in another (what if they move how will you track which address they had? Be sure to include a date field for address or a field to indicate current address. But date would let you track by date and sale). Will an Item never go on sale? How will you track the difference? What if I paid full price and it goes on sale and I want to return it. There are issues you need to discuss with the person who will be using it.

  • 1. If I move CustomerID into a separate table, what will I store in that table and will I have the composite key in that table or in the one with the customer detail? 2. The SaleID field in the Customers table is to link it to the sale that was made. It acts as a foreign key. Not sure if that is the correct way to do it though. That's one of my main issues, I'm not sure how to design the database properly, what data is supposed to go where and what the primary/foreign keys should be. I'm fairly new to database creation. In the past I've only done basic SQL querying in Access. – badProgrammer Dec 27 '17 at 22:32
  • Customer ID would be customer ID and their name. Tie that to a second table with customer address, phone etal. That way if it changes you can keep the old info in case there is a question about a sale delivery you can say it was made on X date and delivered to Y address. Sale ID should be part of a standalone table as you can have more than one sale to the same customer. So putting sale in its own table, with a link to the Customer ID will let you have multiple sales to a single customer without duplicating their personal info. – DCook Dec 28 '17 at 13:28
0

There is common design that can help you:


Inventory

Items: id, typeID, name, code, price

ItemTypes: id, title, description

CarpetsDetails: id, itemID, code, name, etc... (relation one to one with Items)

CRM:

Customers: id, lastName, phone, etc...

Orders: id, customerID, orderDate, etc..

OrdersDetails: id, orderID, itemId, price (in that field "price" you should write price with which item has been being sold, because price can be changed over time)


For studying on examples you should download database sample AdventureWorks from Microsoft

Actually, if you have experience in Java another option for you might be using any ORM (but this question more suit for stackoverflow)

  • Wow, this really simplifies things for me. I like how you organised the tables. How would I go about using an ORM (which I think is Object-relational mapping)? – badProgrammer Dec 27 '17 at 23:03
  • Yes. ORM it is object-relational mapping. With using it you don't have to have a lot skill in sql querying either in database design. I think it principe sound like "Code first" – brz Dec 27 '17 at 23:30
  • Would I not need to add the field typeID into the CarpetsDetails table? And then create another table OtherItems as well and have that also link to the ItemTypes table and from there the CarpetsDetails and OtherItems would link to the Items table? Because I'm not sure why you made a one-to-one relation from CarpetDetails to Items and not to ItemTypes instead. Could you please explain that further? – badProgrammer Dec 28 '17 at 10:36
  • I have made a great update to the initial design, could you have a look and tell me if it is good or not please? – badProgrammer Dec 28 '17 at 11:47
  • 1. Do not add typeID in CarpetsDetails, it's already done in Product (denormalization) 2. OtherProducts - not a good idea. Common data will be strored in Product and non-common data in specified tables, like Desks, Vases etc... 3. Relation one-to-one is in order of normalization. CarpetLength is connected only with Carpets, so it must be stored in another table, or VasesColor (in possible future) connected only with Vases, so you can add table VasesProduct with one-to-one relation to Products – brz Dec 28 '17 at 12:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.