| Author |
Topic |
|
davidoff
Starting Member
7 Posts |
 Posted - 2013-04-09 : 18:12:39
|
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?![]() ![]() ![]() ![]() ![]() ![]() ![]() [url][/url]![]() [url][/url] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-10 : 01:00:12
|
| 1. for that you can follow one of below approachesa. 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 productb. 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 method2. 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
davidoff
Starting Member
7 Posts |
Posted - 2013-04-10 : 01:47:31
|
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 haveProductName ProductNr ProductColor Quantity ProductPrintColor Option Notes Status123 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-10 : 01:53:53
|
| 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
davidoff
Starting Member
7 Posts |
Posted - 2013-04-10 : 02:52:38
|
| 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.) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-10 : 03:07:43
|
| 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
davidoff
Starting Member
7 Posts |
Posted - 2013-04-10 : 04:06:21
|
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? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-10 : 04:33:30
|
| 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
davidoff
Starting Member
7 Posts |
Posted - 2013-04-10 : 05:01:17
|
| 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? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-10 : 05:07:27
|
| yep...Because thats only place where you can associate a value against an item------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
Problem with database building