locked
table design.. help im stuck!! RRS feed

  • Question

  • Hi, I would like some advice about my database design. Part of my application stores contracts – this is where my ambiguity arises. The first part of my business process involves generating a contract form. The form will be used to collect customer details, and needs to contain the contract number that the customer will use to setup their automatic bank payments. The system will use the contract reference when processing bank transactions to tie the amounts received back to the customer contract.

     

    The question is how should I structure my tables so that initially only a contract number is produced, and subsequently all other contract details are stored when these are collected?

    My current thinking is that I will have a Contract table for the contract number, and a ContractDetail table for the main information. Do you think two tables are required? I could use an InUse flag to mark contracts that don’t have any details, and this would also allow me in enforce integrity (ie Required (R) fields) on the ContractDetail table. What do you think?

     

    Contract

    ContractID                                Integer                         PK                                -

    InUse                                       Bit                                                                    R

     

    ContractDetail

    ContractDetailID                       Integer                         PK                                -

    ContractID                                Integer                         FK                                R (Unique)

    ClientID                                   Integer                         FK                                R

    RentalID                                   Integer                         FK                                R

    StartDate                                  DateTime (UTC)                                               R         

    CloseDate                                DateTime (UTC)                                               null

    PaymentDayID                         Integer                         FK                                R

    PaymentFrequencyID               Integer                         FK                                R

     

     

    thanks

    • Moved by Kent Waldrop _ Friday, August 5, 2011 5:13 PM A design question (From:Transact-SQL)
    Sunday, May 28, 2006 6:54 AM

Answers

  • Hi,

    as of your explanation you have to consider some things in your design:

    The tables can be merged together because a 1:1 relation exists between each other if:

    -there can be only one client associated with a contract.
    -there can be only one rental associated with a contract.

    From my plain understanding of the structure below the two table structures can be merged together, but table design is often driven by business rules and understanding. If you are not sure about this, try to explain each column that exists in the table and if this is only dependent on the main contract information.


    HTH, Jens Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---

    Sunday, May 28, 2006 7:59 AM

All replies

  • Hi,

    as of your explanation you have to consider some things in your design:

    The tables can be merged together because a 1:1 relation exists between each other if:

    -there can be only one client associated with a contract.
    -there can be only one rental associated with a contract.

    From my plain understanding of the structure below the two table structures can be merged together, but table design is often driven by business rules and understanding. If you are not sure about this, try to explain each column that exists in the table and if this is only dependent on the main contract information.


    HTH, Jens Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---

    Sunday, May 28, 2006 7:59 AM
  • Hi Andrew,

    I am going to make a couple of assumptions, which would have a bearing on your table design.

    1. The RentalId refers to a property, i.e. realestate, or some another object.

    2. You expect a number of contracts, and clients against each RentalId.

    If I am correct, I would have the main focus on the RentalId, as this will, or should be, the only constant. The first table would contain the RentalId as the Primary key, and any other data revelent to the rental would be contained in this table.

    You would have a Client  or Contract table, to contain the details of the Client and contract, linked through the RentalId. This table could contain all the details of the client and the contract.

    I am also presuming your Contract form, would refer to a specific RentalId. To commence data entry, all that should be required is  the RentalId and the Contractid, and details could be supplied at a later date. The initial Sql INSERT statement would place the RentalId and ContractId in the Client/Contract table, and later located by using a SELECT statement..

    If I have erred in my assumptions, please correct me, and I'll revisit your table design.

    Regards,

    John

     

     

     

     

     

     

    Monday, May 29, 2006 8:54 AM