Solved

I need a SQL query that adds price and size data to a database

Posted on 2011-03-03
12
797 Views
Last Modified: 2012-06-22
 
I need a query to add price and size data to a table. The name of the table is AttributeDetail.
AttributeDetails-ScreenShot.odg
Attributes-Screenshot.odg
Attributes-Table.odt
0
Comment
Question by:philip38
  • 6
  • 4
  • 2
12 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35031915
I don't seem to have anything that will open the file types that you have attached.  Can you, perhaps, attach Word (or some other standard file type) equivalents?

Does your AttributeDetail table have columns for the price and size data?

I'm not at all sure how this is a difficult task . . . but, then again, I can't see the information you have provided. ;-)
0
 

Author Comment

by:philip38
ID: 35032212
Oh, Thank You.  Here attached are word files. At the office I only had the Open Office Draw/Suite programs.
Data-to-enter-into-table.docx
AttributesTable.docx
AttributeDetail-table.docx
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35032363
Okay, I can see the files now (which really helps ;-).

However, I don't exactly see how you plan to add the price and size information to that table . . . there don't appear to be any columns provided for some of the information.

I am assuming that the first set of valuies ("Size") is associated with an unframed image while the second is associated with framed images.  If that is true, then you are dealing with something like the following:
Image_Size	UnFramed	Framed
8.5x11	$60.00	NULL
11x14	$22.00	$ 95.00
16x20	$40.00	$140.00
18x24	$50.00	$180.00
20x28	$60.00	$225.00
26x36	$60.00	$295.00

Open in new window


Now, I am not at all sure how the colors come into play with the pricing.  And I am not at all sure how you are using the Attribute table.  Is the Attribute table used to indicate what attributes something is supposed to have?  And, then, are you somehow wanting to transfer the size/pricing information into the the AttributeDetail table?

Personally, I would tend to have a ImagePrices table with the Data, shown above, arranged in that manner (with a ImagePriceID column that is an Identity column as the primary key).  Then I would use the ImagePriceID to link from any Inventory/OrderDetail/whatever entries.

The use of the Attributes and AttributeDetail tables looks like a bit of over kill in the area of normalization to me.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:philip38
ID: 35032930
Makes sense to me. I will have to run it by the guy who assigned this to me tomorrow. (Flooded with work). Really appreciate it. Please stand by.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35034345
its difficult to understand what you are trying to do since you haven't given any background on the table relationships
or even the table structures...

i'd say that possibly your tables are wrongly named since it appears that attributes is really a productAttribute table
(similarly with attributedetail?).. are you sharing attributes between products ?  is there another table which details
the cross attribute relationships (e.g. you can't have a black frame above size X? or Pink frames are only in size Y)

are you updating existing rows with price info
or inserting new rows ?

do you have attributes already set up for FRAME ?
0
 

Author Comment

by:philip38
ID: 35038620
Thank you for your reply.
Let me enhance the explanation with another file. I have created a diagram of the key tables showing their relationships.

Also, I want to show you the end result page we are talking about. You can find it on the web here:

http://www.ballparkprints.com/detail.aspx?ID=24
Table-Map.doc
0
 

Author Comment

by:philip38
ID: 35038636
That is to say http://www.ballparkprints.com/detail.aspx?ID=24 is a sample of the page that needs to have the prices and sizes updated.
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 100 total points
ID: 35039222
phillip38,

Okay, in looking at your diagram, I am still trying to figure out where your size and cost figures go.  Are the separate entries in the AttributeDetail table or what?

I still think that the separate ImagePrices table would be the best bet.
Table Name: ImagePrices

Column Name            Datatype
ImagePriceID             INT (Identity)
ProductID                   INT   (FK to Products table)
ImageSizeID              INT   (FK to ImageSizes table)
ImageCost                 Currency

Table Name: ImageSizes
Column Name            Datatype
ImageSizeID               INT (Identity)
ImageSizeDesc         VarChar(50) (Description of ImageSize)

Open in new window




0
 

Author Comment

by:philip38
ID: 35039677
I think that is a viable suggestion, except I want to if possible keep working on the database as it is constructed.
Therefore, I have attached an enhanced bitmap of the database diagram. These include the tables that have the 'price' field.

My objective is to 1. change existing prices on the page and 2. add new size item entries to the page.
DatabaseDiagram.bmp
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 400 total points
ID: 35040282
ok...

i still think mostt of your problem is down to mis nameing of the tables, and i don't like how you've got "price" scattered about ...

but it is fairly simple to update your price

insert into attributedetails (list of table columns)
 values ( the values for each column)

e.g.

insert into attributedetails (productid,attributeid,name,price,....)
 values (12,13,'11x8',49,00,...)

assuming that you have converted 'size' to its attributeid  e.g. 13
(framed is also an attribute in your system and it has names which are a subset of the set of names available to size(?))

but it starts to breakdown with colours... which (i think) is where our confusion comes in ...  you are missing a table which
controls the ability to mix the options...
 size, framed, color of frame, etc   ...

your order table has options for gift wrapping etc , i'm not sure why that is being treated differently from (say) framing...
you also have a saleitem indicator , but again is that at the correct place (could you not just have a sale on "pink" frames?)


 perhaps a better model for you to think of would be a pizza creation/order menu....

each component can be separately charged/changed/customised and the price is determined by having a Basic price (size)
to which the other product customisations then add to (e.g. sweetcorn,pepparoni,anchovies 0.50 each, Tuna,beefm,, 0.75)
then you get the options on crust, base type, how cut, delivery etc....



0
 

Author Closing Comment

by:philip38
ID: 35057564
Thank you both to Lowfatsrpead and to 8080 Diver
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35058652
I have to agree with Lowfatspread that having pricing information in 4 tables is a bit problematic.  I can see having it in the Product Table and in the OrderDetails table (because the price could change after the OrderDetails entry is created) but having it in the other tables looks a whole lot like the design needs more work.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

775 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question