Database Design Question.?

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Jackson77
    New Member
    • Jul 2007
    • 3

    #1

    Database Design Question.?

    Hey guys,

    I'm currently in the design stage of a database, and have a question of how the keys should be set up in two of my tables.

    I have an Orders table, which is related to a Customers table, and also to an Images table.
    The orders table has OrderID (PK), CustID (FK), and ImageSetID (PK)

    The ImageSetID field joins to the Images table which contains ImageSetID (FK), ImageID (PK), and ImagePath

    Now, I plan to have multiple images for each order, which all will have a common ImageSetID, and each individual image will have an Unique ImageID.
    And I plan to store all the image paths in one table.

    Example:

    ImageSetId - ImageID - ImagePath
    ---------------------------------------------------
    001 - 001 - C:\Pictures
    001 - 002 - C:\Pictures
    002 - 001 - C:\Pictures
    002 - 002 - C:\Pictures
    002 - 003 - C:\Pictures

    Now, am I wondering.. Am I able to Have a unique ImageID, relating to the ImageSetID?
    But, while still using the same ImageID, but for a different ImageSetID..

    The way I plan to use input the data, is by creating a new order, then importing images into that order, but for the ImageSetID to stay the same while I am in the same order, but for the ImageID to change.

    If that is not possible, any there any suggestions to how to set that up?

    Anything would be appreciated, thank you.
  • Infide
    New Member
    • Jul 2007
    • 28

    #2
    Originally posted by Jackson77
    Hey guys,

    I'm currently in the design stage of a database, and have a question of how the keys should be set up in two of my tables.

    I have an Orders table, which is related to a Customers table, and also to an Images table.
    The orders table has OrderID (PK), CustID (FK), and ImageSetID (PK)

    The ImageSetID field joins to the Images table which contains ImageSetID (FK), ImageID (PK), and ImagePath

    Now, I plan to have multiple images for each order, which all will have a common ImageSetID, and each individual image will have an Unique ImageID.
    And I plan to store all the image paths in one table.

    Example:

    ImageSetId - ImageID - ImagePath
    ---------------------------------------------------
    001 - 001 - C:\Pictures
    001 - 002 - C:\Pictures
    002 - 001 - C:\Pictures
    002 - 002 - C:\Pictures
    002 - 003 - C:\Pictures

    Now, am I wondering.. Am I able to Have a unique ImageID, relating to the ImageSetID?
    But, while still using the same ImageID, but for a different ImageSetID..

    The way I plan to use input the data, is by creating a new order, then importing images into that order, but for the ImageSetID to stay the same while I am in the same order, but for the ImageID to change.

    If that is not possible, any there any suggestions to how to set that up?

    Anything would be appreciated, thank you.
    Here's how I would solve this problem.

    Three tables.
    Image table. pk_ImageID, description, path, etc..
    Orders table. pk_OrderID, fk_CustomerID, order stuff, etc....
    OrderImage table. pk_OrderImageID ,fk_ImageID,fk_ OrderID

    This will give you the many to many relationship yet keep your image and order table normalized.

    Comment

    • Jackson77
      New Member
      • Jul 2007
      • 3

      #3
      Originally posted by Infide
      Here's how I would solve this problem.

      Three tables.
      Image table. pk_ImageID, description, path, etc..
      Orders table. pk_OrderID, fk_CustomerID, order stuff, etc....
      OrderImage table. pk_OrderImageID ,fk_ImageID,fk_ OrderID

      This will give you the many to many relationship yet keep your image and order table normalized.
      But, if I were to do this, and store all the Image paths in one table, and by having the ImageID the PK, I wouldn't be able to have two Images with the ImageID of 001, or 002, which I plan to.

      Thank you for your suggestion, but I would prefer if the Images were numbered according to the order they were placed under the order, and not just being counted upwards so that Order 010 has ImagedID of 001, 002,003,, and then Order 011 having Images of ImageID's of 004, 005, 006. I would like the numbering to start back at 001 for each new order.

      I hope this doesn't confuse anyone.

      Thanks again, if you could give me any more suggestions, it would be appreciated.

      Comment

      • Jackson77
        New Member
        • Jul 2007
        • 3

        #4
        Sorry, It came up with an error when I first posted this, and then I refreshed it.
        ..And now I can't delete this Double post.
        My apologies!

        Comment

        Working...