MySQL and 'this' kind of table

[RyanY]

need help with this.... refer to this thread. I thank you.
http://www.vbforums.com/showthread.php?s=&threadid=140193

[Vincent Puglia]

Hi,

Although it's been awhile since I've played with SQL......
Every SQL database consists of tables (that's why TABLE), consisting of rows and columns. In your case you can define the columns as FruitName and Size1 through Size4 and the rows as follows:
Grapes, $1, $2, etc
Apples, $2, $4, etc

in which case,

select size1 from tablename where FruitName = 'Grapes'
would get you $1

Vinny

[RyanY]

Hmmm.... I've thought about it this way but.
What if the two input values was the name of fruit and the price?
ie, Input > Grapes , $5 Result > Size of fruit = 3?

Select * from table where fruit_name = "grapes" and ??
This wouldnt work i think ....

Thanks though

[Vincent Puglia]

Hi,

If you are allowing selection of all 3 fields, then your only choice is define the following table: fruit, cost, size. which means you will either have to make the record number the primary key or a combination of the three (fruit+cost+size) so that you will have a unique key.

Is the data really what you stated? Or are there other fields, dependencies?

Vinny

[scoutt]

I thought about this and the way you want to do it will become really confusing and not worth the effort. you will have to set a base, like size is always given or price is always giving. you can not have the user select 2 different things. depending on what the user selected would depend on the structor of the database. I can get it one way but as you have the users change the search critiera it wouldn't work from that way.

did all that make since???

[Cudabean]

scoutt -

I think it would make sense to structure it as Vinny said:
(fruit, cost, size). You just have to be thorough on defining sizes.

I think cost is really cost per pound and size represents all the sizes that that particular fruit is available in (stores usually charge different prices based on unit size. This is especially true with shrimp where the Jumbo has a premium price)

cudabean

[Vincent Puglia]

Hi,

going back to the original suggestion I made. If you are doing this with php or some other 3rd gen language, you can

get the resulting table from the query:

Select size1, size2,... from table where fruit_name = "grapes"

and then using a loop, step through the various values until you find the $5 and look up the fieldName from there -- if cell3 then size3

Vinny

[Cudabean]

Vinny,

Suppose you're program is successfully running along then on week 4 sombody says: OMG! there are now 100 sizes for peas! You'd have to restructure your table to ensure that each fruit now had Size1 - Size100. Plus you'd have wasted space in your table because for all the other fruits, most of the Size5-100 would be null, and all because just one item, peas, changed drastically.

On the other hand, declaring a table (fruit, size, cost) allows you to define only what you need to and nothing more. Then instead of freaking out you merely define all the silly new pea sizes and you're back in business. For example you could then do:
Select cost from size_cost_table where fruit = 'peas' and size = 87;

cudabean

[Vincent Puglia]

Hi cuda,

I agree (and please note this is not 'my' table we are discussing, simply two examples of what can be done and how) that the 3-column table would be a way to go, especially if there are umpteen values to play with and more than 1 way to query.

That being said:

Dealing with 1 table is really not what Codd & Date (the developers) defined SQL for. They created it to deal with multiple unique tables that could be inter-related and manipulated quickly, efficiently and easily.

When you have one table, you have a simple spreadsheet, and that is why I offered the first solution -- and with the presumption that the sizes were fixed to some small amount, why I suggested looping through to find the dollar amount

Also, we haven't really discussed indices, primary keys, etc. Finally, I don't really know what and how many fields RyanY really needs in the table and if there are other tables in the db. For example are all the fruits from the same supplier? Are they going to the same distributor? etc. etc.

Which solution is best cannot really be determined until, we have more facts (which don't seem to be forthcoming :)

Vinny

[scoutt]

Originally posted by Cudabean
scoutt -

I think it would make sense to structure it as Vinny said:
(fruit, cost, size). You just have to be thorough on defining sizes.

I think cost is really cost per pound and size represents all the sizes that that particular fruit is available in (stores usually charge different prices based on unit size. This is especially true with shrimp where the Jumbo has a premium price)

cudabean
then you would have tables linked to together, but by how??? doesn't one of the columns have to be linked to another column in the other table?

I do understand what you are saying, but just from what he wanted it is like stepping off the deep end.

[RyanY]

Originally posted by Vincent Puglia
Hi,

If you are allowing selection of all 3 fields, then your only choice is define the following table: fruit, cost, size. which means you will either have to make the record number the primary key or a combination of the three (fruit+cost+size) so that you will have a unique key.

Is the data really what you stated? Or are there other fields, dependencies?

Vinny

Yes, I may have to go with this method as i cant seem to find a better one yet... maybe Ill look into combining tables....

This data was just an example to show the kind of table i want.
There isnt really any other dependencies... most likely its an independent table all on its own.

[RyanY]

Originally posted by Vincent Puglia
Hi,

going back to the original suggestion I made. If you are doing this with php or some other 3rd gen language, you can

get the resulting table from the query:

Select size1, size2,... from table where fruit_name = "grapes"

and then using a loop, step through the various values until you find the $5 and look up the fieldName from there -- if cell3 then size3

Vinny

Vinny,

Yup will be using php/mysql... this looping method seems like another option. I'll make a note of it somewhere. thanks :)

[RyanY]

Sorry about the slow reply.
I'm in Singapore here. Most probably I'm asleep when you posted
:) heh

Also thank you everyone for your input.
Its been a great help. Keep em coming! ;)