Help in querying tables

I am in the process of testing a database design, which i believe I
have hit a stumbling block and cannot think of a way around it.

So far I have the following tables:

1) code_tbl {
product_code
product_name
product_desc
box_quantity
unit_price
size_type
}

2) size_tbl {
code
(this holds multiple sizes available for all products)
}

3) colour_tbl {
code
(again this holds all colours available)

4) acc_number_tbl {
account_number
company_name
address1
address2
address3
postcode
contact
telephone
fax
type (customer, supplier)
status (active, inactive)
}

Now this is where i got lost, 90% of the companies will have their
own
pricelists, so i created a new table for each company using their own
account_number as the table name (this seemed to be working) until i
had to query the list of customers who bought a particular product
and
then display these results in a dropdown on the product page.

I managed to create a query which brought back only those who were
suppliers / customers and which were active. But I cannot think how
to
query their own table based on the product code.


I realise I have to rethink the database structure, as this
particular
query seems to be impossible with the current one.


I would be grateful if someone could point me in the right direction
to overcome this issue.

I did get some help from a php group, but they suggested adding more
information than is currently needed (ie all the purchasing side).
This
database is only for training sales staff so does not need the
purchasing side.

I have thought about adding all the product codes in to the
acc_number_tbl but I am unsure how many fields are allowed in each
table. But this would also cause a problem as to were to store
customer
prices per code. I have also thought about adding the account_number
to the code_tbl but this would only allow one customer per product
code.

thanks in advance

Dave

On 24 Jul, 11:32, Dave <david.greenh...@praybourne.co.uk> wrote:
> I am in the process of testing a database design, which i believe I
> have hit a stumbling block and cannot think of a way around it.
>
> So far I have the following tables:
>
> 1) code_tbl {
> product_code
> product_name
> product_desc
> box_quantity
> unit_price
> size_type
>
> }
>
> 2) size_tbl {
> code
> (this holds multiple sizes available for all products)
>
> }
>
> 3) colour_tbl {
> code
> (again this holds all colours available)
>
> 4) acc_number_tbl {
> account_number
> company_name
> address1
> address2
> address3
> postcode
> contact
> telephone
> fax
> type (customer, supplier)
> status (active, inactive)
>
> }
>
> Now this is where i got lost, 90% of the companies will have their
> own
> pricelists, so i created a new table for each company using their own
> account_number as the table name (this seemed to be working) until i
> had to query the list of customers who bought a particular product
> and
> then display these results in a dropdown on the product page.
>
> I managed to create a query which brought back only those who were
> suppliers / customers and which were active. But I cannot think how
> to
> query their own table based on the product code.
>
> I realise I have to rethink the database structure, as this
> particular
> query seems to be impossible with the current one.
>
> I would be grateful if someone could point me in the right direction
> to overcome this issue.
>
> I did get some help from a php group, but they suggested adding more
> information than is currently needed (ie all the purchasing side).
> This
> database is only for training sales staff so does not need the
> purchasing side.
>
> I have thought about adding all the product codes in to the
> acc_number_tbl but I am unsure how many fields are allowed in each
> table. But this would also cause a problem as to were to store
> customer
> prices per code. I have also thought about adding the account_number
> to the code_tbl but this would only allow one customer per product
> code.
>
> thanks in advance
>
> Dave

Hendri has already pointed you in the right direction over on
comp.lang.php. Why are you starting with the same question again!

