Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2012
    Posts
    8

    Combining One-to-One Entities into One Table

    I have three separate entities as follows: Contract, Buyer, and Seller. Under the business model, the relationships will always be one-to-one. That is, a contract will have one buyer and one seller. The buyer and seller will be associated with one contract.

    As part of creating a contract record, the user will also be creating buyer and seller record. The contract, buyer, and seller data will need to be searchable. The user will always view contract, buyer and seller data together in the UI.

    Should three tables be created to represent contract (5 columns), buyer (40 columns) and seller (2 columns)? Or would it be better just to create one table with all 47 columns (perhaps for efficiency purposes, ease of querying/programming, or other reasons)? The number of contracts/buyers/sellers is small and will have slow linear growth (~4000 records created since 2008).

    Thanks in advance for your help.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Three tables.
    Buyer
    Seller
    Contract (Implements as "Many-to-many" relationships between buyers and sellers.)
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I'd use two tables, one table for Persons and one table for Contracts (which has foreign keys to Persons). That way each person is tracked exactly one time regardless of how many times they may enter into contracts as either buyers or sellers. Each contract tracks one buyer and one seller, so you're good.

    Note that this design is simple, but it is fraught with peril! Persons change over time (they get married, etc). Contracts may only have one buyer or seller today but I'll bet that at some point you'll have more than one buyer, seller, or both.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jun 2012
    Posts
    8
    Many thanks for the feedback.

    A couple of notes in regards to the business requirements.

    1) A contract will always have only one buyer and one seller. A buyer will always be associated with one contract. A seller will always be associated with one contract. A So, it is guaranteed to be a one-to-one relationship.

    This is not the typical relationship that you would think about between contracts/buyers/sellers. However, in this business, it is absolutely the case that this is a one-to-one relationship (and it cannot change due to the inherent properties of the business).

    2) Very different information is captured between the buyer and seller. Corp name is the only common attribute between the two.

    Sorry for that information not being clear on the first post; but would that change your design?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    If either a buyer can buy more than once or a seller can sell more than once, then I'd have separate tables for each of them and for contracts as well. If a buyer can only buy once and a seller can only sell once, then I'd create a single table because all of the information that you are recording would be covered by one primary key.

    I realize that I'm restating the obvious, but you have a VERY weird arrangement! I've seen many cases that originally thought they had exactly what you've described, but turned out to be more general after the system went live (which caused enormous extra work).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

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