| Author |
Topic |
|
happynnc
Starting Member
7 Posts |
 Posted - 2012-08-29 : 02:47:56
|
| 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
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-08-29 : 05:04:51
|
| 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 |
 |
|
|
happynnc
Starting Member
7 Posts |
Posted - 2012-08-29 : 11:03:49
|
| 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? |
|
|
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2012-08-29 : 11:13:50
|
| 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 |
|
|
|
happynnc
Starting Member
7 Posts |
Posted - 2012-08-29 : 11:20:42
|
| 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? |
|
|
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2012-08-29 : 11:28:16
|
| 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 |
|
|
|
happynnc
Starting Member
7 Posts |
Posted - 2012-08-29 : 11:48:17
|
| 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? |
|
|
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2012-08-29 : 12:19:29
|
| 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 |
|
|
|
happynnc
Starting Member
7 Posts |
Posted - 2012-08-29 : 12:44:34
|
| Thank xhostx for helping me. I want to repair broken one :) |
|
|
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2012-08-29 : 14:26:11
|
| you are welcome--------------------------Joins are what RDBMS's do for a living |
|
|
|
|
checking available product before letting hire