On 24 Jul, 11:45, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 24 Jul, 11:32, Dave <david.greenh...@praybourne.co.uk> wrote:
>
>
>
>
>
> > I am in the process of testing a database design, which i believe I
> > have hit a stumbling block and cannot think of a way around it.
>
> > So far I have the following tables:
>
> > 1) code_tbl {
> > product_code
> > product_name
> > product_desc
> > box_quantity
> > unit_price
> > size_type
>
> > }
>
> > 2) size_tbl {
> > code
> > (this holds multiple sizes available for all products)
>
> > }
>
> > 3) colour_tbl {
> > code
> > (again this holds all colours available)
>
> > 4) acc_number_tbl {
> > account_number
> > company_name
> > address1
> > address2
> > address3
> > postcode
> > contact
> > telephone
> > fax
> > type (customer, supplier)
> > status (active, inactive)
>
> > }
>
> > Now this is where i got lost, 90% of the companies will have their
> > own
> > pricelists, so i created a new table for each company using their own
> > account_number as the table name (this seemed to be working) until i
> > had to query the list of customers who bought a particular product
> > and
> > then display these results in a dropdown on the product page.
>
> > I managed to create a query which brought back only those who were
> > suppliers / customers and which were active. But I cannot think how
> > to
> > query their own table based on the product code.
>
> > I realise I have to rethink the database structure, as this
> > particular
> > query seems to be impossible with the current one.
>
> > I would be grateful if someone could point me in the right direction
> > to overcome this issue.
>
> > I did get some help from a php group, but they suggested adding more
> > information than is currently needed (ie all the purchasing side).
> > This
> > database is only for training sales staff so does not need the
> > purchasing side.
>
> > I have thought about adding all the product codes in to the
> > acc_number_tbl but I am unsure how many fields are allowed in each
> > table. But this would also cause a problem as to were to store
> > customer
> > prices per code. I have also thought about adding the account_number
> > to the code_tbl but this would only allow one customer per product
> > code.
>
> > thanks in advance
>
> > Dave
>
> Hendri has already pointed you in the right direction over on
> comp.lang.php. Why are you starting with the same question again!- Hide quoted text -
>
> - Show quoted text -

As far as I am aware, Hendri has not.

Yes he helped a lot, but like I stated above, adding the purchasing
side to this database is an unnessasary task. I was hoping someone
here might have a better suggestion to get the information I needed.

And also like Hendri stated, that group is not the place for this
question. So again, maybe someone in the right group would have a
better suggestion.

On Tue, 24 Jul 2007 12:53:05 +0200, Dave
<david.greenhall@praybourne.co.uk> wrote:
>> Hendri has already pointed you in the right direction over on
>> comp.lang.php. Why are you starting with the same question again!
>
> As far as I am aware, Hendri has not.
>
> Yes he helped a lot, but like I stated above, adding the purchasing
> side to this database is an unnessasary task. I was hoping someone
> here might have a better suggestion to get the information I needed.

AFAIK he was right on the money with
<news:13abg20dahpnf52@corp.supernews.com>. What better suggestion do you
want?
--
Rik Wasmus

Dave wrote:
> I am in the process of testing a database design, which i believe I
> have hit a stumbling block and cannot think of a way around it.
>
> So far I have the following tables:
>
> 1) code_tbl {
> product_code
> product_name
> product_desc
> box_quantity
> unit_price
> size_type
> }
>
> 2) size_tbl {
> code
> (this holds multiple sizes available for all products)
> }
>
> 3) colour_tbl {
> code
> (again this holds all colours available)
>
> 4) acc_number_tbl {
> account_number
> company_name
> address1
> address2
> address3
> postcode
> contact
> telephone
> fax
> type (customer, supplier)
> status (active, inactive)
> }
>
> Now this is where i got lost, 90% of the companies will have their
> own
> pricelists, so i created a new table for each company using their own
> account_number as the table name (this seemed to be working) until i
> had to query the list of customers who bought a particular product
> and
> then display these results in a dropdown on the product page.
>
> I managed to create a query which brought back only those who were
> suppliers / customers and which were active. But I cannot think how
> to
> query their own table based on the product code.
>
>
> I realise I have to rethink the database structure, as this
> particular
> query seems to be impossible with the current one.
>

Yes, you don't want multiple tables with basically the same information.

>
> I would be grateful if someone could point me in the right direction
> to overcome this issue.
>
> I did get some help from a php group, but they suggested adding more
> information than is currently needed (ie all the purchasing side).
> This
> database is only for training sales staff so does not need the
> purchasing side.
>

