Should I duplicate my table for correct normalized database design?

Question

I'm trying to create a small application which will allow a person to handle incoming donations and distribute them among a list of receivers according to their percentage amounts which has been predetermined.

During the table design process, I have come to a place where I am unsure of how to proceed and am looking for advice in how I should design the tables. I have begun by placing all people in the same table regardless of whether they are donors or receivers because each person could potentially either give and/or receive.

As you can see below, tblGiverReceiver is the central table, however I'm not sure whether the fact that there is a circular reference will cause me problems and whether or not it is bad design from a normalization standpoint.

Here is what my design looks like:

I know the naming conventions are not correct. I am still in the design stage and find it easier to use names like this to help me think.

Should I duplicate the tblGiverReceiver table and rename each as tblGiver / tblReceiver or is it OK for me to link the table back to itself in this manner?

EDIT: I read through a suggested link from a fellow stackoverflow-er (How can you represent inheritance in a database?), however the question posed is not what I was looking for.

Answer 1

Your application is about People, Addresses, Donations and Received payments so they seem to be the appropriate tables. Here is my suggestion implemented as a sample design in 5th normal form.

Tables and relationships start out as statements of facts such as "Person has LanguagePreference". I specified the facts and then used our NORMA tool to generate the schema design. Sorry but I could not understand the "percentage amounts" concept so it is not included.

Here is the schema:

Here are the facts from which the schema was generated.

Here are the constraints.

Answer 2

I thought that you might find it helpful to see the SQL DDL for the schema diagram in my earlier answer.

This DDL was also generated from our NORMA tool. It took me about 30 seconds to choose the appropriate options and then Voila! a millisecond or so later we have the SQL DDL in fifth normal form.

CREATE SCHEMA "Donations"
GO

CREATE TABLE "Donations".Person
(
    personNr int NOT NULL,
    addressNr int NOT NULL,
    firstName nchar(30) NOT NULL,
    languagePref nchar(15) NOT NULL,
    lastName nchar(40) NOT NULL,
    nickName nchar(100) NOT NULL,
    title nchar(50) NOT NULL,
    CONSTRAINT Person_PK PRIMARY KEY(personNr)
)
GO


CREATE TABLE "Donations".Address
(
    addressNr int NOT NULL,
    address1 nchar(40) NOT NULL,
    address2 nchar(40) NOT NULL,
    country nchar(40) NOT NULL,
    postalCode nchar(10) NOT NULL,
    province nchar(30) NOT NULL,
    address3 nchar(40),
    CONSTRAINT Address_PK PRIMARY KEY(addressNr)
)
GO


CREATE TABLE "Donations".Donation
(
    donationNr int NOT NULL,
    amount decimal(6,2) NOT NULL,
    "date" datetime NOT NULL,
    personNr int,
    CONSTRAINT Donation_PK PRIMARY KEY(donationNr)
)
GO


CREATE TABLE "Donations".Payment
(
    paymentNr int NOT NULL,
    amount decimal(6,2) NOT NULL,
    "date" datetime NOT NULL,
    personNr int,
    CONSTRAINT Payment_PK PRIMARY KEY(paymentNr)
)
GO


ALTER TABLE "Donations".Person ADD CONSTRAINT Person_FK FOREIGN KEY (addressNr) REFERENCES "Donations".Address (addressNr) ON DELETE NO ACTION ON UPDATE NO ACTION
GO


ALTER TABLE "Donations".Donation ADD CONSTRAINT Donation_FK FOREIGN KEY (personNr) REFERENCES "Donations".Person (personNr) ON DELETE NO ACTION ON UPDATE NO ACTION
GO


ALTER TABLE "Donations".Payment ADD CONSTRAINT Payment_FK FOREIGN KEY (personNr) REFERENCES "Donations".Person (personNr) ON DELETE NO ACTION ON UPDATE NO ACTION


GO