Professional Web Applications Themes

Help in querying tables - MySQL

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

  1. #1

    Default 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

    Dave Guest

  2. #2

    Default Re: Help in querying tables

    On 24 Jul, 11:32, Dave <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!

    Captain Guest

  3. #3

    Default Re: Help in querying tables

    On 24 Jul, 11:45, Captain Paralytic <com> wrote: 
















    >
    > 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 -[/ref]

    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.

    Dave Guest

  4. #4

    Default Re: Help in querying tables

    On Tue, 24 Jul 2007 12:53:05 +0200, Dave
    <co.uk> wrote: 
    >
    > 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.[/ref]

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

  5. #5

    Default Re: Help in querying tables

    Dave wrote: 

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

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

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

    AccountNumber
    ProductCode
    Price
     

    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.
    net
    ==================
    Jerry Guest

  6. #6

    Default Re: Help in querying tables

    On 24 Jul, 12:11, Jerry Stuckle <net> wrote: 








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


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

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


    >
    > 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.
    > net
    > ==================- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    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)

    Dave Guest

Similar Threads

  1. Querying related tables
    By c1natra in forum Coldfusion Database Access
    Replies: 4
    Last Post: November 11th, 08:49 PM
  2. Querying one-to-many tables
    By v_roma in forum Coldfusion Database Access
    Replies: 5
    Last Post: November 7th, 03:10 AM
  3. Querying data that matches in two different tables
    By weswhite7 in forum Coldfusion Database Access
    Replies: 3
    Last Post: July 28th, 03:53 PM
  4. Querying for unmatched records in two tables
    By OldNapkin in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 10th, 06:46 PM
  5. querying a view of a lot tables
    By Anthony in forum ASP Database
    Replies: 7
    Last Post: August 7th, 03:30 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139