Well, just add the information you need, then. But you still need to
restructure your database.

> I have thought about adding all the product codes in to the
> acc_number_tbl but I am unsure how many fields are allowed in each
> table. But this would also cause a problem as to were to store
> customer
> prices per code. I have also thought about adding the account_number
> to the code_tbl but this would only allow one customer per product
> code.
>

Thousands. But you shouldn't have them all in one row. Rather, have a
table such as:

AccountNumber
ProductCode
Price

> thanks in advance
>
> Dave
>

Also, I recommend you google on "database normalization". The MySQL doc
has a good discussion about it, but there are a lot of others.
Understanding and applying normalization rules helps simplify the design
process.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On 24 Jul, 12:11, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Dave wrote:
> > I am in the process of testing a database design, which i believe I
> > have hit a stumbling block and cannot think of a way around it.
>
> > So far I have the following tables:
>
> > 1) code_tbl {
> > product_code
> > product_name
> > product_desc
> > box_quantity
> > unit_price
> > size_type
> > }
>
> > 2) size_tbl {
> > code
> > (this holds multiple sizes available for all products)
> > }
>
> > 3) colour_tbl {
> > code
> > (again this holds all colours available)
>
> > 4) acc_number_tbl {
> > account_number
> > company_name
> > address1
> > address2
> > address3
> > postcode
> > contact
> > telephone
> > fax
> > type (customer, supplier)
> > status (active, inactive)
> > }
>
> > Now this is where i got lost, 90% of the companies will have their
> > own
> > pricelists, so i created a new table for each company using their own
> > account_number as the table name (this seemed to be working) until i
> > had to query the list of customers who bought a particular product
> > and
> > then display these results in a dropdown on the product page.
>
> > I managed to create a query which brought back only those who were
> > suppliers / customers and which were active. But I cannot think how
> > to
> > query their own table based on the product code.
>
> > I realise I have to rethink the database structure, as this
> > particular
> > query seems to be impossible with the current one.
>
> Yes, you don't want multiple tables with basically the same information.
>
>
>
> > I would be grateful if someone could point me in the right direction
> > to overcome this issue.
>
> > I did get some help from a php group, but they suggested adding more
> > information than is currently needed (ie all the purchasing side).
> > This
> > database is only for training sales staff so does not need the
> > purchasing side.
>
> Well, just add the information you need, then. But you still need to
> restructure your database.
>
> > I have thought about adding all the product codes in to the
> > acc_number_tbl but I am unsure how many fields are allowed in each
> > table. But this would also cause a problem as to were to store
> > customer
> > prices per code. I have also thought about adding the account_number
> > to the code_tbl but this would only allow one customer per product
> > code.
>
> Thousands. But you shouldn't have them all in one row. Rather, have a
> table such as:
>
> AccountNumber
> ProductCode
> Price
>
> > thanks in advance
>
> > Dave
>
> Also, I recommend you google on "database normalization". The MySQL doc
> has a good discussion about it, but there are a lot of others.
> Understanding and applying normalization rules helps simplify the design
> process.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================- Hide quoted text -
>
> - Show quoted text -

Just incase anyone in the future gets stuck, I solved this problem
without adding the whole purchasing side to the database. However I
must give some credit to Hendri (see comp.lang.php group for details)

I created a small table:
product_company_tbl {
id
code
account_number
unit_price
etc..
}

then the query was like so:
$getownprices = mysql_query("SELECT
code,product_company_tbl.account_number,status,typ e,company_name FROM
`product_company_tbl` JOIN `acc_number_tbl` ON
product_company_tbl.account_number = acc_number_tbl.account_number
WHERE `code` ='$product' AND `type` ='customer' AND `status`
='Active' ORDER BY company_name")
or die(mysql_error());

This then gave me a list of all customers who bought the product that
is currently showing on the page.

Thanks Hendri (if you are viewing this group)