CODEWITHSUNDEEP
sqldatabase
Tyler Siegrist
Tyler Siegrist

Reputation: 139

Best way to store a list of numbers in an SQL database?

I need to store a list of numbers, preferably into a single cell. I have a list of people each with a unique ID and I need to be able to who their family members are. I'm fairly new to SQL databases to try to keep it fairly simple please.

Upvotes: 4

Views: 6171

Answers (3)

Robert Co
Robert Co

Reputation: 1715

Adding the familyId is the simplest solution.

However, since the members of the family changes over time as children get married and form a new family. I would create a separate "relationship" table, which contains personId and familyID.

If you want to track member movements. In a data warehousing world, you typically have a relId, relTy and Timestamp to preserve history, but you don't have to go that far.

Perhaps one day, you want to create a family tree. So decide if you really want a personId/familyId as column names in your relationship table.

In summary, for your immediate need I will go with a separate relationship table.

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726709

Do not do this: storing multiple values in the single column kills the possibility of using referential integrity constraints, and turns into a nightmare to maintain (imagine maintaining everyone's lists when a new baby's born!)

The simplest solution is to add a column with a family_id to each person's row. All members of the same family will have this unique ID set to the same value; members of different families will have different family_ids.

This is not ideal in cases of marriages, unless you are willing to either join the two families on marriage of their children, or move the bride or the groom to the family of the spouse.

Upvotes: 5

TMan
TMan

Reputation: 1905

You're going to want to setup a Family table with two columns, one to store the unique ID of the person in question, and another to store the unique ID of their family member. Your table will end up looking like this:

Family(userID, familyMemberID)

and there will be an entry for each family member so it could look something like this:

(1, 2), (1, 3), (1, 4), (1, 5), (2, 8)

It's up to you to decide you want to store the connection both directions (i.e. for every insert, you also insert the reciprocal (1,2) also inserts (2,1)) which can easily be accomplished via triggers or other means.

Upvotes: 3

Related Questions