Need Help Tring to make a database work properly

[jones77622]

I have a database of 5 tables. 1st table is my customer table. 2nd is my crust table. 3rd is my toppings table. 4th is my sides table. 5th is my order table. My crust, toppings and sides table all have a price column and I am trying to get them to add together on my order table total cost column. What function would be the best to use to accomplish getting the crust price toppings price and sides price to add up on the order table Total cost column?

Here is my table schema:

Crust TableCrustID Primary KeyCrustnameCrustsizeCrustprice moneyToppings TableToppingID Primary KeyToppingNameToppingPrice moneySides TableSideID Primary KeySideNameSidePortionSidePrice moneyCustomer TableCustomerID Primary KeyFnameLnameStreetCityStateZipPhonenumberemailaddress Order TableOrderId Primary Key INTCustomer Foreign Key INTCrust Foreign Key INTToppings Foreign Key INT

Sides foreign key int

TotalCost money

Edited November 18, 2013 by jones77622

[justsomeguy]

Can a single order have multiple toppings and sides? It looks like the order table has one column for toppings, and no column for sides.

[jones77622]

Sorry about that i thought i put all of the order table in.

 

I am starting with only one topping and one side but was thinking about adding more after i figure out how to properly add up cost.

Edited November 18, 2013 by jones77622

[justsomeguy]

Well, the technique is going to be different with multiple toppings and sizes, the database structure for that would be different. Ideally you would store those using a third lookup table to link orders with toppings, and orders with sides. That would be a many-to-many relationship instead of the one-to-many relationship you have now.

 

With the current structure you'll need to join the tables and then add up the appropriate columns. Something like this:

SELECT o.OrderID, c.Crustname, t.ToppingName, s.SideName, c.Crustprice + t.ToppingPrice + s.SidePrice as OrderPrice FROM order AS o LEFT JOIN crusts AS c ON o.crust=c.crustID LEFT JOIN toppings AS t ON o.toppings=t.toppingID LEFT JOIN sides AS s ON o.sides=s.sideID

[jones77622]

How do i get it to show up on the order table when i view it in select top 1000 rows on the order table ???

 

It shows the proper price in the query but it does not show it on the ordertable query select top 1000 rows

 

This is the query that worked so far

 

SELECT OrderID, Crust, Toppings, Sides, Crustprice + ToppingPrice + SidePrice as OrderPrice FROM order1 AS o LEFT JOIN Crusttable AS c ON crust=crustID LEFT JOIN ToppingsTable AS t ON Toppings=toppingID LEFT JOIN sidestable AS s ON sides=sideID

Edited November 19, 2013 by jones77622

[justsomeguy]

If you're talking about the TotalCost field on the order table, then the correct way to do that would be to calculate the total cost at the time you create the order and set the value for that field. You could use an update query to update all of the total cost fields for the whole table, but it wouldn't be a good design for the application if you need to update that table periodically in order to reflect the correct total cost for all orders. The total cost should be calculated when the order is created and you should add the record to the order table with the total cost value already filled in.

[jones77622]

any ideas???

[justsomeguy]

Any ideas for what? How do you add orders to that table now?

[jones77622]

through the edit top 200 rows

[jones77622]

i am trying to get it to calculate total cost automatically on the order table as i am entering the product in the order table.

[jones77622]

one of my friends said i should try a multistatement table-valued functions but am not sure how to go about that.

[justsomeguy]

If you are editing the database manually then your options are limited. Most of the time databases are used they are interacted with by another programming language, where you would use that language to calculate the cost before you add the order record to the table. If you're adding records manually then one option would be to create a trigger for each time you add a new record to run the queries necessary to figure out the total cost for that record. Which database system are you using?

[jones77622]

SQL management studio i believe it is SQL server 2008 r2

[jones77622]

i have been looking for a few weeks on how to do this i am working on this for a project for my SQL class as extra credit to boost my grade.

Edited November 20, 2013 by jones77622

[justsomeguy]

This is the section on how to create triggers:

 

http://technet.microsoft.com/en-us/library/ms189799.aspx

 

You'll probably want to research triggers in general. It sounds like you need to create an insert trigger and an update trigger for the orders table, to run each time a record gets added or updated to calculate the cost.

[jones77622]

what about using a multistatement table-valued function ??

[jones77622]

what would the correct syntax be for an insert into statement??

[jones77622]

using the same idea

[justsomeguy]

A function like that isn't going to update the data already in the table. If you want to make sure the table always contains accurate values for the cost then use a trigger. You can also use a view, but again a view is not going to change the data in the table.

 

There are several examples of insert statements here:

 

http://technet.microsoft.com/en-us/library/dd776381(v=sql.105).aspx

[jones77622]

well i have a table that doesn't have anything in it yet then i have a table that i am using for testing purposes

[jones77622]

can i use the same idea in creating a function??

[justsomeguy]

I don't know what you're asking. Which idea? You can use any idea to create any function, I suppose, as long as you design and implement it correctly. I don't know why you would want to use a function for this instead of a trigger, though. When do you execute the function?

[jones77622]

i would like to execute this function every time i enter a new row in the order table. my guess is that i need to create a function then create a trigger to activate that function every time i enter a new row into the order table. am i correct?

[justsomeguy]

That's one way to do it. The trigger could also contain all of the code you need, as far as I know.

[jones77622]

i am having trouble making a trigger for the order table can you help me with the syntax please?