3

I currently have a database with a students table like so:

 Students
 _________
 StudentID PK
 FirstName
 LastName
 BirthDate
 AddressL1
 AddressL2
 City
 State
 Zip
 etc.....

My client has asked me to add a feature so she can identify siblings in the system. If Jimmy is a student and Billy is a student and Jimmy and Billy are brothers, she wants a way to know that.

How would you implement this?

I'm thinking adding a Family Table with a FamilyID FK is overkill for this problem. Do you agree?

My instinct is to add a nullable column Called Family. Set it to null if the student has no siblings. If the student has siblings, each students get the same GUID stored in the Family column.

If you want to list the siblings of a given student you'd execute: Select Siblings.* From Students Inner Join Students as Siblings On Students.Family = Siblings.Family Where Students.StudentID = ?

What are the downsides of this approach? Is there an alternate strategy I should consider?

Aheho
  • 12,622
  • 13
  • 54
  • 83
  • unless they are correlated by hand, I'm afraid you can get only 'possible' siblings – vector Oct 18 '11 at 15:07
  • Please clarify: "My client has asked me to add a feature so she can identify siblings in the system. ". Is the feature supposed to identify dupes?... or does she identify them and the feature is then just about storing the link? Do you also need a spec of how this would be presented? Does this imply a search feature to find them? Not being picky, just trying to be exact about the requirements as the most common error is the opposite (not being specific enough / understanding the requirements :) – Michael Durrant Oct 18 '11 at 15:13
  • "Identify siblings"? How? By last name? What about half-siblings? – Unsigned Oct 18 '11 at 15:23
  • @Michael, She will tag the siblings via the UI. I just need a way to represent them in the database. – Aheho Oct 18 '11 at 15:41

1 Answers1

2

I assume the need for sibling relationship storage is a fringe use case, so out of the infinite possibilities to represent this I'll propose one that infringes the least on your existing model surely suited to the more common needs:

Add a "Family" table with Family ID and Student ID. Student ID is a foreign key referencing your "Students" table. The two fields together are the composite primary key.

When two students turn out to be siblings, create a new Family ID from a sequence, and add two rows with the two Student IDs and this new Family ID to the "Family" table.

Some might suggest normalizing fields like "address" or "mother's name" out to the family table, but trust me, families get more complicated than you can imagine :-)

Szocske
  • 7,466
  • 2
  • 20
  • 24
  • I agree with this approach, it seems to be the most logical way to link together students. – Purplegoldfish Oct 18 '11 at 15:36
  • Using this approach, how to you generate unique FamilyIDs? It can't be an identity column. Do I use a Guid? Is there a more compact way of generating a unique FamilyID? (I realize these questions are probably implementation specific...I'm using Sql Server 2008). – Aheho Oct 18 '11 at 15:45
  • Yes, you are right, you can't just use an autoincrement column. And you are also right, it's too implementation specific for me :-) The rest of the DB world has "sequences" for this. There seems to be a SO thread on this: http://stackoverflow.com/questions/282943/how-would-you-implement-sequences-in-microsoft-sql-server – Szocske Oct 19 '11 at 12:22