[BiffBaffBoff]

Hey guys,

First time poster here and I apologise if its in the wrong forum, I dont know if this should be in the database or the Java forum. Basically I need help with my database structure and queries if possible. I have a database structure set-up as following with 3 tables - items, orders and an intersection table called OrderItem:

Items
-----
ID - PK - AI
Name - VARCHAR - 50
Value - DECIMAL - 10,2
Weight - INT - 11
Size - INT - 11

Orders
------
ID - PK - AI
TotalPrice - DECIMAL - 10,2
CanTotalInserted - INT - 11
BottleTotalInserted - INT - 11
CrateTotalInserted - INT - 11
DATE - DATETIME

OrderItem
---------
ID - PK - AI
OrderID - INT - 11
ItemID - INT - 11
Quantity - INT - 11
Value - DECIMAL - 10,2

The items table contains 3 items - bottle, crate and can which each have their own weight, value and size values. I have a Java application where a user is able to run a method called addItem which accepts 2 parameters - itemID and number where itemID is set to either 1, 2 or 3 (Can, Crate, Bottle) and number is the quantity added to the order. Im basically having trouble with the query of a) Creating an order, Adding items to an order and c) Closing that order ready for a new order. I've tried several queries but have come to no success. I think I'm close to solving it with - INSERT INTO OrderItem(OrderID, ItemID, Quantity, Value) VALUES(1, itemID, number, value) - For the moment the orderID is hardcoded as im not sure how I create a new order at the same time and value is created through another query but that one works fine as it just selects Value from the Items table and multiplies that by the number parameter for the method.

In my OrderItem table I would like to see rows of something like this:

ID|OrderID|ItemID|Quantity|Value
--------------------------------
1 1 1 3 12
2 1 2 4 100
3 1 3 2 30
4 2 2 1 25
5 2 3 1 15
6 3 1 1 4
7 4 2 3 75

...and so forth.


Thanks in advance for any help provided, if you need me to clarify on any points let me know.

[baavgai]

You need some FK in there, e.g. OrderItem(OrderID) references Order(OrderID).

I usually prefer to make the PK name play off the table and avoid plural in names, so:

Item: ItemId(PK), Name, Value, Weight, Size

Order: OrderId(PK), OrderDate -- the rest of the fields can be derived

OrderItem: OrderItemId(PK), OrderId(FK), ItemId(FK), Quantity -- I question Value, shouldn't that be in Item?

So, you create an Order and what to add items to it? This is SQL specific, I'm afraid. You need to figure out the auto generated ID that was assigned. You can just add to the last order entered, but that's very bad form; we assume more than one person is using a database at a time.

In something like MSSQL, it might look like:

declare @OrderId int
insert into Order(OrderDate) values (GetDate())
set @OrderId = SCOPE_IDENTITY()

insert into OrderItem(OrderId, ItemId, Quantity)
	values (@OrderId, 3, 42)
insert into OrderItem(OrderId, ItemId, Quantity)
	values (@OrderId, 1, 69)

This post has been edited by baavgai: 30 March 2011 - 05:03 AM

[BiffBaffBoff]

Hi there,

Thanks for the reply, I am using a mySQL database with phpmyadmin so I dont think a FK can be set? Im kinda new to this so not sure. I guess you're right about the value, it can be derived by quantity * number inserted. Maybe I should just have a Total column in the order table that adds it all up?

[baavgai]

You should be able to set up foreign key relationships in phpmyadmin. It's not a big deal for inital design, but it does keep you honest.

Keep in mind, tables store data. Their goal is to never store the same data point twice. If you can figure out a total from the data; don't store it. That said, just data can be pretty hard to read. That's why views exist. A view is just a select statement stored in a database.


For OrderItem, a view might look like:

select a.OrderId, a.OrderItemId, 
		b.Name as ItemName, b.Value as ItemValue,
		a.Quantity, 
		b.Value * a.Quantity as LineTotal
	from OrderItem a
		inner join Item b on a.ItemId=b.ItemId

For a rolled up total, you can just group it:

select a.OrderId, count(*) as TotalItems, sum(b.Value * a.Quantity) as TotalAmount
	from OrderItem a
		inner join Item b on a.ItemId=b.ItemId
	group by a.OrderId

[BiffBaffBoff]

Hi again,

I'd just like to say thanks a lot for your help, you helped me solve my problem although I adopted a different approach as I was using the queries within a Java application. Your last query especially with the total really was useful!