February 11th, 2013, 07:16 PM
-
Trouble designing table structure
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?
February 12th, 2013, 01:52 AM
-
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
February 13th, 2013, 05:46 PM
-
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
February 13th, 2013, 09:19 PM
-
thanks, that helped
yes, your items table is not related to customers table, and purchases table is related to neither
does that make sense?
February 13th, 2013, 10:01 PM
-
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!
February 14th, 2013, 05:39 AM
-
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
February 14th, 2013, 06:39 PM
-
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.
February 20th, 2013, 05:14 PM
-
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?
February 20th, 2013, 05:46 PM
-
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