[MuyJingo]

Hi guys, I hope this is in the right forum.

I've been making a very simple web application, but have run into trouble.

Essentially, I want to track customer purchases, and aggregate purchases together for a customer, but still have each buying "session" able to be looked at separately.

So, I have a customers table, with fields like name, id number and the date, and number of days they are buying from us.

Then I have a purchases table, which has an id field corresponding to the customer id, then for each time that can be bought, a numItem field showing how many of that item they bought.

Finally I have an items table which has every type of item, an id number and the cost.

I have been told that this is not a good structure, although I am not clear on why.

I want to show, on one web page, all of a customers purchases during their "session" with the amount paid so far and any amount owing.

Can you guys give some suggestions on a table structure more suitable to what I am trying to accomplish?

[r937]

Originally Posted by MuyJingo

Can you guys give some suggestions on a table structure more suitable to what I am trying to accomplish?

sure

first thing you need to do is a SHOW CREATE TABLE for each table, so that we can see the current structure

[MuyJingo]

Originally Posted by r937

sure

first thing you need to do is a SHOW CREATE TABLE for each table, so that we can see the current structure

Sure, Thanks

CREATE TABLE `items` ( `itemID` int(11) NOT NULL AUTO_INCREMENT, `itemName` varchar(20) NOT NULL, `itemCost` int(11) NOT NULL, PRIMARY KEY (`itemID`)) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1

CREATE TABLE `customers` ( `id` int(11) NOT NULL AUTO_INCREMENT, `firstName` varchar(225) NOT NULL, `lastName` varchar(225) NOT NULL, `country` varchar(100) NOT NULL, `modelType` char(5) NOT NULL, `modelNumber` varchar(150) NOT NULL, `checkout` date NOT NULL, `days` varchar(150) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

CREATE TABLE `purchases` ( `id` int(11) NOT NULL, `workCost` int(11) NOT NULL, `polishCost` int(11) NOT NULL, `numScrews` int(11) NOT NULL, `numNails` int(11) NOT NULL, `numSlats` int(11) NOT NULL, `numBrushes` int(11) NOT NULL, `numStickers` int(11) NOT NULL, `numDecals` int(11) NOT NULL, `numAccessories` int(11) NOT NULL, UNIQUE KEY `id` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1

[r937]

thanks, that helped

yes, your items table is not related to customers table, and purchases table is related to neither

does that make sense?

[MuyJingo]

Originally Posted by r937

thanks, that helped

yes, your items table is not related to customers table, and purchases table is related to neither

does that make sense?

Somewhat, although I'm pretty rusty with that stuff.

I had thought that if 2 tables had an id field in common, that is all that was needed to do joins or unions, or to show a query combing fields from both tables.

I was told from the get go that my structure didn't make sense, and I wasn't too sure why.

I had thought my current structure allowed for the types of queries I want to do, such as:

• Show all the purchases a customer has made, how much money has been paid and how much is owing
• Show for each customer each "session" of renting out equipment and making purchases
• Seeing how many of each product they purchased for a given session

For example, would my current structure allow for a query selecting all purchases from a customer, calculating the cost owed from the items table, and subtracting and cash paid?

Thanks for your assistance by the way!

[r937]

Originally Posted by MuyJingo

For example, would my current structure allow for a query selecting all purchases from a customer, calculating the cost owed from the items table, and subtracting and cash paid?

no

grab a row of purchases data from the purchases table... which customer is it for? you can't tell, because there is no customer_id in the purchases table to relate the two tables

[MuyJingo]

Originally Posted by r937

no

grab a row of purchases data from the purchases table... which customer is it for? you can't tell, because there is no customer_id in the purchases table to relate the two tables

That's what the id fields is, I don't have an id for each purchase because I didn't think I needed it.

[MuyJingo]

Originally Posted by r937

no

grab a row of purchases data from the purchases table... which customer is it for? you can't tell, because there is no customer_id in the purchases table to relate the two tables

I'm still unsure what I should do now....can you give an example table structure that would work better for what I want...mainly so that I can perform calculations in the query?

[Aurum84]

Look up on "Foreign Key", that is the technique you have to master for this (and any) relational problem.

The namification of an id is, imo, irrelevant. It is certainly not true that when two tables have a column name in common, that they are related. These columns will be tested on equality during for example a JOIN.
As an example: add a column customerID to your purchases table. The values in this column will be equal to for example the values of the column ID of the customer table