Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2012
    Posts
    33

    Unanswered: Project database for shop

    Hello

    I am question. I Have table Products ( ID: SerialNumber ) and table Receipts(ID: ID_R). I not have idea on relationship. One receipts may contain multiple products but this product may be only one receipt.

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    How about some table structure to go with the question. Helps to see the big picture.

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    This is a common SQL idiom

    A table models a set of entities, or it models a relationship (it can also be an auxiliary table, but skip that for now).

    CREATE TABLE Products
    (serial_nbr CHAR(15) NOT NULL PRIMARY KEY,
    ..);

    CREATE TABLE Receipts
    (receipt_nbr CHAR(10) NOT NULL PRIMARY KEY,
    .. );

    Those are the entities; now the relationship, 1:M.

    CREATE TABLE Receipt_Details
    (receipt_nbr CHAR(10) NOT NULL
    REFERENCES Receipts (receipt_nbr),
    serial_nbr CHAR(15) NOT NULL UNIQUE
    REFERENCES Products(serial_nbr),
    PRIMARY KEY (receipt_nbr, serial_nbr),
    ..);

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Thanks Joe, but I was hoping the OP would provide their table ddl. P.S. Welkom back!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you might want to add a unique index in the receipt details table

    do you actually mean a serialnumber or do you mean a product number?
    A product may get allocated a number, but specific instances of a rpoduct mayu get allocated a nuber that uniquely identifies a specific itme of that product.

    think of vehicles, each vehicle as a unique Identification number (the VIN).
    think of mobile phones, each mobile phone will have an IMEI number
    it will have a modle number (that will be common to other phones of the same type, fromt he same maker
    and each phone will have a serial number which is unique to teh manufacturer (and or model)
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Run this:
    sp_help Products

    ...and this:
    sp_help Receipts

    ...and show us the results, in text output.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Celko View Post
    Those are the entities; now the relationship, 1:M.
    Actually M:M.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Aug 2012
    Posts
    33
    Hello. I wanted to idea on relation between tables I solved problem. I disbelled doubts.

    I greet.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    We greet back.
    Welcome to dbforums.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •