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.

Hello

No  you don't have to store arrays for  receipt_ids. You need receipt_id foreign key to receipts  table and haveing unique constraint on statement_id  and receipt_id. Create check constraint to make sure that statement_starting_date    column's value is greater than statement_closing_date . Do allow NULL for statement_closing_date  ( well it depends on your business requirements) and DO NOT allow NULL to  statement_starting_date .

I don't understand your model:

You have receipts - one statement is associated with multiple (none, one, more than one) receipts.

Then the column

statement_id    SERIAL  REFERENCES   statements(statement_id)

is exact and enough: You do not need  the same in the other direction:

receipt_id_array                integer[]

To get all receipts of a given statement, you can do a search like

Select *

From receipts

Where statement_id = @id

You need a third table if one receipt is associated with multiple (none, one, more than one) statements.Then you have to drop both columns (receipt_id and receipt_id_array) and create a third table:

Create table receipts_statements(id int Identity(1,1) Primary Key,

    receipt_id int Constraint fk_recstat_receipt References receipts(receiptsId),

     statement_id int Constraint fk_recstat_statement References statements(statementsId))