How to manage "groups" in the database?

Question

I've asked this question here, but I don't think I got my point across.

Let's say I have the following tables (all PK are IDENTITY fields):

• People (PersonId (PK), Name, SSN, etc.)
• Loans (LoanId (PK), Amount, etc.)
• Borrowers (BorrowerId(PK), PersonId, LoanId)

Let's say Mr. Smith got 2 loans on his name, 3 joint loans with his wife, and 1 join loan with his mistress. For the purposes of application I want to GROUP people, so that I can easily single-out the loans that Mr. Smith took out jointly with his wife.

To accomplish that I added BorrowerGroup table, now I have the following (all PK are IDENTITY fields):

• People (PersonId (PK), Name, SSN, etc.)
• Loans (LoanId (PK), Amount, BorrowerGroupId, etc.)
• BorrowerGroup(GroupId (PK))
• Borrowers (BorrowerId(PK), GroupId, PersonId)

Now Mr. Smith is in 3 groups (himself, him and his wife, him and his mistress) and I can easily lookup his activity in any of those groups.

The problems with new design:

The only way to generate new BorrowerGroup is by inserting MAX(GourpId)+1 with IDENTITY_INSERT ON, this just doesn't feel right. Also, the notion of a table with 1 column is kind of weird.

I'm a firm believer in surrogate keys, and would like to stick to that design if possible.

This application does not care about individuals, the GROUP is treated as an individual

Is there a better way to group people for the purpose of this application?

Answer 1

The design of the database seems OK. Why do you have to use MAX(GourpId)+1 when you create a new group? Can't you just create the row and then use SCOPE_IDENTITY() to return the new ID?

e.g.

INSERT INTO BorrowerGroup() DEFAULT VALUES
SELECT SCOPE_IDENTITY()

(See this other question)

(edit to SQL courtesy of this question)

Answer 2

You could just remove the table BorrowerGroups - it carries no information. This information is allready present via the Loans People share - I just assume you have a PeopleLoans table.

People          Loans           PeopleLoans
-----------     ------------    -----------
1  Smith         6  S1    60    1   6
2  Wife          7  S2    60    1   7
3  Mistress      8  S+W1  74    1   8
                 9  S+W2  74    1   9
                10  S+W3  74    1  10
                11  S+M1  89    1  11
                                2   8
                                2   9
                                2  10
                                3  11

So your BorrowerGroups are actually almost the Loans - 6 and 7 with Smith only, 8 to 10 with Smith and Wife, and 11 with Smith and Mistress. So there is no need for BorrowerGroups in the first place, because they are identical to Loans grouped by the involved People.

But it might be quite hard to efficently retrieve this information, so you could think about adding a GroupId directly to Loans. Ignoring the second column of Loans (just for readability) the third column schould represent your groups. They are redundant, so you have to be carefull if you change them.

If you find a good way to derive a unique GroupId from the ids of involved people, you could make it a computed column. If a string would be okay as an group id, you could just order the ids of the people an concat them with a separator.

Group 60 with Smith only would get id '1', group 74 would become 1.2, and group 89 would become 1.3. Not that smart, but unique and easy to compute.

Answer 3

I would do something more like this:

• People (PersonId (PK), Name, SSN, etc.)

• Loans (LoanId (PK), Amount, BorrowerGroupId, etc.)

• BorrowerGroup(BorrowerGroupId (PK))

• PersonBelongsToBorrowerGroup(BorrowerGroupId (PK), PersonId(PK))

I got rid of the Borrowers table. Just store the info in the BorrowerGroup table. That's my preference.

Answer 4

use the original schema:

• People (PersonId (PK), Name, SSN, etc.)
• Loans (LoanId (PK), Amount, etc.)
• Borrowers (BorrowerId(PK), PersonId, LoanId)

just query for the data you need (your example to find husband and wife on same loans):

SELECT
    l.*
    FROM Borrowers            b1
        INNER JOIN Borrowers  b2 ON b1.LoanId=b2.LoanId
        INNER JOIN Loans       l ON b1.LoanId=l.LoanId
    WHERE b1.PersonId=@HusbandID
        AND b2.PersonId=@WifeID

Answer 5

The consensus seems to be to omit the BorrowerGroup table and I have to agree. Suggesting that you would use MAX(groupId+1) has all sorts of ACID/transaction issues and the main reason why IDENTITY fields exist.

That said; the SQL that KM provided looks good. There are any number of ways to get the same results. Joins, sub-selects and so on. The real issue there... is knowing the dataset. Given the explanation you provided the datasets are going to be very small. That also supports removing the BorrowerGroup table.

Answer 6

I would have a group table and then a groupmembers(borrowers) table to accomplish the many-to-many relationship between loans and people. This allows the tracking of data on the group other than just a list of members (I believe someone else made this suggestion?).

CREATE TABLE LoanGroup
(
    ID int NOT NULL 
    , Group_Name char(50) NULL 
    , Date_Started datetime NULL 
    , Primary_ContactID int NULL
    , Group_Type varchar(25)
)