THE SQL Server Blog Spot on the Web
Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | Join | Help

Basic (Quick) Question about Receipt/Statement Database

  •  11-07-2008, 4:19

    Basic (Quick) Question about Receipt/Statement Database

    Hello, I'm completely new to SQL - and have a question on how to make a basic database. I want to make a database of receipts (credit card) and credit card statements.  My proposed receipts table (i'm not worried about syntax right now - although this specific syntax is PostgreSQL) is basically something like this:

    CREATE TABLE receipts (
        receipt_id   SERIAL      PRIMARY KEY,

        vendor        varchar(20) NOT NULL,

        cost          float NOT NULL,

        receipt_type    int,

        receipt_date    date,

        statement_id    SERIAL  REFERENCES   statements(statement_id)

    ); 

    CREATE TABLE statements (
        statement_id   SERIAL      PRIMARY KEY,

        statement_closing_date    date,

        statement_starting_date    date,

        payment_due_date           date,

        receipt_id_array                integer[]

    );

     

    I actually have two questions, but my second question is dependent on the first, so I'll just ask that first:

    1) I want each receipt to reference the statement it belongs to (i.e., each receipt has a statement_id that goes along with it) and I want each statement to have a list of receipts that go along with it (i.e., something like: a statement that has multiple receipt_ids that go along with it).  However, it seems that I can't have foreign keys in each table referencing the other, because this will seem to create a circular reference.  How can I get around this (i.e., how do I accomplish what I am trying to do so that each receipt is tied to a statement, and each statement is tied to a set of receipts. 

    I'm sorry if this is already answered somewhere else on the web or in this blog -- if so, please point me to the right place.

     Thank you very much.

View Complete Thread
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement