Jump to content

Need Help Tring to make a database work properly


jones77622
 Share

Recommended Posts

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 by jones77622
Link to comment
Share on other sites

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
  • Like 1
Link to comment
Share on other sites

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 by jones77622
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...