Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Posts
    3

    check register normalization problem

    Hello,

    I am designing an accounting database, and must be able to display a check register for each checking account. The problem is how to tie/relate the check payee to a customer, vendor, creditor, or employee in the database?

    I suppose I could build a base table called Names which would have all customers, vendors, employees etc. and a column indicating the type. This way a check payee field could point to a row in the Names table. Columns not common to all types, such as an employee's salary, would be in separate tables and tied back to the Names table. (I am guessing this is the way QuickBooks does it).

    Is there a better way to handle this? Every example of an accounting database I have seen has separate tables for customers, employees, etc. so the above setup seems odd.

    Thanks,
    roger

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: check register normalization problem

    Originally posted by rdheadrick
    Hello,

    I am designing an accounting database, and must be able to display a check register for each checking account. The problem is how to tie/relate the check payee to a customer, vendor, creditor, or employee in the database?

    I suppose I could build a base table called Names which would have all customers, vendors, employees etc. and a column indicating the type. This way a check payee field could point to a row in the Names table. Columns not common to all types, such as an employee's salary, would be in separate tables and tied back to the Names table. (I am guessing this is the way QuickBooks does it).

    Is there a better way to handle this? Every example of an accounting database I have seen has separate tables for customers, employees, etc. so the above setup seems odd.

    Thanks,
    roger
    The approach you suggest is quite common for handling subtypes - a generic table called something like PERSON or PARTY (which could include companies and people), that has the common information, then a table for each subtype. Example:
    Table PERSON (id PRIMARY KEY, name, type, ... )
    Table CUSTOMER (id PRIMARY KEY, credit_rating...,
    FOREIGN KEY (id) REFERENCES PERSON)
    ...

    A big advantage is that queries that are not interested in the subtype details can just join to PARTY to get the name, address etc. - otherwise you would have to outer join to each of the subtypes in turn to get the name, which is messy.

    For queries that are interested in subtypes, you can create views that pre-join the PARTY and subtype records.

  3. #3
    Join Date
    Oct 2002
    Posts
    3
    Hi Tony,

    Thanks for the feedback. I needed someone to confirm I wasnt off base here, as the rest of the db design depends upon how this done.

    thanks,

    roger

Posting Permissions

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