Database design help

Databases
1

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.


                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

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

what dan said, except stay away from DOUBLE and use DECIMAL(m,n) instead

4

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

ok. I’ve managed to get the data from this:

         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:


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

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?

7

Thanks for the reply rudy.

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

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)

9

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).