SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot martin's Avatar
    Join Date
    Jun 2001
    Location
    London
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile lookuptable needs archiving, any thoughts

    Hi,

    I have a simple ordering system. When client orders something the information is held in a lookup table with two primary keys (client and order). Once I have viewed the order I wish to archive this. What would be the best way, copy the unique key into another table along with the date and then delete the original from the lookuptable? OR add an additional primary key (such as the date)??? to the lookup table?

    Currently because the order is still in the lookup table, the client cannot order the same item again (due to the unique key still held in the lookuptable), and this will cause a big problem!

    Any thoughts, suggestions greatly appreciated.
    Martin

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the term "lookup" is usually applied to tables which have a key and a description, such that you can look up the description for a value of the key, like a state code table

    what you describe is more often called a relationship or linking or junction table, because it implements a many-to-many relationship between two other entities -- in your case, clients and orders

    now to your problem...

    the more common structure involves clients, orders, and products

    orders-products is many-to-many, i.e. one order can have multiple products, and one product can be on multiple orders

    clients-orders, however, is one-to-many, i.e. a client can have multiple orders, but each order can have only one client

    your design, on the other hand, would allow an order to belong to multiple clients, which is unlikely

    so before you start looking at "archiving" your data, have a closer look and see why you have a many-to-many structure between clients and orders
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot martin's Avatar
    Join Date
    Jun 2001
    Location
    London
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    Hi r937,

    Sorry I didnt make myself clear in my original post, I was just trying to keep the description simple.

    If I may explain, the "client" in my original description is actually a department in a company. The "product" is actually one of over 100 printed materials (brochures, letterheads etc). Each Deptartment can order just some of the printed material that has been allocated to it and multiple departments can order the same product, hence my need for a many to many relationship table to allocate the department with the product.

    Once the Department has ordered (for example) some letterheads, I need to archive this and "clear" the many to many table so that they can reorder.

    The trouble is the joined primary key (Department, Product) is already in the table and hence the new order will have the same unique key as the original!

    So my problem is, do I add another field to the table with, say a date or move the data to another table.

    Sorry for the longwinded answer, I hope this makes more sense!
    Martin

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    nope, no i'm totally discombobulated

    i still don't see why you are doing without the equivalent of the orders table

    the department places an order, the order consists of multiple products, a product can be on multiple orders, you need a many-to-many between orders and products, and only a one-to-many between departments and orders

    yes, adding a date to the compound primary key will also work
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot martin's Avatar
    Join Date
    Jun 2001
    Location
    London
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry for my inability to express myself! I think this is why I have so much trouble!!

    I think I will pursue the compound primary key as you suggest... thanks for your patience!
    Martin


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
  •