Posted by Dave on 07/24/07 07:29

I am in the process of testing a database design, which i believe I
have hit a stumpbling 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.

thanks in advance

Dave

• Next in forum: Re: Php based app and mp3's
• Prev in forum: Re: Are PHP libraries linked dynamically or statically?
• Thread view: help in querying tables

[Reply to this message]