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
 Problem with database building
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

davidoff
Starting Member

7 Posts

Posted - 04/09/2013 :  18:12:39  Show Profile  Reply with Quote
I've come to a problem with which I would like to ask you for some directions. I have database as in image below.



1 In my application I have products predefined, so they automaticaly popup in textbox, depends on first letter. My problem is that every product available in multiple colors. And i dont know how to make a proper table. Should i make one table only with colors?

2 Let's say Customer orders 5 monitors, and 4 keyboards that are all on same Order. Where should I have quantity? In my OrderProduct table?

3 What if customer want a custom sticker or his logo printed on item selected. If I select print on item or sticker in my application, where in database should this be stored? Also on OrderProduct?

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/10/2013 :  01:00:12  Show Profile  Reply with Quote
1. for that you can follow one of below approaches
a. add a color field to Product table and include the value. But this has disadvantage of repeating entire product info if you've more than one color available for same product
b. Add two tables Color(ColorID,ColorValue) and ProductColor(ProductID,ColorID). This will have flexibility of adding as much color value as you want and also a product can be associated to more than one color by adding a record in ProductColor table which will have fks to product and color tables. This is my preferred method

2. You need to have a table OrderItems (OrderItemID,OrderID,ItemName,Qty,Price) and add the items under same order here. So your example will have two records in OrderItems table with same OrderID one for monitor with quantity of 5 and other for keyborad with Qty of 4 you may also include itemprice field to indicate price of individual items if you want.

3.You can store the images inside your fileserver and add a field ImagePath under your customer table to store the image path. Also add a bit field inside customer called PrintOnItem inside table. for those customers who want logo printed set it to 1. Then in application code check for this bit field and if its 1 retrieve image from server and display it on the page.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 04/10/2013 01:01:07
Go to Top of Page

davidoff
Starting Member

7 Posts

Posted - 04/10/2013 :  01:47:31  Show Profile  Reply with Quote
Thank you for your post. Customers don't have direct contact with my application. So under point 3, I'm just choosing what to do on items that are on some order. As I have understood from your post I could do this in the same way as you mentioned for colors.

In a short I'm making this application to more easily manage orders.

Procedure is: Customer orders few items and says he would like to have his ordered products printed with his logo. I get his order on paper and then I'll putt that order into my program, which will show status of items on that order.

(Lets say Customer orders 5 white and 3 black tshirts. On white shirts comes print and on black comes flexprint. On my order will then I have


ProductName  ProductNr  ProductColor  Quantity  ProductPrintColor  Option          Notes      Status
123             Tshirt     White         5         Green              ClassicPrint    BlaBla     NotFinished(0)
123             Tshirt     Black         3         Yellow             FlexPrint       BlaBla     Finished(1)


And when I open my order in new datagridview I can change status of that item. When all items are finished then I will like to mark order as finished.

Application is simple. I have to insert order. Then I have 2 datagridviews. On first are all orders(Customer, OrderStatus, OrderCompletionDate). When I double click on one of orders, that order opens in new datagridview then I can see complete order and on that datagridview I'm changing ProductStatus.

Edited by - davidoff on 04/10/2013 01:50:13
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/10/2013 :  01:53:53  Show Profile  Reply with Quote
I dont prefer putting all the attributes like color print etc in same table as this will cause the core attributes of product to be repeated for each of other values. So you will end up having lots of records for same product in this table. I would have added all the extended attributes to different table and then linked that to product via a bridge table as I showed you above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

davidoff
Starting Member

7 Posts

Posted - 04/10/2013 :  02:52:38  Show Profile  Reply with Quote
Sry if I did mislead you now. The result above is from 3 tables combined. I've write down example just to ask you where on your opinion should I put "option, printcolor" into my database. I also have, as you have seen, "notes" where did I think to put instructions like where on shirt comes print(on front or back of it, etc.)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/10/2013 :  03:07:43  Show Profile  Reply with Quote
yep..you need to put them as fields inside table in your database if you've plans of any data analysis using them like listing colors of product etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

davidoff
Starting Member

7 Posts

Posted - 04/10/2013 :  04:06:21  Show Profile  Reply with Quote
I've now created new ER diagram. I will post image here. If I have status on my item(finished or not), and notes how to complete every order in which table should I put them? In OrderProducts? If yes, why so?


Edited by - davidoff on 04/10/2013 04:08:51
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/10/2013 :  04:33:30  Show Profile  Reply with Quote
STatus of item should be on OrderProduct and notes should be on Orders or OrderProduct depending on whether it describes entire order or an item within

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

davidoff
Starting Member

7 Posts

Posted - 04/10/2013 :  05:01:17  Show Profile  Reply with Quote
Thank you sir for helping me out on this matter. =)

Yes notes are different for every item. Or better every item has his own notes so I think that it goes in OrderProduct. Is this ok?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/10/2013 :  05:07:27  Show Profile  Reply with Quote
yep...Because thats only place where you can associate a value against an item

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.06 seconds. Powered By: Snitz Forums 2000