Databases
StackOverflow2013
Postsdbo
POHow to manage "groups" in the database?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to manage "groups" in the database?
    text
    copied!<p>I've asked this question <a href="https://stackoverflow.com/questions/774402/is-there-anything-wrong-with-having-a-table-with-one-column-mssql-server">here</a>, but I don't think I got my point across. </p> <p>Let's say I have the following tables (all PK are IDENTITY fields): </p> <ul> <li>People (PersonId (PK), Name, SSN, etc.)</li> <li>Loans (LoanId (PK), Amount, etc.)</li> <li>Borrowers (BorrowerId(PK), PersonId, LoanId)</li> </ul> <p>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. </p> <p>To accomplish that I added BorrowerGroup table, now I have the following (all PK are IDENTITY fields):</p> <ul> <li>People (PersonId (PK), Name, SSN, etc.)</li> <li>Loans (LoanId (PK), Amount, BorrowerGroupId, etc.)</li> <li>BorrowerGroup(GroupId (PK))</li> <li>Borrowers (BorrowerId(PK), GroupId, PersonId)</li> </ul> <p>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.</p> <p>The problems with new design:</p> <p>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.</p> <p>I'm a firm believer in surrogate keys, and would like to stick to that design if possible.</p> <p>This application does not care about individuals, the GROUP is treated as an individual</p> <h3>Is there a better way to group people for the purpose of this application?</h3>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload