#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    5
    Rep Power
    0

    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?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    5
    Rep Power
    0
    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
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    thanks, that helped

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

    does that make sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    5
    Rep Power
    0
    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!
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    5
    Rep Power
    0
    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.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    5
    Rep Power
    0
    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?
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    74
    Rep Power
    19
    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

IMN logo majestic logo threadwatch logo seochat tools logo