CODEWITHSUNDEEP
database-design
David Villarreal
David Villarreal

Reputation: 33

Database design 3rd Normal Form

I have a database with many tables, 4 of which are these

  1. Payment
  2. Credit Card
  3. Paypal
  4. Bitcoin

Credit Card attributes:

PayPal attributes:

Bitcoin attributes:

Payment Table attributes:

A payment can only be paid by either a card/paypal/bitcoin so I am breaking 3rd normal form because if the client uses a card then I know that he didnt use paypal or bitcoin. How can I fix this so that I am not breaking 3rd normal Form.

Upvotes: 1

Views: 97

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95562

There isn't a completely clean way to do this today in SQL, because SQL platforms don't support assertions. (CREATE ASSERTION in the SQL standards) But you can design your tables to support sensible constraints, even without support for assertions.

Push the attributes that are common to all scheduled payments "up" into the table "scheduled_payments".

create table scheduled_payments (
  pmt_id integer primary key,
  pmt_amount numeric(14, 2) not null
    check (pmt_amount > 0),
  pmt_type char(1) not null
    check (pmt_type in ('b', 'c', 'p')),      -- (b)itcoin, (c)redit card, (p)aypal.
  other_columns char(1) not null default 'x', -- Other columns common to all payment types.
  unique (pmt_id, pmt_type)
);

-- Tables for Bitcoin and PayPal not shown, but they're very similar
-- to this table for credit cards.
create table credit_cards (
  pmt_id integer primary key,
  pmt_type char(1) not null default 'c'
    check (pmt_type = 'c'),
  foreign key (pmt_id, pmt_type) 
    references scheduled_payments (pmt_id, pmt_type),
  other_columns char(1) not null default 'x' -- Other columns unique to credit cards.
);

The primary key, not null, and check(...) constraints in "credit_cards" guarantee that every row will have a payment id number and a 'c'. The foreign key constraint guarantees that every row in "credit_cards" will reference a 'c' row in "scheduled_payments".

Upvotes: 1

Related Questions