SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    May 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database design help

    Hello all,

    I've just started reading up on using mysql and php so am working through a few tutorials with little knowledge at the moment. I have also ordered simply sql to help along the way.

    My question is, how would I create a usable table like the one below in sql? This is just a small excerpt as the width extends to 'L' and thickness to 6.0cm.

    Code:
                    Tube diameter
    	a	b	c	d	e
    1.0cm	3.0	3.1	3.2	3.3	3.4
    2.0cm	3.6	3.7	3.8	3.9	4.0
    A-E is the tubing diameter.
    1cm-2cm is the thickness of the tubing.
    Other values are the weight of the tubing.
    The tubing will also come in different metals (i.e brass, copper).

    I would like to create a page where a user would be able to select the tube material, diameter and thickness from drop down lists. The weight is then multiplied by a price I have which is then displayed on the page for the end user. So I'm guessing there would be four variables which change.

    I'm uncertain (from what I've read so far) as to the best layout to use for each table, or if I could combine all the information into one large table.

    Help would be much appreciated at this early stage. Thanks.

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    CREATE TABLE tubes (diameter char(1), thickness double, weight double, PRIMARY KEY (diameter, thickness));

    INSERT INTO tubes (diameter, thickness, weight) VALUES ('a', 1.0, 3.0);
    INSERT INTO tubes (diameter, thickness, weight) VALUES ('b', 1.0, 3.1);

    ...etc

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what dan said, except stay away from DOUBLE and use DECIMAL(m,n) instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    May 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you both for your input! It's now clear to me how to insert these values. Just have to figure out how to automate the process of selecting the weights from the matrix in excel with the other values.

    Thanks again!

  5. #5
    SitePoint Member
    Join Date
    May 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok. I've managed to get the data from this:

    Code:
             Tube diameter
    	a	b	c	d	e
    1.0cm	3.0	3.1	3.2	3.3	3.4
    2.0cm	3.6	3.7	3.8	3.9	4.0
    to this:

    Code:
    metal 	shape 	dia 	width 	weight
    Cop 	OV 	a 	1.0 	3.0
    Cop 	OV 	a 	2.0 	3.6
    Cop 	OV 	b 	1.0 	3.1
    Cop 	OV 	b 	2.0 	3.7
    I'm now also able to display all this information in a table. I placed the metal and shape fields as primary keys also.

    What I'm trying to do is have a drop down box for each field where the end user can select what they require, and then the weight show up as the result in the last box.

    I've been doing a bit of reading but seem to be getting more confused on the best way to approach this.

    I just need a little push in the right direction, especially with the technical terms.

    Thanks

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by boneyo View Post
    I placed the metal and shape fields as primary keys also.
    then three of those four rows would have been rejetces, as the pair of values Cop/OV would be accepted only once


    Quote Originally Posted by boneyo View Post
    I just need a little push in the right direction, especially with the technical terms.
    i understand that situation well... but are there any other issues at the moment?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    May 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply rudy.

    Quote Originally Posted by r937 View Post
    then three of those four rows would have been rejetces, as the pair of values Cop/OV would be accepted only once


    i understand that situation well... but are there any other issues at the moment?
    I thought that the primary key had to be unique so that it could single out a row. In my case would it be best to auto increment an id to each row and use this as the primary key? (if all four fields cannot be used as I have).

    No other issues at the moment. I'm just reading and trying..reading and failing. hehe

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your primary key should be (metal,shape,dia,width)

    there is no need for an auto_increment here, but if you did use one, you would also want a unique constraint on (metal,shape,dia,width)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    May 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah just realised where I went wrong. I typed in width on my example table when it should be thickness. I do indeed have the primary keys set up as (metal,shape,dia,width/thickness).


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •