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

Posted on 2011-03-03
Medium Priority
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.
Question by:philip38
  • 6
  • 4
  • 2
LVL 22

Expert Comment

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. ;-)

Author Comment

ID: 35032212
Oh, Thank You.  Here attached are word files. At the office I only had the Open Office Draw/Suite programs.
LVL 22

Expert Comment

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.
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.


Author Comment

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.
LVL 50

Expert Comment

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 ?

Author Comment

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:


Author Comment

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.
LVL 22

Assisted Solution

8080_Diver earned 400 total points
ID: 35039222

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


Author Comment

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.
LVL 50

Accepted Solution

Lowfatspread earned 1600 total points
ID: 35040282

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)


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....


Author Closing Comment

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

Expert Comment

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.

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

623 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