SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 checking available product before letting hire
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

happynnc
Starting Member

7 Posts

Posted - 08/29/2012 :  02:47:56  Show Profile  Reply with Quote
Hi everyone,

I get stuck in design DB in this case:

Before I want to give my products for my customer. I need to check that products is available in stock or not. But i don't know how to start it. Could you give me some ideas please?

Thanks a lot

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 08/29/2012 :  05:04:51  Show Profile  Visit jackv's Homepage  Reply with Quote
Can you post some more details, do you have any application already set up or are you in the design phase?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

happynnc
Starting Member

7 Posts

Posted - 08/29/2012 :  11:03:49  Show Profile  Reply with Quote
Dear Jackv,

I am just in design step. I have a products for hire. At first, I have to check that product is available in stock or not (may be my product is broken or on hire). Then, if products are available, I will let customer to hire. If the product that customer hired get a problem, I have to change another to customer then mark that product is broken - need to be fixed. If there are not any product in my stock, I have to pay back customer money.

I have design the following tables:

Product (Product ID - PK, Product Type, Product Name, Product Price, ...)
Customer (Customer ID - PK, Customer Name, ...)
Hiring (Hiring ID - PK, Hiring Date, Customer ID, VAT, Total Price) <Hiring Date = Date that print invoice>
Hiring Product (Hiring ID, Product ID, Quantity, Start Date, End Date) <Start Date, End Date = Date that user hire and return my product)

In this case, I just know how many product I give for hiring. How can I manege how many products are already broken and how many broken products that are returned by my customers?
Go to Top of Page

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 08/29/2012 :  11:13:50  Show Profile  Reply with Quote
product that are broken: I think you will need a column called status which will hold values like; "Broken", "Hired", "repaired", returned...etc.

This will allow you to run counts on that column and group by customer or product.




--------------------------
Joins are what RDBMS's do for a living
Go to Top of Page

happynnc
Starting Member

7 Posts

Posted - 08/29/2012 :  11:20:42  Show Profile  Reply with Quote
That mean I create a column in Product table:
Product (Product ID - PK, Product Type, Product Name, Product Price, Product Status) If the product broken or return by user, I will update my product ID record again?
Go to Top of Page

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 08/29/2012 :  11:28:16  Show Profile  Reply with Quote
I'm not how this application will update your databases. however; generally speaking that's one way to server your need. I would advice you also to have a History table that records the IN/Out of your product in case you need to go back and check on the history of a specific product ID, you will be adding to it with a timestamp_ID.

Yes, what you have said is correct.

--------------------------
Joins are what RDBMS's do for a living
Go to Top of Page

happynnc
Starting Member

7 Posts

Posted - 08/29/2012 :  11:48:17  Show Profile  Reply with Quote
For example, customer hire 3 same products (like: 3 Wooden Handle) and he return me 2 broken tools. I just have only 1 to replace to him. And I have to refund hiring fee for 1 tool. So,

In Product table: I mark Product No1 status = broken, Product N02 = broken.
Then, I create a table call Product Log (Product Log ID - PK, Product ID - FK, Resolved Date, Resolved Type = Replace|Refund)

Is it ok, xhostx?
Go to Top of Page

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 08/29/2012 :  12:19:29  Show Profile  Reply with Quote
it is okay to do that.

It is also up to you how the business runs, would you repair those products and put them into circulation, or once they are broken, that's their end of life?

you always. manage it the way your business operates.

--------------------------
Joins are what RDBMS's do for a living
Go to Top of Page

happynnc
Starting Member

7 Posts

Posted - 08/29/2012 :  12:44:34  Show Profile  Reply with Quote
Thank xhostx for helping me. I want to repair broken one :)
Go to Top of Page

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 08/29/2012 :  14:26:11  Show Profile  Reply with Quote
you are welcome

--------------------------
Joins are what RDBMS's do for a living
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000