CODEWITHSUNDEEP
sqldatabasedatabase-designdatabase-schemaschema-design
Pixelated
Pixelated

Reputation: 1651

Database design - Multiple 1 to many relationships

What would be the best way to model 1 table with multiple 1 to many relatiionships.

enter image description here

With the above schema if Report contains 1 row, Grant 2 rows and Donation 12. When I join the three together I end up with a Cartesian product and result set of 24. Report joins to Grant and creates 2 rows, then Donation joins on that to make 24 rows.

Is there a better way to model this to avoid the caresian product?

example code

DECLARE @Report
TABLE   (
        ReportID    INT,
        Name        VARCHAR(50)
        )

INSERT
INTO    @Report 
        (
        ReportID,
        Name 
        )

SELECT  1,'Report1'


DECLARE @Grant 
TABLE   (
        GrantID     INT IDENTITY(1,1) PRIMARY KEY(GrantID),
        GrantMaker  VARCHAR(50),
        Amount      DECIMAL(10,2),
        ReportID    INT
        )

INSERT
INTO    @Grant 
        (
        GrantMaker,
        Amount,
        ReportID
        )

SELECT  'Grantmaker1',10,1
UNION ALL
SELECT  'Grantmaker2',999,1


DECLARE @Donation
TABLE   (
        DonationID      INT IDENTITY(1,1) PRIMARY KEY(DonationID),
        DonationMaker   VARCHAR(50),
        Amount          DECIMAL(10,2),
        ReportID        INT
        )

INSERT
INTO    @Donation 
        (
        DonationMaker,
        Amount,
        ReportID
        )

SELECT  'Grantmaker1',10,1
UNION ALL
SELECT  'Grantmaker2',3434,1
UNION ALL
SELECT  'Grantmaker3',45645,1
UNION ALL
SELECT  'Grantmaker4',3,1
UNION ALL
SELECT  'Grantmaker5',34,1
UNION ALL
SELECT  'Grantmaker6',23,1
UNION ALL
SELECT  'Grantmaker7',67,1
UNION ALL
SELECT  'Grantmaker8',78,1
UNION ALL
SELECT  'Grantmaker9',98,1
UNION ALL
SELECT  'Grantmaker10',43,1
UNION ALL
SELECT  'Grantmaker11',107,1
UNION ALL
SELECT  'Grantmaker12',111,1

SELECT  *
FROM    @Report r
INNER JOIN
        @Grant g
        ON  r.ReportID = g.ReportID 
INNER JOIN
        @Donation d
        ON  r.ReportID = d.ReportID

Update 1 2011-03-07 15:20

Cheers for the feedback so far, to add to this scenario there are also 15 other 1 to many relationships coming from the one report table. These tables can't for various business reasons be grouped together.

Upvotes: 1

Views: 894

Answers (3)

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

Reputation: 95512

If you're going to join on ReportID, then no, you can't avoid a lot of rows. When you omit the table "Report", and just join "Donation" to "Grant" on ReportId, you still get 24 rows.

SELECT  *
FROM    Grant g
INNER JOIN
        Donation d
        ON  g.ReportID = d.ReportID

But the essential point is that it doesn't make sense in the real world to match up donations and grants. They're completely independent things that essentially have nothing to do with each other.

In the database, the statement immediately above will join each row in Grants to every matching row in Donation. The resulting 24 rows really shouldn't surprise you.

When you need to present independent things to the user, you should use a report writer or web application (for example) that selects the independent things, well, independently. Select donations and put them into one section of a report or web page, then select grants and put them into another section of the report or web page, and so on.

If the table "Report" is supposed to help you record which sections go into a particular report, then you need a structure more like this:

create table reports (
    reportid integer primary key,
    report_name varchar(35) not null unique
);

create table report_sections (
    reportid integer not null references reports (reportid),
    section_name varchar(35),  -- Might want to reference a table of section names
    section_order integer not null,
    primary key (reportid, section_name)
);

Upvotes: 1

Nathan DeWitt
Nathan DeWitt

Reputation: 6591

Is there any relationship at all between Grants and Donations? If there isn't, does it make sense to pull back a query that shows a pseudo relationship between them?

I'd do one query for grants:

SELECT r.*, g.*
FROM @Report r
JOIN @Grant g ON r.ReportID = g.ReportID

And another for donations:

SELECT r.*, d.*
FROM @Report r
JOIN @Donation d ON r.ReportID = d.ReportID

Then let your application show the appropriate data.

However, if Grants and Donations are similar, then just make a more generic table such as Contributions.

Contributions
-------------
ContributionID (PK)
Maker
Amount
Type
ReportID (FK)

Now your query is:

SELECT r.*, c.*
FROM @Report r
JOIN @Contribution c ON r.ReportID = c.ReportID
WHERE c.Type = 'Grant' -- or Donation, depending on the application

Upvotes: 2

apaq11
apaq11

Reputation: 82

The donation and grant tables look almost identical. You could make them one table and add a column that is something like DonationType. Would reduce complexity by 1 table. Now if donations and grants are completely different and have different subtables associated with them then keeping them seperate and only joining on one at a time would be ideal.

Upvotes: 0

Related Questions