SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2006
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation many to many over many tables (confusion!!)

    Sorry for the poor title but It's all I could think of to make an attempt at explaining my problem.

    I manage a site that sells automotive products, lights, towbars, cylcle racks and such. Towbars are in 3 tables(witter, westfalia, and bosal) this is because the data structure is so different for each supplier. i have an orders table and an orders_items table. I would like to have the orders_items table hold rows for each item, like :

    Code:
    order_id    item_id
        2          2365
    But because of all the tables, that wont work. I thought about having another field to say what table to use but that sounds like a bad idea, anyone have any ideas on what to do?


    thanks

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    I never had to do something like this, so there might be a better way, but I really believe you need to keep the basic stucture tables "items", "orders" and "order-items". You might add a column to the "item" table, where different values mean additional data for that item is to be found in another table.

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2006
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    based on what you said I have created an items table that just holds the description of the item and its native ID plus a reference to its original table - from that i should be able to list the items to the person processing the order but still maintain speed when loading the page - Will let you know how it turns out! thanks for the pointer

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by promanex View Post
    Towbars are in 3 tables(witter, westfalia, and bosal) this is because the data structure is so different for each supplier.
    do a search for supertype/subtype

    those 3 tables are subtype tables

    the supertype table would be your items table, and this is the only table that should participate in the order-items relationship

    if you use auto_increments, they would be assigned in the items table and propagated down to the 3 different towbar tables

    everything that all items have in common--a description, a price, etc.--would be in the items table

    consequently the need to join to the individual towbar tables is infrequent, but when it is needed, you would use LEFT OUTER JOINs and join all three of them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2006
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    do a search for supertype/subtype

    those 3 tables are subtype tables

    the supertype table would be your items table, and this is the only table that should participate in the order-items relationship

    if you use auto_increments, they would be assigned in the items table and propagated down to the 3 different towbar tables

    everything that all items have in common--a description, a price, etc.--would be in the items table

    consequently the need to join to the individual towbar tables is infrequent, but when it is needed, you would use LEFT OUTER JOINs and join all three of them
    Thank you! I will do some further reading on what you have said again, thanks!


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
  •